1

I'm filtering a table depending on if a variable is set or not. But the clustered index is not used. Is it possible to get the query to somehow use the index. (I cant dynamically create the query in this case)

DECLARE @UseFilter as bit = 1

select pkEventId from EventEvent
where pkEventID = 57637 or @UseFilter = 0

Execution plan (not using index)
enter image description here

If I skip the Or the index is used.

select pkEventId from EventEvent
where pkEventID = 57637

Execution plan (Using index)
enter image description here

Magnus
  • 45,362
  • 8
  • 80
  • 118
  • you do an `IF @UseFilter = 0` and make 2 different queries – ughai Apr 15 '15 at 11:38
  • @ughai In this case I cant do that either. – Magnus Apr 15 '15 at 11:39
  • the part `or @UseFilter = 0` should be meaningless when @UserFilter is set as 1. what is the execution plan when the @UserFilter is set to 0? Perhaps try `WHERE pkEventID CASE WHEN @UseFilter = 0 THEN 57637 ELSE IS NOT NULL END` (guessing from the name it's not nullable – Zohar Peled Apr 15 '15 at 11:43
  • @ZoharPeled When `@UseFilter = 0` all rows are returned and the execution plan is the same as the one not using the index (Index scan, nonClustered) – Magnus Apr 15 '15 at 11:47
  • Is an index hint an option for you? For dynamic search conditions have a look here: http://www.sommarskog.se/dyn-search-2008.html – deterministicFail Apr 15 '15 at 11:52
  • If `EventEvent` is a table with PK; it should be clustered, As same as mine that I try it with `or @var`, Please add a `CREATE` script of `EventEvent` ;). – shA.t Apr 15 '15 at 14:32
  • @shA.t Yes the pk is using a clustered index as can be seen in image two. – Magnus Apr 15 '15 at 15:02

2 Answers2

1

Optimizing or is hard, as your example shows. One way around this is to use dynamic SQL. You can also try this:

DECLARE @UseFilter as bit = 1

select pkEventId
from EventEvent
where pkEventID = 57637
UNION ALL
select pkEventId
from EventEvent
where pkEventID <> 57637 AND @UseFilter = 0;

It is possible that the second subquery will short-circuit because of the comparison of the constant.

EDIT:

This version may do what you really want:

select pkEventId
from EventEvent
where pkEventID = 57637 AND  @UseFilter = 1
UNION ALL
select pkEventId
from EventEvent
where @UseFilter = 0;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the answer. It seems the query after the union is not using the index. – Magnus Apr 15 '15 at 11:57
  • `pkEventID <> 57637 AND` is wrong, it should select ALL rows if if `@UseFilter = 0` – A ツ Apr 15 '15 at 12:05
  • @Aツ . . . This is a `union all` query. Those are retrieved by the first subquery. Magnus, does the second part slow down the query? The question is whether the engine will short-circuit the table scan because of the constant condition. – Gordon Linoff Apr 15 '15 at 12:14
  • 1
    why would the first part return a single row if `@UseFilter = 0`? – A ツ Apr 15 '15 at 12:15
  • It seems to be the same problem with the updated query. It does an Index scan. – Magnus Apr 15 '15 at 12:31
  • @Magnus . . . Here is a related question (http://stackoverflow.com/questions/22435420/does-sql-server-optimise-constant-expressions). I'm pretty sure that SQL Server will remove constant expressions. However, is it possible that the query is getting compiled and then run with different parameters? If so, you might need to recompile the query each time you run it. – Gordon Linoff Apr 15 '15 at 13:01
0

enter image description here

As you can see my Index scan is Clustered !

Here is my Table_1 Create Script:

CREATE TABLE [dbo].[Table_1](
    [col1] [bigint] IDENTITY(1,1) NOT NULL,
    [col2] [varchar](50) NULL,
    [col3] [nvarchar](max) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
shA.t
  • 16,580
  • 5
  • 54
  • 111