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.

Answer 2

If you use a TQuery, make sure that you use a local TField outside of the retrieval loop for faster process (the FieldByName method is somewhat slow).

You can try our freeware Open Source classes to access any DB engine.

It provides a direct access to MS SQL via OleDB, without calling the ADO layer.

It is very optimized for speed, and is Unicode ready, even on older version of Delphi. It has been tested on Windows XP, Vista, and Seven (including 64 bit).

It has a TQuery emulator: this is not a true TQuery as defined in the DB.pas unit, but a class with most of the same methods. And you won't need to work with all BDE classes and units. Drawback is that you can't use Delphi DB visual controls, but for a quick TQuery, it will do the work.

It has some unique features (like late-binding use for field access), which are worth considering.

It does not require any third-party library (like the BDE), and works from Delphi 5 up to XE2. I guess it will run under Delphi 4 also.

You can download and ask for support in our site.

Answer 3

To reduce the time (depending on data) we can use DATALENGTH in a query.

like

DATALENGTH(TEXT) <> 0

This will not load records having no value in TEXT field.

Answer 4

Really fetching TEXT column values takes the time and takes the memory.

To speedup fetching, exclude TEXT columns from the SELECT list. And fetch them using an additional query by a record primary key and only when you really need their values.

To reduce memory usage do as above, use Unidirectional query or bove.

Answer 5

• Which field types did you define? If they are large, they will take up memory, there's little you can do about it. You may try different libraries, some are smart enough to allocate only the actual size of the field and not the declared one, other will always allocate the declared one, so if you have three fields of 4000 characters and 1000 records you will have 3 * 4000 * 1000 bytes allocated just for the text fields.
• Do you need to load the whole dataset at once? Fetching just the needed data using a where condition and/or incremental fetching will help to reduce both memory and maybe execution time
• If the query takes long to execute, you have to understand why and where. It could be the query execution time itself, it could be the time taken to transfer the result set to the client application. You need to profile your query to understand what is the issue actually, and take the proper corrective action. 1000 records is a very small dataset today, if it is slow there's something really bad.
• Each database has subtle optimization differences. You have to study the one you're using carefully, and write the proper query for that database - after you designed the proper database.

Just changing the database components without determining that's exactly the cause is plainly stupid, and if the problem is elsewhere is just wasted time. BDE works well enough, especially if compared to ADO. And Microsoft is desupporting ADO as well, thereby I won't invest time and money in it.

Update: it would be interesting to know why this answer was downvoted. Just because ADO worshippers will have an hard time in the future, and they feel the need to hide the truth?

KEEP ON DOWNVOTING MORONS. YOU JUST SHOW YOUR IGNORANCE!