2

I have a table like (SQLServer 2008):

CREATE TABLE [dbo].[my_test_table](
    [productId] [int] NOT NULL,
    [purchaseId] [bigint] NOT NULL,
    (some other columns....),
 CONSTRAINT [PK_my_test_table] PRIMARY KEY CLUSTERED 
(
    [productId] ASC,
    [purchaseId] ASC
))

having around 10 milions rows.

I want a query that returns the total number of rows for a product or, if the product is not secified, the total number of rows for all the products. Something like:

declare @productId int

set @productId = 320

select count(*)
from my_test_table t with(nolock)
where productId = @productId
or @productId is null

The problem is that query takes much more time than the equivalent query:

select count(*)
from my_test_table t with(nolock)
where productId = 320
or 320 is null

How can we explain this behaviour ?

Here are the execution plans: enter image description here

  • It's possible that the real difference lies in the plan going parallel - this may be one of those times where that's actually a bad idea. Try adding MAXDOP = 1 as a query hint, and see if it does better. For that matter, see wha the time and plan is for SELECT COUNT(*) FROM my_test_table; – RDFozz Jun 1 '17 at 16:16
5

Consider this query:

select count(*)
from my_test_table t
where null is null;

Does it make sense for the optimizer to do a seek there? There's nothing to seek against. Trying to force it with a hint throws an error:

select count(*)
from my_test_table t with (forceseek)
where null is null;

Msg 8622, Level 16, State 1, Line 15

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

For the query that you have right now you may want an index seek depending on the parameter value. However, an index seek is not valid for all possible parameter values so your cached plan cannot contain a seek. In addition, when the query optimizer builds the plan it does not check the value of the local variable first.

You indicated that the query is complex so likely the best solution will be to upgrade to at least SQL Server 2008 R2 SP2 so you can take advantage of the Parameter Embedding Optimization (PEO). That combined with an OPTION (RECOMPILE) hint allows the query optimizer to sniff the value of the local variable before executing the plan. The query plan cannot be reused by another query.

Let's see it in action. The actual query plan for the following query shows an index seek:

declare @productId int = 320;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

enter image description here

The actual query plan for the following shows a scan:

declare @productId int = NULL;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

enter image description here

If upgrading isn't an option I suppose you could try this:

select *
from my_test_table t 
where productId = @productId
and @productId is not null

UNION ALL

select *
from my_test_table t
where @productId is null;

That can will do a seek or a scan depending on the value of the local variable but could have unintended side effects.

If that also isn't acceptable I think your only option is to use dynamic SQL which was already covered in another answer.

1

SQL Server tries to produce a re-usable query execution plan that can be run at once when you perform the same query again. So it can't use the fact that @productId IS NULL, because you will run the same query with @productId NOT NULL. I don't know if you can directly prevent that from being considered, although you can control plan caching and re-use.

I address situations like this by using "dynamic SQL" in which the SQL code is constructed in an nvarchar(max) variable and then executed from that, with variables as parameters if you use sp_executesql. This method has limitations and hazards ("SQL injection") but for this case, you could make the @productId condition appear in the query only when @productId isn't NULL. So in fact two different queries for the two cases.

SET @workstring = N'SET @count = (SELECT COUNT(*) FROM my_test_table ' +
    CASE WHEN ( @productId IS NOT NULL ) THEN N'WHERE ( productId = @testThis )' 
         ELSE N'' END
EXEC sp_executesql @workstring, N'@count int OUTPUT, @testThis int',
    @count OUTPUT, @productId

For longer complex statements, I declare one long varchar(max) of beautifully laid out SQL containing token markers such as in this case @{where_condition}, then I use REPLACE() to change these to what the code actually needs to be, in this case the result of the CASE statement. I may check before execution that there isn't a left-over '@{' still in the string!

1
select count(*) from my_test_table t with(nolock) where productId = 320
or 320 is null

Since 320 is null always return false ,optimizer is smart enough to remove "or 320 is null"

So above statement is equivalent to

select count(*) from my_test_table t with(nolock) where productId = 320

productid is CI and also productid is selective enough index so optmizer decide to Index Seek.

select count(*) from my_test_table t with(nolock) where productId = @productId
or @productId is null

Suppose @productid=2 (initiallly)

Even then Estimated number of rows & Actual number of rows is always maximum even if you supply value for variable.So optimizer decide to Index Scan, always.

select count(*) from my_test_table t with(nolock) where productId = @productId
    or @productId is null
    OPTION (RECOMPILE);

here plan will change each time.so when @productid is not null then It will be Index Seek else index scan.

But suppose your search frequncy is very high then repeated recompilation will increase overload on server.

In real life if your query is really like above then you can use if else

 if(@productid is not null)
    select count(*) from my_test_table t with(nolock) where productId = @productId

else 
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'my_test_table' AND (index_id < 2)

If your search query is very complex also will be very frequently use then you can go for parameterized dynamic sql using "sp_executesql". I don't think sp_executesql has any limitation like 'Sql injection".Rather to overcome "sql injection hazard in dynamic sql, we should be using sp_executesql.

You should share your complex query in another thread.

  • Thx for your answer. But why the optimizer is not smart enough to remove or @productId is null if @productId is null ? Moreover, my real life query is much more complex and duplicating the code depending on the value of the variable is absolutely not convenient :( – irimias Jun 1 '17 at 12:47
  • If you add OPTION (RECOMPILE) as suggested, it will be 'smart enough', it just takes time to be smart (generate a new plan) and by default we save this time by not regenerating the plan everytime – Nick.McDermaid Jun 2 '17 at 5:09

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.