7

I was reviewing an under-performing query which looks like this:

WHERE manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL)
ORDER BY aux.SortCode

I accidentally used SSMS query designer on this query and it re-wrote the query as follows:

WHERE manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P2
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND main.TextCol1 IS NOT NULL

   OR manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P2
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND main.TextCol2 IS NOT NULL
ORDER BY aux.SortCode

If you look closely you'll notice that it simply expanded the OR condition by repeating all conditions i.e. it changed a AND (b OR c) to (a AND b) OR (a AND c).

The resulting query was 50% smaller in terms of cost and 33% smaller in terms of execution time. I simply do not understand why re-arranging the OR condition changed the plan when both queries are identical (?). I could have expanded the OR condition myself by copy-pasting the conditions but why should I?

Paste the plan and screenshot:

Execution Plans

Number of rows:

main     2718
manymany 188761
aux      19

Notes:

  • TextCol1 and TextCol2 are text datatype and cannot be indexed
  • There are avg. 170.20 records in manymany table per website id
  • 1
    For to understand (or at least to try to do it) you must show table's DDL. A simple example - if there exists 2 separate indices which can be used to optimize (a AND b) and (a AND c) conditions separately, they can be used in "expanded" variant, but cannot in "base" one... – Akina Mar 6 at 8:11
  • @Akina I have covering indexes for all where clauses except the table that is involved in IS NOT NULL... those columns are of text datatype. – Salman A Mar 6 at 8:21
  • Adding the query plan's to pastetheplan.com would be helpful – Randi Vertongen Mar 6 at 8:51
  • If you paste whole query with all joins that will help everyone understand better. Also this depends on which table is aliased main,(how many records each table contains,) similarly apply for manymany and aux aliased tables and how you join them. – MarmiK Mar 6 at 9:33
  • 1
    Please add the CREATE TABLE statements 9with all indexes) and the full query. The FROM clause at least is essential in understanding how the plan may be produced. – ypercubeᵀᴹ Mar 6 at 10:58
2

But why doesn't SQL server sees both queries as one? After all, a AND (b OR c) = (a AND b) OR (a AND c)?

Logically it is the same, and it will get the same results.

Assumptions

My assumptions are that for the 'faster' plan, the optimizer is not considering some filter statements at the top of the OR to be the same as some filter statements at the bottom. I might be entirely off base here.

the reasoning for getting these assumptions are based on this filter predicate:

This filter predicate uses the result of the join between the Main table and manymany table. enter image description here

Notice that EXPR1021 and EXPR1022 in this filter are expressions created from the scalar operator on the manymany table.

enter image description here

This filter consists of two parts, the first one with (.. AND .. OR .. AND ..) and the second one plain AND filtering

(getdate()>=[Expr1021] 
AND getdate()<=[Expr1022] 
AND getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL 
OR getdate()>=[Expr1021] 
AND getdate()<=[Expr1022]
 AND getdate()>=[DB1].[dbo].[main].[FromDate] 
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL) 

 AND (getdate()>=[DB1].[dbo].[main].[FromDate] 
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
 AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate] 
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

As you can see, the only difference above and below the OR in the first part of this filter is

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

And the second part needs to be true no matter what, as they are AND predicates without any OR's.

Resulting in extra calculations of the same functions, that in my opinion are not needed. Again, my guess here is that the reason that sql server does these calculations is that it does not know that they are the same.

For some other parts of the where clause, it does know that these are the same, e.g. In the main table, the statusid = 1 is only evaluated once:

enter image description here

And in the manymany table, the same statement is evaluated twice:

enter image description here

In the 'slow' plan, the statements are not added together with OR clauses, and that is why the optimizer is generating a different plan, applying filter predicates on the tables separately (and no duplicate filters).

enter image description here

enter image description here

End of assumptions

Comparison of the two plans

I think that you got lucky with the performance of the 'fast' plan, but that the 'fast' plan might turn ugly when the matching data increases. It can depend on where and when you are applying your filters (and other factors).

The fast plan filtering

In the 'fast' plan: sql server applies some of the filters after the join of the main table with the manymany table as a result of different combinations with the two OR's + (AND ... AND ... AND...) blocks. The columns from the maintable are filtered after finding all the possible combinations with the manymany table.

As a result, the same predicate is executed twice on the manymany table:

enter image description here For the predicates above and below the OR.

But this is not the case for some of the seek predicates on the main table

enter image description here

After this the join happens, and an even bigger filter predicate on the results of the join between main and manymany happens, again for all the possible combinations enter image description here

Notice that EXPR1021 and EXPR1022 in this filter are expressions created from the scalar operator on the manymany table.

enter image description here

This filter consists of two parts, the first one with (.. AND .. OR .. AND ..) and the second one plain AND filtering

(getdate()>=[Expr1021] 
AND getdate()<=[Expr1022] 
AND getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL 
OR getdate()>=[Expr1021] 
AND getdate()<=[Expr1022]
 AND getdate()>=[DB1].[dbo].[main].[FromDate] 
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL) 

 AND (getdate()>=[DB1].[dbo].[main].[FromDate] 
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
 AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate] 
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

As you can see, the only difference above and below the OR in the first part of this filter is

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

And the second part needs to be true no matter what, as they are AND predicates without any OR's.

Resulting in extra calculations that in my opinion are not needed.

The slow plan filtering

In the 'slow' plan: sql server applies the filter directly to the Main table as a result of the AND (TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL) part and then joins with the manymany table to filter out the rest to get to 15 rows.

Main table filters

enter image description here

enter image description here

manymany table filters

enter image description here


Some other, sometimes overlapping information:

The slower plan

When we look at the slower plan, the clustered index PK_main is used, into a compute scalar, filter and nested loops operator:

enter image description here

When we compare this with the estimated rows to be returned, we see a difference: enter image description here

It is estimating 93 rows to be returned by the predicate on the scan:

enter image description here

Which is actually about 20x less than what was expected, which is 1947 rows.

Afterwards, the Compute scalar or this statement:

 , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
 , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating

is evaluated on these 1947 rows.

Then the filter operator (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL) to reduce it to 1374 rows.

After this, join these 1374 rows to the dbo.manymany table to get 15 rows returned.

The faster plan

The faster plan is using the NC index: CVR_main_4 on the dbo.Main table, enter image description here

It is filtering with a seek predicate, returning 27 rows to the nested loops Join operator, again joining with the dbo.manymany table.

And the actual rows returned are even lower than the estimated rows:

enter image description here

27 actual rows for an estimate of 152 rows

Filtering

A big difference is where the filtering happens, where on the 'slower' plan this is being done directly on the dbo.Main table:

With the predicate: TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL

enter image description here

And is applying this filter to 1943 rows.

With the other filtering happening directly on the dbo.manymany table

enter image description here (seek) predicates on dbo.manymany

While the other OR, on the 'faster' plan, is being filtered after the join from dbo.Main to dbo.manymany, and results in a much bigger filter, on the 27 rows.

enter image description here

Much bigger filter with multiple OR's on 27 rows.

Another difference is the Key lookup operator:

enter image description here

which gets 10 additional columns from the clustered index, but only has to do this for 27 rows.

enter image description here

enter image description here

Another reason that the optimizer chooses the 'slower' plan might be because the optimizer thinks not looking up the other columns would be better.


Is the fast plan even faster, or always going to be 'faster'?

I do think that, if the data passing through the filter increases, the 'slow' plan will be better. Not only due to the key lookup, but also due to the bigger filter operator further down in the plan.

If that happens, next to indexing. You could improve the filtering by splitting the query into multiple parts using a UNION statement.

Like so:

SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
     , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
     , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
  AND manymany.Check1 = -1
  AND manymany.Active = -1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND TextCol1 IS NOT NULL

 UNION 

 SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
     , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
     , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
  AND manymany.Check1 = -1
  AND manymany.Active = -1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND TextCol2 IS NOT NULL
ORDER BY SortCode;
  • But why doesn't SQL server sees both queries as one? After all, a AND (b OR c) = (a AND b) OR (a AND c)? – Salman A Mar 6 at 11:27
  • @SalmanA Lets say a belongs to the manymany table and B and C belongs to the Main table .For a AND (b OR c) it is resembling of the slow query, where b or c is directly filtered on the main table, as to then join with a (ManyMany) to get the remaining results. (a and b) or (a and c) is filtering after the join of the two tables, to filter these out after getting the resultset of the join Because a and c can only be filtered together with a and b. – Randi Vertongen Mar 6 at 12:13
  • @SalmanA SQL does not see its same because 1*1 and 1+1 is not same for SQL. This is computer and machine language at the end. If it becomes smart enough why DBA's are required.!? – MarmiK Mar 6 at 13:54
  • 2
    @RandiVertongen here you explain why the different plans have different performance and which one should be better, but I the OP's question is why writing 2 expressions that are completely equivalent affect the optimizer's plans. SQL is (in theory) a declarative language (not imperative), so we tell the engine what we want and not how it should do. For this example it seems that we can influence the how. – EzLo Mar 6 at 14:46
  • @EzLo You are correct, I have added my assumptions, I might be entirely off base though. – Randi Vertongen Mar 6 at 17:33
0

In the first query, It has to start with a scan whereas with the second it was able to utilize the non-clustered index for a seek.

Think about it like you have 100 marbles in a bag and you want to audit them to pick out only the ones that are blue and white or blue and red.

The first query is saying, look through each of the 100 marbles and pick out all the blue. Once you've done that, check all them and see if there are any with white or red.

The second query is saying go into the bag and only grab blue and white or blue and red marbles.

The second query would be faster because you wouldn't have to look at each marble for just the blue first. You could combine that step with what you really wanted which was blue and white or blue and red.

That's how I look at it anyways. Ultimately the first query required a table scan and the second used the seek right from the beginning. It still had to do a key lookup and a scan because the non-clustered index didn't have all the information it needed, but by that point it had a much smaller set of data to look through so it was faster.

0

Plan Link is not working.

You have provided incomplete info.It is important to know table join column and their data type.

WHAT is aux.SortCode ? WHAT is aux alias ? Where it is in Query ?

There is further scope to rearrange the query.

In this case,

AND (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL)

Sql Optimizer Cardianility Estimate is very poor.

Repeating same condition with OR is bad idea.

You can put many table record in #Temp table or CTE

Then finally join manymany with #Temp table.

DECLARE @Dt Datetime=CURRENT_TIMESTAMP

select many.Col,many.OnlyRequiredColumn
from many into #Temp
WHERE main.Active = -1
  AND main.StatusID = 1
  AND (main.FromDate>=@Dt AND main.UptoDate<=@Dt)
  AND (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL)
ORDER BY aux.SortCode

If #Temp have more than 100/200 record then you can create index on the Column which Join with manymany.

select *
from manymany
join #Temp on manymany.somecolumn=#temp.somecolumn
WHERE manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P1
  AND (manymany.FromDate>=@Dt AND manymany.UptoDate<=@Dt)
--ORDER BY aux.SortCode

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.