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

[Jon Franz_antispam]

> 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