Fastest way to get thousands of db records to client in 3 tier?

[R. Alan Monroe]

Subject says it all... what's the fastest way to get many thousands of
records to the client?

I'm trying to sketch out a traditional 3 tier client-server system.
Multiple clients would make requests to the server. The server should
execute sql select commands against a server-side database, then
return the results to the client.

I tried the standard xmlrpclib and SimpleXMLRPCServer tools that came
with Python. I liked the simplicity, but it was about 30-35 times
slower than executing the sql directly from the client...

I intend to write both client and server in Python, and my main goal
in the first cut is speed. Suggestions?

Alan

 

[Max M]

R. Alan Monroe wrote:

I intend to write both client and server in Python, and my main goal
in the first cut is speed. Suggestions?

Why do you need thousand of rows at the client? Normally you would get
the results in batches, so that you only transfer at most a few hundred
at a time to the client.

I am not saying that you never need to do it. It's just rare. So perhaps
all you need is a change in user-interface?

Anyhoo you can reduce the number of queries/connections to get better
speed. It is easy to inadvertently make queries like:

for i in range(1000):
"select * from table1 where some_number=i;"

Instead of:
"select * from table1 where some_number=<1000;"

Normally it is not as simple as this, but the basic problem is the same.

But if your problem is that you transfer the data over the wire in xml
format and that is too slow, you need to use another format. XML
conversion can have a lot of overhead. Especially if it is many thousand
rows.

Why not let the client ask directly if it is faster?

But if you can bring down the number of rows, using xml might not be a
problem at all.



regards Max M

 

[Miki Tebeka]

Hello Alan,

I intend to write both client and server in Python, and my main goal
in the first cut is speed. Suggestions?

Find your bottleneck! (try hotshot)
If it's in the network maybe use socket + pickle + bz2 ?

HTH.
Miki

 

[R. Alan Monroe]

Why do you need thousand of rows at the client? Normally you would get
the results in batches, so that you only transfer at most a few hundred
at a time to the client.

Those types of tests will be done in my second cut.

Why not let the client ask directly if it is faster?

I thought that defeats the purpose of having 3 tiers. You'd have to
update the client if/every time business rules changed. Unless I'm
missing something. Links to tutorials welcome.

Alan

 

[John J. Lee]

Those types of tests will be done in my second cut.

At this stage you should be worrying more about this than about the
details of the protocol and implementation thereof.

I thought that defeats the purpose of having 3 tiers. You'd have to
update the client if/every time business rules changed. Unless I'm

[...]

Why? The client asks the client in terms of "business logic", the
server asks the database in SQL. If server and DB can communicate
quickly, no problem.


John

 

[R. Alan Monroe]

I thought that defeats the purpose of having 3 tiers. You'd have to
update the client if/every time business rules changed. Unless I'm

[...]

Why? The client asks the client in terms of "business logic", the

^^^^^^^^^^^^^^^^^^^^^^
Not sure what you meant to say here?

server asks the database in SQL. If server and DB can communicate
quickly, no problem.

Let's say I want to see all the tickets I worked on in the previous
calendar year. The server can get this from the DB in 1 second. But if
the server has to spend 30, 60 or more seconds getting those results
back to me, the client, the system wouldn't be worth using, in my
opinion.

I _have_ used systems that returned a subset of matching records to
the client, and found it very uncomfortable. That's why in my system I
want to scratch my own itch, and return all the records.

Alan

 

[Guest]

I _have_ used systems that returned a subset of matching records to

the client, and found it very uncomfortable. That's why in my system I
want to scratch my own itch, and return all the records.

Here are some odd ideas:
- Have the server do the original query, but put the results into a table
(temporary table? - depends upon the lifespan of temp tables in your RDBMS),
then let the server hand the client the name of this table. The client then
queries for the records from the location the server gave it. The table the
client queries is a one-time view of the data, thus your server code can do
any joins and logic needed for the biz rules, and the client simple does a
select * on the results.

- Stored procedures. Move some of your biz logic into them, the server will
get simpler, and the occasional client by-pass of the server won't cause
headaches when the biz rules change.

- Warning, this one is ugly: Write the query in such a way that the
results are preformated for transmission between the server and client. For
example, using string concatenation, change your query to return 1 column,
which would contain a pickled object sutable for immediate transmission to
the client. If you're not passing pickled objects, this will be easier,
since you just format the column to contain a valid line/record for sending
to the client.

~Jon Franz
NeuroKode Labs, LLC

 

[Chris Reedy]

Subject says it all... what's the fastest way to get many thousands of
records to the client?

I'm trying to sketch out a traditional 3 tier client-server system.
Multiple clients would make requests to the server. The server should
execute sql select commands against a server-side database, then
return the results to the client.

I tried the standard xmlrpclib and SimpleXMLRPCServer tools that came
with Python. I liked the simplicity, but it was about 30-35 times
slower than executing the sql directly from the client...

I intend to write both client and server in Python, and my main goal
in the first cut is speed. Suggestions?

Alan

I suspect that it will be difficult for you to match the speed of using
sql to do direct record retrieval from the client. People who build
databases spend a lot of time and effort making this work as fast as
they can because of its impact on overall database performance.

My suggestion: You need to think harder about your performance versus
functionality trade-offs. E.g., if you're not moving too many records
and the server isn't heavily used, you shouldn't care about the extra
overhead.

On the other hand, if you're moving a lot of records _and_ the server is
heavily used, then you're trying for bleeding edge performance and you
should either expect to spend a lot of time making ugly compromises in
order to meet your requirements or to spend money buying a bigger server
and more communications bandwidth.

Chris

 

[John J. Lee]

Why not let the client ask directly if it is faster?
I thought that defeats the purpose of having 3 tiers. You'd have to
update the client if/every time business rules changed. Unless I'm

[...]

Why? The client asks the client in terms of "business logic", the

^^^^^^^^^^^^^^^^^^^^^^
Not sure what you meant to say here?

The client asks the server, sorry.

Let's say I want to see all the tickets I worked on in the previous
calendar year. The server can get this from the DB in 1 second. But if
the server has to spend 30, 60 or more seconds getting those results
back to me, the client, the system wouldn't be worth using, in my
opinion.

"So don't do that", was the point that somebody was making. Worry
about the big issue of what network traffic you'll have and how many
round trips, not the small issue of the particular means of transport
of that traffic.

I _have_ used systems that returned a subset of matching records to
the client, and found it very uncomfortable. That's why in my system I
want to scratch my own itch, and return all the records.

Fair enough. Would have to know more to comment.


John