SQL Performance Optimization Tips (i)

About the effect of SQL condition matching on execution efficiency test

First, create a scalar function
Create function Ff_test ()

returns int

As

Begin

DECLARE @i int=0

while (@i<100000000)

Set @i+=1

Return @i

End

Next, select a random table, where you use the business table Mt_delegate

Observe the following two types of situations

1. Select * from mt_delegate where procid=-1 or dbo.ff_test () >10000

2. Select * from Mt_delegate where dbo.ff_test () >10000 or Procid=-1

The result is that the order of the Where condition statements does not affect execution, and the SQL engine clearly optimizes where conditions

Observe the following situation again

1.------------------------------------------------------------------------------------------------
Select A.* from Dbo.mt_delegate a
INNER JOIN Dbo.mt_delegate B on A.procid=b.procid and (dbo. Ff_test () >10000 or
A.ACTIVITYID=-1)

2.------------------------------------------------------------------------------------------------
Select A.* from Dbo.mt_delegate a
INNER JOIN Dbo.mt_delegate B on a.procid=b.procid and (A.activityid=-1 or
Dbo. Ff_test () >10000)

As a result, the query of 1 cases is obviously inefficient, and the 2 case results quickly, so it is presumed that SQL connection query condition execution is based on left-to-right order, not some articles say right match

conclusion, the SQL query connection query condition and the Where condition have different sequence requirements for the condition, and the connection query suggests that the large probability condition be placed on the left side

SQL Performance Optimization Tips (i)