Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 15 of 15 total |
Filtering Very slow using DBISAM 4.26 Build 2 |
Tue, May 6 2008 9:30 AM | Permanent Link |
Francois Steyn | Hello Roy,
Attached is the debttran plan and structure created by DBSYS. Can you please explain what I am missing in my tests. I have done multiple tests, so if buffering was changing the results, it would change all the results. >>All this still does not explain why a table with filter is slower than a query with a >>where clause, both working on order of entry or a secondary index. >It does if the filter is executed and then the query is run without a reboot. OK, but.... the filter is giving me the same results every time and the query too... Meaning if the query would benefit from the filter (buffer) the filter should have the same benefit, right? Is your data situated on a fileserver on the network or local disk? Ive just executed the sql first using dbsys, then opened the debttran table and applied the filter... Still the same results... Query is 1.3 secs and Filter takes 27secs.... Can you please explain why I would get far better results using DBISAM3? Kind Regards Francois Attachments: debttran-plan.txt |
Tue, May 6 2008 10:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Francois
>Can you please explain what I am missing in my tests. Not yet, and possibly never >I have done multiple tests, so if >buffering was changing the results, it would change all the results. Possibly. What are the results, just for filtering, if you run it say 3 times in DBSys? >>>All this still does not explain why a table with filter is slower than a query with a >>>where clause, both working on order of entry or a secondary index. No, there I think you have to accept what Tim is saying - its down to the table having to reconcile back to the index - a query doesn't. >>It does if the filter is executed and then the query is run without a reboot. >OK, but.... the filter is giving me the same results every time and the query too... >Meaning if the query would benefit from the filter (buffer) the filter should have the >same benefit, right? I do wish that was the case but in the land of M$ its not necessarily true. >Is your data situated on a fileserver on the network or local disk? Those were local, and against an integer indexed field. I'd missed the fact that you were selecting from a non-indexed field, over a network. All that means is that you could have multiple levels of buffering. >Ive just executed the sql first using dbsys, then opened the debttran table and applied >the filter... Still the same results... Query is 1.3 secs and Filter takes 27secs.... This is something that does puzzle me. I've never been able to get really consistent results usually between 10% and 50% different on different runs. >Can you please explain why I would get far better results using DBISAM3? Nope. Unfortunately I don't have V3 installed any more, but as a guess (TIM) it could be the way floats are compared. One other thing I would like you to try is add an index to the amount field and see what results you get then. Roy Lambert [Team Elevate] |
Wed, May 7 2008 12:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Francois,
<< Attached is the debttran plan and structure created by DBSYS. >> Okay, first of all the query plan says that you're executing it with 4.24, not 4.26. However, that won't make much difference. Secondly, the query is un-optimized, and is requiring a brute-force scan of the table. The reason that 4.x is slower than 3.x is because 4.x has a property called TableMaxReadLockCount in the TDBISAMEngine component that allows for the engine to release a read lock during brute-force table scans in order to improve concurrency. 3.x doesn't do this, and so requires a lot less read locks for a table scan, but at the price of reduced concurrency (no writing can take place during the table scan): http://www.elevatesoft.com/manual?action=mancompprop&id=dbisam4&product=d&version=7&comp=TDBISAMEngine&prop=TableMaxReadLockCount To get the 3.x performance in 4.x, just set this property to 1. However, I would suggest that you add an index instead to optimize the WHERE clause so that a table scan is not required, especially if this query or filter is particularly time-sensitive and is executed frequently. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jun 6 2008 6:06 AM | Permanent Link |
Ries van der Velden | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
>http://www.elevatesoft.com/manual? >action=mancompprop&id=dbisam4&product=d&version=7&comp=TDBISAMEngine&prop=TableMaxReadLockCount >To get the 3.x performance in 4.x, just set this property to 1. Hello Tim, I've read this post with great interest, because we've got customers complaining about performance since we've switched from 4.25 build 7 to 4.26 build 1. I did some testing with the property TableMaxReadLockCount, setting it to 1 and setting it to 50.000. But the strange thing is, that the overal performance [reading, writing, filtering, queries] of the application is much better when I set the value to 50.000. We've got a client server solution with 50 concurrent users. I know that this description is very general, but do you have any idea why we've got this behaviour? Looking at the manual regarding this property, I would have suspected the opposite behaviour. Thanks in advance. |
Fri, Jun 6 2008 4:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ries,
<< I've read this post with great interest, because we've got customers complaining about performance since we've switched from 4.25 build 7 to 4.26 build 1. >> You may be experiencing this issue instead: http://www.elevatesoft.com/incident?action=viewrep&category=dbisam&release=4.26&type=f&incident=2612 If the filters that are slow are using compound expressions, then download/install/compile with 4.26 B2 and see if that helps. -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |