Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Query SLOW with tables open |
Wed, Dec 4 2013 10:53 AM | Permanent Link |
Alessandra Romano | I've a quite complex query that does execute in 1.5 seconds in DBSYS,
but requires: 2.0 seconds in my program, if run with tables (the same involved in the query) CLOSED 9.2 seconds in my program, if run with tables (the same involved in the query) OPEN Why such a difference? Also where tables are not connected to any VCL db-control, the difference is exactly the same. Using 4.37b3 + D2010 Thx A. |
Wed, Dec 4 2013 11:31 AM | Permanent Link |
Matthew Jones | > Why such a difference?
I'd be looking at things like the strict/optimistic change detection, and other such parameters that affect lookups. /Matthew Jones/ |
Thu, Dec 5 2013 2:13 AM | Permanent Link |
Alessandra Romano | > I'd be looking at things like the strict/optimistic change detection,
> and other such parameters that affect lookups. thanks Matthew, i discovered that the execution time was related to engine's buffers (i've customized the engine) and the tables, if opened and not used, seems to keep some (a lot?) of buffers busy, slowing down the query. A. |
Thu, Dec 5 2013 4:43 AM | Permanent Link |
Matthew Jones | Indeed. In some cases, less buffers can speed things up. There is also an
"exclusive" mode, and one of my applications uses this when it knows it is in "single thread" mode to get a small speed up. Optimisation becomes something of measure and adjust to find the best for your situation. /Matthew Jones/ |
Mon, Dec 9 2013 4:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Alessandra,
<< thanks Matthew, i discovered that the execution time was related to engine's buffers (i've customized the engine) and the tables, if opened and not used, seems to keep some (a lot?) of buffers busy, slowing down the query. >> The tables being open or not should not affect the query execution time (other than the additional overhead to actually open the tables). However, if you increase the memory buffering settings too high, you can most definitely affect the performance in a negative way. How high did you set the engine's buffering settings ? Tim Young Elevate Software www.elevatesoft.com |
Mon, Dec 9 2013 8:35 PM | Permanent Link |
Alessandra Romano | > The tables being open or not should not affect the query execution
> time (other than the additional overhead to actually open the > tables). However, if you increase the memory buffering settings too > high, you can most definitely affect the performance in a negative > way. How high did you set the engine's buffering settings ? Hi, Tim, i multiply the default engine values by a factor of 500: MaxTableDataBufferSize:=DEFAULT_RECORD_BUFFER_SIZE*mult; // total amount of memory to use for record buffers in the buffer cache per physical table MaxTableDataBufferCount:=DEFAULT_RECORD_BUFFER_COUNT*mult; // maximum number of record buffers to allow in the buffer cache per physical table MaxTableIndexBufferSize:=DEFAULT_PAGE_BUFFER_SIZE*mult; // total amount of memory to use for index page buffers in the buffer cache per physical table MaxTableIndexBufferCount:=DEFAULT_PAGE_BUFFER_COUNT*mult; // maximum number of index page buffers to allow in the buffer cache per physical table MaxTableBlobBufferSize:=DEFAULT_BLOCK_BUFFER_SIZE*mult; // total amount of memory to use for BLOB block buffers in the buffer cache per physical table MaxTableBlobBufferCount:=DEFAULT_BLOCK_BUFFER_COUNT*mult; // maximum number of BLOB block buffers to allow in the buffer cache per physical table in further experiments, i noticed that with such a value the tables being open and NOT used (no forms, no vcl attached) slow down the query a lot. In the same scenario: Using mult:=1, the query executes in about 1 second Using mult:=1500, the query executes in about 0,4 seconds Can you please advice about a value of "mult" suitable in most cases? i guess that it can (and should) be different in case of main pc (using local tables) and netword pcs (using shared tables) Thanks in advance Tim! -- Alessandra |
Mon, Dec 16 2013 8:38 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Alessandra,
<< in further experiments, i noticed that with such a value the tables being open and NOT used (no forms, no vcl attached) slow down the query a lot. In the same scenario: Using mult:=1, the query executes in about 1 second Using mult:=1500, the query executes in about 0,4 seconds >> Are those numbers transposed ? I would expect the 1500 multiplier to be the slower, if what you're saying is correct. << Can you please advice about a value of "mult" suitable in most cases? i guess that it can (and should) be different in case of main pc (using local tables) and netword pcs (using shared tables) >> Yes, if you're using local tables, then the defaults should be fine. With shared, networked tables, you'll probably not want to go much above 1 or 2 MB. Anything higher is just going to result in excessive overhead without an accompanying increase in performance. This is because the cache is constantly needing to be dumped/refreshed due to changes by other users, and isn't a "permanent" cache. If you're doing a lot of batch processing, then you should look into using this property (it's new) to optimize any sequential access: http://www.elevatesoft.com/manual?action=viewprop&id=dbisam4&product=delphi&version=7&comp=TDBISAMDataSet&prop=LocalReadSize Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |