Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Any idea why the speed difference |
Fri, Nov 6 2009 8:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | From unprepared you get the figures below. Subsequent runs without the Fetchall parameter EDBManager quotes 0 with the parameter 0.047 secs
SELECT _ID, _fkContacts, _fkStaff, _InOutInd, _ELNtype, IF(_aList IS NOT NULL,TRUE,FALSE) AS _HasAttachments, _Subject, _Comments, _Timestamp FROM ELN WHERE _fkContacts = :IDToMatch AND (:FetchAll OR(_TimeStamp >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH)) ORDER BY _timestamp DESC SQL Query (Executed by ElevateDB 2.03 Build 5) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ SELECT ALL "_ID" AS "_ID", "_fkContacts" AS "_fkContacts", "_fkStaff" AS "_fkStaff", "_InOutInd" AS "_InOutInd", "_ELNtype" AS "_ELNtype", IF("_aList" IS NOT NULL, TRUE, FALSE) AS "_HasAttachments", "_Subject" AS "_Subject", "_Comments" AS "_Comments", "_Timestamp" AS "_Timestamp" FROM "ELN" WHERE "_fkContacts" = 1007387 AND (FALSE OR ("_TimeStamp" >= CURRENT_TIMESTAMP() - INTERVAL '6' MONTH)) ORDER BY "_Timestamp" DESC Source Tables ------------- ELN: 19371 rows Result Set ---------- The result set was sensitive The result set consisted of zero or more rows The result set was ordered using the index Timestamp Filtering --------- The following filter condition was applied to the Query table: "_fkContacts" = 1007387 [Index scan (ELN.Contact):, 989 keys, 16384 bytes estimated cost] AND (FALSE OR ("_TimeStamp" >= CURRENT_TIMESTAMP() - INTERVAL '6' MONTH [Index scan (ELN.Timestamp):, 274 keys, 12288 bytes estimated cost])) ================================================================================ 36 row(s) returned in 1.045 secs ================================================================================ SELECT _ID, _fkContacts, _fkStaff, _InOutInd, _ELNtype, IF(_aList IS NOT NULL,TRUE,FALSE) AS _HasAttachments, _Subject, _Comments, _Timestamp FROM ELN WHERE _fkContacts = :IDToMatch AND _TimeStamp >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH ORDER BY _timestamp DESC ================================================================================ SQL Query (Executed by ElevateDB 2.03 Build 5) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ SELECT ALL "_ID" AS "_ID", "_fkContacts" AS "_fkContacts", "_fkStaff" AS "_fkStaff", "_InOutInd" AS "_InOutInd", "_ELNtype" AS "_ELNtype", IF("_aList" IS NOT NULL, TRUE, FALSE) AS "_HasAttachments", "_Subject" AS "_Subject", "_Comments" AS "_Comments", "_Timestamp" AS "_Timestamp" FROM "ELN" WHERE "_fkContacts" = 1007387 AND "_TimeStamp" >= CURRENT_TIMESTAMP() - INTERVAL '6' MONTH ORDER BY "_Timestamp" DESC Source Tables ------------- ELN: 19371 rows Result Set ---------- The result set was sensitive The result set consisted of zero or more rows The result set was ordered using the index Timestamp Filtering --------- The following filter condition was applied to the Query table: "_TimeStamp" >= CURRENT_TIMESTAMP() - INTERVAL '6' MONTH [Index scan (ELN.Timestamp):, 274 keys, 12288 bytes estimated cost] AND "_fkContacts" = 1007387 [Index scan (ELN.Contact):, 989 keys, 16384 bytes estimated cost] ================================================================================ 36 row(s) returned in 0.187 secs ================================================================================ |
Sat, Nov 7 2009 11:31 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< From unprepared you get the figures below. Subsequent runs without the Fetchall parameter EDBManager quotes 0 with the parameter 0.047 secs >> You'll notice that the second version of the WHERE clause is rewritten by the optimizer so that the TIMESTAMP condition executes first. For some reason, the parameter is messing up the ability of the optimizer to rewrite the expression. If you want to send me the database catalog and table, I'll give it a run here and see what I can find. -- Tim Young Elevate Software www.elevatesoft.com |
Sun, Nov 8 2009 5:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>You'll notice that the second version of the WHERE clause is rewritten by >the optimizer so that the TIMESTAMP condition executes first. For some >reason, the parameter is messing up the ability of the optimizer to rewrite >the expression. If you want to send me the database catalog and table, I'll >give it a run here and see what I can find. I was looking at the amount of data scanned. I didn't realise the order was that important. I thought it would run both then combine. If you keep the stuff I send its the same catalog and the ELN table (c 1Gb now) Roy Lambert |
Sat, Nov 14 2009 1:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< If you keep the stuff I send its the same catalog and the ELN table (c 1Gb now) >> I have your recent catalog, but no copy of the ELN table that isn't empty. Send me an email and I'll give you instructions on how to send me a .zip of the table files via EDB stores. -- Tim Young Elevate Software www.elevatesoft.com |
Sun, Nov 15 2009 6:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I set up my app for export and import so that when I totally trash the development db I can re-import the live one so I'll email you a zip of the export file. I'll also send the latest catalog. I'll be interested on you comments on these stats ELN.EDBBlb, 707,116,544 bytes ELN.EDBIdx, 39,124,992 bytes ELN.EDBTbl, 9,203,584 bytes ELN.TfRE, 110,032,651 bytes and out of interest ELN.7z, 16,528,550 bytes My guess is I have the blob block size wrong Roy Lambert |
Mon, Nov 16 2009 3:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I'll be interested on you comments on these stats ELN.EDBBlb, 707,116,544 bytes ELN.EDBIdx, 39,124,992 bytes ELN.EDBTbl, 9,203,584 bytes ELN.TfRE, 110,032,651 bytes and out of interest ELN.7z, 16,528,550 bytes My guess is I have the blob block size wrong >> I wouldn't even hazard a guess without looking at the actual data and doing an analysis of the BLOBs. Comparing an export file to the binary files is an exercise in futility due to the extra formatting in the export file. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Nov 17 2009 2:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>the extra formatting in the export file. That was my point. I would have expected the export file to be bigger not 1/8th of the size Roy Lambert |
Tue, Nov 17 2009 6:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< That was my point. I would have expected the export file to be bigger not 1/8th of the size >> Yes, but that wasn't my point. My point was that the extra formatting is just another issue in making the comparison impossible. I would actually expect the EDB table files to be much bigger, namely because of the fixed-length rows, BLOB blocks, and index pages. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Nov 18 2009 2:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>Yes, but that wasn't my point. My point was that the extra formatting is >just another issue in making the comparison impossible. I would actually >expect the EDB table files to be much bigger, namely because of the >fixed-length rows, BLOB blocks, and index pages. Fixed length fields - yep no problem and expected, index pages - red herring cos they aren't exported blob blocks - this is the real kicker; I think I need a tool to see what the utilisation is like. I did write one for DBISAM I'll have to dust it off unless there's one built in somewhere Roy Lambert |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |