Does setting RequestLive=True slow 'query.open' down?

[LucieLastic]

hi All

I'm using D7 and the BDE.

My understanding is that setting RequestLive = true will slow the .Open down as the BDE has to create a cursor to the dataset, am I right? The query does contain a 'where' clause but it is on a very big table (millions of records).

I'm trying to speed up someone else's code and I always avoid using RequestLive and cachedupdates, (cached updates are not being used in this project). In my experience I've also learnt that using parambyName is also slower than writing the values directly into the 'sql.add()' lines.

This code I'm looking at also uses a lot of ParamByName commands which I'm going to replace but was wondering whether removing the Requestlive=true would speed it up aswell. The RequestLive is being used with the .Delete but I'm going to replace all those with 'delete from... where' queries.

lou

 

[lespaul]

If you set the RequestLive to false, you will get a read only recordset, so if you don't need to do any data editing, should be fine.

I haven't found that setting requestlive := True reduces the open, what killed my query speed was the ParamByName. When I removed those and used the direct SQL.Add(), the queries were CONSIDERABLY faster.

HTH

Leslie

 

[hilfy]

Using parameters is actually faster under certain conditions:

1. The query will be open multiple times with different parameter sets.
2. You're using a client/server database (i.e., SQL Server, Oracle, Interbase, etc.)

The first time you open the query will take as long or longer than without parameters, but once the server has the query "prepared" it will run faster for the consecutive opens where just parameters are changed. If you change the actual SQL, then it has to prepare the query every time it's opened.

-D

 

[VintageWine]

IMHO correct indexing on the tables in the database has more effect on speed than how you construct the query in Delphi. With the proviso that you use TQuery not TTable with client/server databases.

Simon

 

[LucieLastic]

hi Simon

Can I just add that although indexing plays a big part when it comes to retrieving data, how a query is written can determine whether an index is used at all(even if a field has an index). If the query is not optimised, the server will perform a full table scan. Also, simple things like SARGs (eg use >= <= rather than 'between') as this can affect how the query is executed. ...I'll stop there as this is a large subject and this is not really the right forum.

Going back to my post, I asked as I believe that Delphi has to do a lot more work to retrieve a live dataset rather than just a read only one (am I right?) and was wondering if the difference in processing is worth the code change. I'm also wondering whether to change it all to ADO and if this'll make a big difference.

I find SQL/DBs interesting (I should get out more), and I'm really grateful of everyone's input.

lou

 

[VintageWine]

Totally agree. The query itself matters as you say. My comment is badly worded.

I use live queries in one project with no noticeable impact on data retrieval time

I don't know for sure, but I suspect that the BDE does do extra work in respect of the retrieved records, but not to find them. If you fetch 30 records from 1 million record table, there will be no noticeable slow down by going to live datasets, as the extra work is only done for the 30 records. However if you retrieve all 10,000 records from another table there will be.

Using live datasets client/server can generate a lot of update SQL which can slow things down by virtue of volume of database requests.

I've had grief with blob fields and live datasets. The problem comes when you retrieve more than 64 records, although the 64 limit can be extended.

Unless your circumstances are exceptional using ParamByName or not will have no noticeable impact on execution speed. As hilfy says, using parameters can be the faster option.

As for ADO, I find it much better than the BDE for SQL Server and Access. If you are using DBase or Paradox tables the BDE is as good as any.

I hope this is of some use to you
Simon