Query optimization in Delphi 4

Question

In Delphi 4, we have one SELECT query which is fetching 3 Text type fields along with other required fields at a time using a TQuery component.
There are more than 1,000 records (which might increase in future).
This query consumes lots of memory. and I think due to this next query takes huge amount of time to execute.

I'm using BDE to connect to SQL-server.

I need to optimize the performance so that it won't take so much time. Please advice.

Answer 1

You should consider some kind of Paging mechanism. do not fetch 1000 (or 1 million) records to the client, but instead use paging with SQL-server ROW_NUMBER() to get blocks of say 50-100 records per page.

so a query like:

SELECT id, username FROM mytable ORDER BY id

could look like this:

SELECT * FROM (
SELECT id, username, TOTAL_ROWS=Count(*) OVER(), ROW_NUMBER() OVER(ORDER BY id) AS ROW_NUM
FROM mytable 
) T1
WHERE ROW_NUM BETWEEN 1 AND 50

The ORDER BY field(s) should be Indexed (if possible) to speed things up.