Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
SQL Vs filter speed |
Sat, Aug 12 2006 9:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | V4.24
I'm experimenting with a transaction table that will grow over the years. I've created a test with c350k records to check out speed. Essentially the table will record the results of calls (including the fact that the person called was not available). I don't know if its just Windows or network buffering but I'm getting some interesting results. I seeded the table with a few records where I know the call number (either 9 or 11) and tried running the following select * from perfstats where _fkcalls = 9 order by _date desc, _time desc When _fkcalls is 9 the DBSys reported time is c.07secs but if its 11 then its c.8secs it 10x longer. The only germane difference is that the 9's are all fairly near each other in the table in primary index order whilst the 11's are scattered throughout - I deliberately made sure some were are either "end" of the table. I can sort of accept that but then I tried opening the table, setting the index to decending date/time order and setting a filter - result felt instant for both 9's and 11's Finally I (for some reason) ran the SQL with the table still open in DBSys with the index set to the decending date/time and for both 9's and 11's the DBSys time is zero. Then I closed the table and ran the sql and back to the previous times. The sql was all done asking for and receiving a live result set, and without unpreping the query (Canned took between 3 - 4 secs for both) Any thoughts / comments / observations as to what will happen with ElevateDB? Roy Lambert |
Mon, Aug 14 2006 8:37 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< When _fkcalls is 9 the DBSys reported time is c.07secs but if its 11 then its c.8secs it 10x longer. The only germane difference is that the 9's are all fairly near each other in the table in primary index order whilst the 11's are scattered throughout - I deliberately made sure some were are either "end" of the table. >> Did you run some query plans on these tests ? If so, could you post them ? << Any thoughts / comments / observations as to what will happen with ElevateDB? >> It will be faster....... ? Actually, until I see the query plan(s), I can't really say what is going on or what is responsible. It could just very well be an issue with the speed of the Windows file system due to the organization of the table files on disk, and once the table files have been cached by Windows, the speed improves. This actually happens quite often. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Aug 14 2006 9:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Being thick I forgot - just tried again ran queries in the sequence as shown by the plans below so if there was any improvement due to caching I'd expect it to benefit the second one. I'm surprised since I thought DBISAM would use the compound index (CREATE INDEX IF NOT EXISTS "MostRecentFirst" ON "PerfStats" ("_Date" DESC,"_Time" DESC)) ;I have for ordering but no. I tried creating two new descending indices for _Date and _Time but still the same sort of result just a bit faster (plans 3 & 4 below). ================================================================================ SQL statement (Executed with 4.24 Build 1) ================================================================================ select * from perfstats where _fkcalls = 9 order by _date desc, _time desc Tables Involved --------------- perfstats (perfstats) table opened shared, has 354590 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-sensitive temporary index: _Date DESC _Time DESC WHERE Clause Execution ---------------------- The expression: _fkcalls = 9 is OPTIMIZED, covers 42 rows or index keys, costs 529 bytes, and will be applied to the perfstats table (perfstats) before any joins ================================================================================ >>>>> 42 rows affected in 0.078 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.24 Build 1) ================================================================================ select * from perfstats where _fkcalls = 11 order by _date desc, _time desc Tables Involved --------------- perfstats (perfstats) table opened shared, has 354590 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-sensitive temporary index: _Date DESC _Time DESC WHERE Clause Execution ---------------------- The expression: _fkcalls = 11 is OPTIMIZED, covers 47 rows or index keys, costs 592 bytes, and will be applied to the perfstats table (perfstats) before any joins ================================================================================ >>>>> 47 rows affected in 0.344 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.24 Build 1) ================================================================================ select * from perfstats where _fkcalls = 9 order by _date desc, _time desc Tables Involved --------------- perfstats (perfstats) table opened shared, has 354590 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-sensitive temporary index: _Date DESC _Time DESC WHERE Clause Execution ---------------------- The expression: _fkcalls = 9 is OPTIMIZED, covers 42 rows or index keys, costs 529 bytes, and will be applied to the perfstats table (perfstats) before any joins ================================================================================ >>>>> 42 rows affected in 0.046 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.24 Build 1) ================================================================================ select * from perfstats where _fkcalls = 11 order by _date desc, _time desc Tables Involved --------------- perfstats (perfstats) table opened shared, has 354590 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-sensitive temporary index: _Date DESC _Time DESC WHERE Clause Execution ---------------------- The expression: _fkcalls = 11 is OPTIMIZED, covers 47 rows or index keys, costs 592 bytes, and will be applied to the perfstats table (perfstats) before any joins ================================================================================ >>>>> 47 rows affected in 0.234 seconds ================================================================================ Roy Lambert |
Sun, Aug 20 2006 10:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Any thoughts? Roy Lambert |
Mon, Aug 21 2006 2:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Any thoughts? >> Sorry about that - I missed your response in all of my machine woes this week. As far as the timings are concerned, differences below one second can be caused by just about anything on the machine from longer seek times in the files due to fragmentation to blips in the file system caching. If the records being retrieved are scattered throughout the table, then I would guess that the first description would fit (seek times). As for the descending index issue, I'm not sure what is going on short of you not setting RequestLive=True. I tried the same thing here: ================================================================================ SQL statement (Executed with 4.24 Build 1) ================================================================================ SELECT * FROM customer ORDER BY City DESC, State DESC Tables Involved --------------- customer (customer) table opened shared, has 55 rows Result Set Generation --------------------- Result set will be live Result set will consist of one or more rows Result set will be ordered by the secondary index Test for the table customer ================================================================================ >>>>> 55 rows affected in 0 seconds ================================================================================ and it worked fine. The index 'Test' was defined as: CREATE INDEX Test ON Customer (City DESC, State DESC) -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Aug 22 2006 3:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
PC's are great when they go wrong aren't they Seek times (41Mbn dat, 122Mb idx) could have an impact, but fragmentation is improbable since it was a brand new file, and the one I did the testing on was a copy on a USB drive intended to deliberately slow things down. I've gone for a TDBISAMTable and a filter since it was consistently fast for either case (faster than sql as well) Roy Lambert |
This web page was last updated on Monday, June 17, 2024 at 07:11 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |