BDE and Tquery speed question again

[LucieLastic]

hi All

I'm trying my best to speed up a simple select statement.

The select is a basic SELECT * from TABLE WHERE ID = n. The ID is indexed on the table.

I then tried sticking it in a Temp table

SELECT *
INTO #TempTable
from TABLE WHERE ID = n

I'm sticking the sql statement in a TQuery.SQL property and then doing an EXECSQL on the query, but it takes ages (15 secs).

The DB is SYBASE and the the table I'm querying has approx 600,000 records. The result set is about 166 records.

I've also tried to create a View, but the reading of the view is much slower than reading the Temp table.

Performing the SQL statements in SQLExplorer is quick but calling the EXECSQL is slow, is there anyway of speeding this up? StoredProcs are out really as it's the client db and they don't want me creating them all over the place - I have to make sure I clean up anything I create.

Any suggestions ?

many thanks
lou

 

[EricDraven]

Why not use a filter? Im not sure if they are quicker but I use them all the time on large tables and they seem to be pretty darn quick to me.

 

[LucieLastic]

hi EricDraven

Filtering isn't good in this respect as I don't want to be loading 600000+ records unnecessarily, the table size is also going to increase. I tried it anyway and the time to execute increased about another 7 seconds.

I'm wondering whether ADO is an option but going off previous replies there may not be much in it, response wise.
I'm going to bite the bullet and see if I can get a big difference using a Stored Proc.

lou

 

[LucieLastic]

AAAAHHHH ! I don't believe it!

I've changed the code so that the condition is set in the SQL property of the query at runtime rather than with Params[0] := ID, eg

QRY.SQL.ADD := 'WHERE ID = '+InttoStr(newID);

and it now takes 1 SECOND !!!!! to retrieve the data.

Can anyone shed any light as to why there is such a difference ? ie. 15 seconds using Params and 1 second writing the WHERE at runtime.

lou

 

[footpad]

lou,

Without more specifics, I can't hazard a terribly detailed guess, but I suspect that the secret lies in the way BDE prepares remote queries that have not been specifically prepared.

According to the help files, BDE will prepare a query if you do not do so explicitly. Depending on the differences between your two approaches, I susupect that BDE decided it needed to handle the first one and then decided to pass the second one directly to the server.

The speed up you noted suggests that your index wasn't being used the first time, but is being used now. I suspect that choice was made by your server.

In any event, are you formally preparing your queries before executing them? If not, consider doing so. I've heard that can make a dramatic improvement in your performance.

For more information, check out the query topics in the Developing Database Applications Help file.

Also, there's some solid info in the BDE32.HLP file (/program files/borland/common files/bde). Look at the topics on Passthrough SQL and your Sybase alias settings for that.

Hope this helps...

-- Lance

 

[LucieLastic]

Lance,

I think you're right, I wasn't doing a Prepare before the original was being executed.
I will try using params again with the Prepare and see if that fixes the response time.

Thanks for the helpful reply

lou

 

[LucieLastic]

Lance

I've just tried the 'old' way using Params and Prepare and the response time is still quite slow, that is, no where near as fast as 'hard coding' the TQuery at runtime.

...unless there's something else I've missed...I'll have a play around with it to see if it can be tuned further.

lou