We help IT Professionals succeed at work.

Speeding up Queries that have CachedUpdates

greendot
greendot asked
on
Medium Priority
526 Views
Last Modified: 2010-04-04
Using Delphi 3, BDE 4.51 to get at paradox/foxpro tables with LocalSQL.

Recently, I had to move my Queries over from live to CachedUpdates.  As soon as I set the TQuery.CachedUpdates property to True, the performance slows to a crawl.

Are there some settings that speed that up?  Either in the BDE or an object?

Thanks,
joe
Comment
Watch Question

Commented:
Hello greendot,

      There are many ways to improve queries. Here go some ideas:


1 - Use the Prepare method of TQuery objects before opening them. It sends an SQL query to the database engine for parsing and optimization. If Prepare is called explicitly for a query that that will be executed more than once, Delphi sends only the query parameters - not the entire text - with each successive execution. This make query operations run faster.

2 - UpdateMode - TTAble and TQuery have an UpdateMode property. This determines the type of WHERE clause used to perform modifications using dataware comtrols. It defaults to UpWhereAll, which means that BDE generates a WHERE clause that list every column in a table. An alternative (faster) is to use UpWhereCanged setting. It generates a WHERE clause that mentions only the table's key fields along with the fields that were changed.

3 - Schema caching - BDE supports schema caching -  storing structural information about database object locally. Enabling schema caching can reduce the queries sent by your app to server to retrieve database catalog information. This can speed up your app significantly cause BDE is not forced to constantly re-retrieve meta-data from server.
You can enable schema caching in the BDE Admnistration Utility. There are four settings that are related to schema caching.

ENABLE SCHEMA CACHE                  Enable/Disable Schema Caching

SCHEMA CACHE SIZE               Number of tables for witch to                                     cache schema data

SCHEMA CACHE TIME               Specifies the number of seconds
                                to cache schema information

SCHEMA CACHE DIR                directory to store schema info.


Sorry if i was a bit verbous...

I hope it can help you
   
                         []'s
                                 Oberdan

Author

Commented:
I already had those set. :)

The schema cache is for a different type of database setup.  This is all local, no servers.  We get to use SQL on top of local tables.  It's neat, but sometimes it's a pain in the you-know-what.  

The BDE does not give you many settings when doing this LocalSQL stuff.

Thanks for trying.  :)

-gd
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.