Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Parenphases in where clause |
Fri, Oct 20 2017 4:33 AM | Permanent Link |
David | I have a query that I have noticed is going slower that I would like. If I wrap parentheses around some of the where clause it goes from ~>3seconds down to less than 0.156 seconds! This is awesome but I was wondering if anyone could help explain what is happening here. Should I have been doing this for all my queries to group conditions on the same table together?
Slow Query Select ID,CoyID,t.ContractID,TestItemNo,VialNo,LabelInfo,PreBookedIn,UnitOfMeasure,Disposed,Volume,TIGMO,TIRiskAssessmentSupplied,CurrentStatus from TestItemReceipt a join TIContract t on (a.ID=t.TestItemID) where t.ContractID = 2695 and a.CoyID =1169 and a.Disposed = false and a.RecievedDateTime <> null group by TestItemNo,VialNo Order By TestItemNo,VialNo Asc Fast Query Select ID,CoyID,t.ContractID,TestItemNo,VialNo,LabelInfo,PreBookedIn,UnitOfMeasure,Disposed,Volume,TIGMO,TIRiskAssessmentSupplied,CurrentStatus from TestItemReceipt a join TIContract t on (a.ID=t.TestItemID) where t.ContractID = 2695 and (a.CoyID =1169 and a.Disposed = false and a.RecievedDateTime <> null) group by TestItemNo,VialNo Order By TestItemNo,VialNo Asc |
Fri, Oct 20 2017 7:18 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
As a guess your concern about the tests being carried out separately is correct and the tests are carried out individually and the results aggregated and with the brackets the tests are carried out at the same time. However, this looks like the same query in your other post where the time taken was 0.031 seconds and now you say "~>3seconds down to less than 0.156 seconds!". I'm confused. Roy Lambert |
Fri, Oct 20 2017 8:26 AM | Permanent Link |
David | Hi Roy.
It is more or less the same. I introduced a CoyID field to further reduce the number of rows produced. So now the query is Select ID,CoyID,t.ContractID,TestItemNo,VialNo,LabelInfo,PreBookedIn, UnitOfMeasure,Disposed,Volume,TIGMO,TIRiskAssessmentSupplied,CurrentStatus from TestItemReceipt a join TIContract t on (a.ID=t.TestItemID) where t.ContractID = 2695 and (a.CoyID =1169 and a.Disposed = false and RecievedDateTime <> null) and this is the where part from the query plan WHERE Clause Execution ---------------------- The expression: t.ContractID = 2695 is OPTIMIZED, covers 8 rows or index keys, costs 100 bytes, and will be applied to the TIContract table (t) before any joins The expression: a.CoyID = 1169 and a.Disposed = false and RecievedDateTime <> null has been rewritten and is PARTIALLY-OPTIMIZED, covers 71 rows or index keys, costs 226958 bytes, and will be applied to the TestItemReceipt table (a) before any joins Removing the parentheses results in this query plan WHERE Clause Execution ---------------------- The expression: t.ContractID = 2695 is OPTIMIZED, covers 8 rows or index keys, costs 100 bytes, and will be applied to the TIContract table (t) before any joins The expression: a.CoyID = 1169 is OPTIMIZED, covers 71 rows or index keys, costs 894 bytes, and will be applied to the TestItemReceipt table (a) before any joins The expression: a.Disposed = false is OPTIMIZED, covers 27731 rows or index keys, costs 271763 bytes, and will be applied to the TestItemReceipt table (a) before any joins The expression: RecievedDateTime <> null is OPTIMIZED, covers 35915 rows or index keys, costs 653653 bytes, and will be applied to the TestItemReceipt table (a) before any joins So my question is, is using the parenthesis the correct way to do this query as it procures less rows in the joined table even though this is further reduced by the join? I hope this makes sense David. |
Fri, Oct 20 2017 10:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
It makes sense, but I'm only guessing so we'll have to wait for Tim to give the definitive answer. Roy Lambert |
Fri, Oct 20 2017 5:00 PM | Permanent Link |
David | Thanks Roy.
Yes would be good to get some feedback from Tim if he can. I am sure he is a busy man though! Cheers David |
Mon, Oct 23 2017 12:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< and this is the where part from the query plan >> Yes, this is an expected result with DBISAM. It uses the "natural" specification of the WHERE conditions as a hint as to how to optimize the conditions, so it's always best to group like conditions together to ensure that they are considered as a whole. Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 24 2017 1:30 PM | Permanent Link |
David | Hi Tim.
So does that mean that parenthesis needs to be used to enforce this or that they just need to be in a logical order? I need to re-look at the way I have formed some of my Where clauses. Regards David Tim Young [Elevate Software] wrote: David, << and this is the where part from the query plan >> Yes, this is an expected result with DBISAM. It uses the "natural" specification of the WHERE conditions as a hint as to how to optimize the conditions, so it's always best to group like conditions together to ensure that they are considered as a whole. Tim Young Elevate Software www.elevatesoft.com |
Wed, Oct 25 2017 11:41 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< So does that mean that parenthesis needs to be used to enforce this or that they just need to be in a logical order? >> The parentheses provide the grouping/ordering of conditions that DBISAM needs before it can consider performing the optimization related to directly reading records instead of using indexes. If you want to email me the relevant database table files, I can tell you exactly what the DBISAM query optimizer is doing. There's a lot of small technical details that aren't included in the query executions plans because they're "inside baseball", but they would tell me what's going on when I trace the execution in the actual code. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |