Solved

Speeding up Queries that have CachedUpdates

Posted on 1998-09-04
3
399 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
0
Comment
Question by:greendot
3 Comments
 

Expert Comment

by:oberdan
ID: 1338630
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

0
 
LVL 2

Author Comment

by:greendot
ID: 1338631
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
0
 

Accepted Solution

by:
manolis_afentakis earned 100 total points
ID: 1338632

i don't know if u tried this:

A         set cached updates only before updating ,
B         or read this about index performance (for local databases:)

 For each field you intend to search or query, you need a:  (1) maintained, (2) case-
sensitive, (3) single-field secondary index.  This is essentially the only type of index that a
query will use.

 Maintained indexes -require- that there be a primary key.  In PxWin you can simply use
the "autoincrement" field-type to create one if you don't have one.  Paradox for DOS users
have to do the same thing manually but it's still worth doing so that you can get the other
indexes.

 The necessity of indexes can be demonstrated by this:  in your Yellow Pages, please find
the first Accountant.  (That was easy.)  Now find Joe Smith, CPA...  (Hope he's under
Accountants!) Now find the phone-number 543-2109.  :-)  A computer will obligingly attempt
any one of these operations even though you, the impatient human, would never subject
yourself to the same labor!

 Regardless of what software you use, indexes are THE most important factor in database
performance and you should make yourself very, very familiar with them.  Given the extreme
overhead of transferring large amounts of unwanted data through a network, the overhead of
maintaining the indexes pales in comparison.


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy file in dll not working but working on exe ! 18 88
Delphi XE10, MySQL Query 4 126
Base1 Encode/Decode 3 67
DBGrid or StringGrid ? 6 65
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now