Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 15 total |
Filtering Very slow using DBISAM 4.26 Build 2 |
Mon, May 5 2008 10:53 AM | Permanent Link |
Francois Steyn | Hello,
We are using DBISAM 4.26 Build 2 and Delphi 7. We have the following scenario: Using a transaction table with 54000 records we apply the following filter: amount between 2000 and 2100 Resultset after filter: 715 records Amount is not indexed, executed using dbsys over network (1gigabit) using normal windows file shares(dedicated server) SQL Statement: select * from debttran where amount between 2000 and 2100 DBISAM 4.26 Build 2: Table with Filter: Takes 27 Seconds DBISAM 4.26 Build 2: SQL: takes 1.266 Seconds (with want live checked) DBISAM 4.26 Build 2: SQL: takes 3.625 Seconds (without want live checked) DBISAM 3.27: Table with Filter: Takes 9 Seconds DBISAM 3.27: SQL: takes 0.328 Seconds (with want live checked) DBISAM 3.27: SQL: takes 0.485 Seconds (without want live checked) Any ideas?? If you need the data, we can provide some test data. Thanks! Francois Steyn |
Mon, May 5 2008 11:34 AM | Permanent Link |
Fernando Dias Team Elevate | Francois,
> DBISAM 4.26 Build 2: Table with Filter: Takes 27 Seconds > DBISAM 4.26 Build 2: SQL: takes 1.266 Seconds (with want live checked) > DBISAM 4.26 Build 2: SQL: takes 3.625 Seconds (without want live checked) Did you execute the test actions in the listed order? What happens if you change the order - do you notice any significant change in times? -- Fernando Dias [Team Elevate] |
Mon, May 5 2008 11:42 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Francois,
<< DBISAM 4.26 Build 2: Table with Filter: Takes 27 Seconds DBISAM 4.26 Build 2: SQL: takes 1.266 Seconds (with want live checked) DBISAM 4.26 Build 2: SQL: takes 3.625 Seconds (without want live checked) DBISAM 3.27: Table with Filter: Takes 9 Seconds DBISAM 3.27: SQL: takes 0.328 Seconds (with want live checked) DBISAM 3.27: SQL: takes 0.485 Seconds (without want live checked) Any ideas?? >> What happens if you add an ORDER BY to the SELECT statement that is equivalent to the current index order for the table ? The results should be the same as with the table. IOW, the majority of the time isn't being spent in the filtering, it's being spent in reconciling the filtered set to the active index order for the purposes of re-positioning the current row. This has been discussed many times here on the newsgroups. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, May 6 2008 1:56 AM | Permanent Link |
Francois Steyn | Hello,
Fernando Dias, i did execute the steps as they are listed, but it doesnt matter which order since those are averages. I ran all the tests multiple times with no difference. The queries were executed, then unprepared and re-executed. Tim Young, the index of the table is the primary key, which is an auto number. If im not mistaken, when you omit the order by, it will be ordered by the order of entry, which will be equivalent to the auto number. If the majority of the time isnt spent filtering, but ordering, i still dont see why filtering is soo much slower than the query. I reckon both test cases share the same properties. Also, i dont see why DBISAM3 is 3-4 times faster than DBISAM4. I thought this was suppose to be the other way around. How can I speed up my test results?? Thanks for all the support! Kind Regards Francois |
Tue, May 6 2008 5:24 AM | Permanent Link |
Fernando Dias Team Elevate | Francois,
> Fernando Dias, i did execute the steps as they are listed, but it doesnt > matter which > order since those are averages. I ran all the tests multiple times with > no difference. > The queries were executed, then unprepared and re-executed. Are you running the SQL tests mantaining the filtered table open in dbsys? Because if you are, I think it can explain all - data is being buffered so the subsequent operations will run much faster than the first one. That's why I asked you to change the execution irder: execute first an SQL query, leave it open and then apply the filter to the table - I suspect you will obtain very different results, caused by the same : buffering. -- Fernando Dias [Team Elevate] |
Tue, May 6 2008 6:14 AM | Permanent Link |
Francois Steyn | Hello,
I was running all the tests keeping DBSYS and tables open. I ran all the test again. I closed DBSYS after every test and still got the same results. How would I switch on buffering in DBSYS? Kind Regards Francois |
Tue, May 6 2008 6:42 AM | Permanent Link |
Fernando Dias Team Elevate | Francois,
> I was running all the tests keeping DBSYS and tables open. > I ran all the test again. I closed DBSYS after every test and still got > the same results. I find this really strange... Maybe Tim can bring some light to this because now I'm really confused. I was really expecting that changing the order should change the results. > How would I switch on buffering in DBSYS? It's always on. -- Fernando Dias [Team Elevate] |
Tue, May 6 2008 8:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Francois
The buffering Fernando is talking about is Windows buffering. When you open a table and run a query or set a filter Windows may buffer large chunks of the table essentially meaning that subsequent operations do not require disk access. Even closing the table or flushing the buffers down to disk may not (I believe) clear these buffers. In the event of a flush the OS would have to check to make sure they were current at least. The only way I know to "even" out the effect is reboot the PC after each test. Running each test several times and only taking the last result is also a good way, but not guaranteed unless you have a magic wand that makes Windows do just what you want. Roy Lambert [Team Elevate] |
Tue, May 6 2008 8:25 AM | Permanent Link |
Francois Steyn | Hello Roy,
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. Is DBISAM3 suppose to be faster than DBISAM4? Am I missing something with my tests? Kind Regards Francois Steyn |
Tue, May 6 2008 8:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Francois
>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. Table size - 216218 records filter: _BoxNo between 100 and 250 - 57k results c1.6 secs sql - select * from mandn where _boxno between 100 and 250 canned c130 secs live c.2 secs rerun filter after sql and c.0 secs These are about the sort of results I'd expect, especially since I have another app hitting the disk >Is DBISAM3 suppose to be faster than DBISAM4? No >Am I missing something with my tests? Yes. Can you post the table structure and query plans? Roy Lambert [Team Elevate] |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |