Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread Parenphases in where clause
Fri, Oct 20 2017 4:33 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 AMPermanent 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 Smile

David.
Fri, Oct 20 2017 10:14 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 PMPermanent 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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
Image