Optimizing Clustered Index Scan

I am trying to tune a query that has as its execution output as a Clustered
Index Scan. There is a Clustered Index on field [dt], which is a datetime
field. The query is structured as such:
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( ( @.date IS NULL )
OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
Clustered Index Seek. Is there any way to rework the WHERE clause to where it
would always be a seek?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Why are you setting @.date to null if @.date is < '1/1/1901'?
--
MG
"cbrichards" wrote:
> I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
> would always be a seek?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||Try,
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM
Table1 AS t1
WHERE
t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
'9999-12-30T23:59:59')
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"cbrichards" wrote:
> I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
> would always be a seek?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||"cbrichards" <u3288@.uwe> wrote in message news:5f760d81fa01c@.uwe...
>I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
> it
> would always be a seek?
>
This is a classic case of pseudo-dynamic SQL. THis is really two different
queries crammed into one. With this query you must do a scan because the
same plan is used whether or not @.date is null. If it is, obviously only a
clustered index scan will do.
IF @.date < '1/1/1901'
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
END
ELSE
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
END
david|||David,
Thanks for your response. I ran this in test and (true enough!) the same plan
is used whether or not @.date is null. At this point, I am trying to
understand why the same plan is used. Could you explain further, please,
specifically why the same plan is used?
David Browne wrote:
>>I am trying to tune a query that has as its execution output as a Clustered
>> Index Scan. There is a Clustered Index on field [dt], which is a datetime
>[quoted text clipped - 14 lines]
>> it
>> would always be a seek?
>This is a classic case of pseudo-dynamic SQL. THis is really two different
>queries crammed into one. With this query you must do a scan because the
>same plan is used whether or not @.date is null. If it is, obviously only a
>clustered index scan will do.
>IF @.date < '1/1/1901'
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
>END
>ELSE
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
>END
>david
--
Message posted via http://www.sqlmonster.com|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5f9b39b4a6681@.uwe...
> David,
> Thanks for your response. I ran this in test and (true enough!) the same
> plan
> is used whether or not @.date is null. At this point, I am trying to
> understand why the same plan is used. Could you explain further, please,
> specifically why the same plan is used?
>
Each query gets only one plan. The values you happen to bind to the
parameters don't change that. So whatever the plan is for the query, it has
to work for all possible values of the parameters.
Read:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
David|||Thanks Alejandro. That appears to resolve the scan regardless of the value of
@.date. Much appreciated. Thanks for the link too.
Alejandro Mesa wrote:
>Try,
>IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
>SELECT
> t1.dt_UID
>FROM
> Table1 AS t1
>WHERE
> t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
>'9999-12-30T23:59:59')
>go
>Dynamic Search Conditions in T-SQL
>http://www.sommarskog.se/dyn-search.html
>AMB
>> I am trying to tune a query that has as its execution output as a Clustered
>> Index Scan. There is a Clustered Index on field [dt], which is a datetime
>[quoted text clipped - 13 lines]
>> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
>> would always be a seek?
--
Message posted via http://www.sqlmonster.com|||David,
I read through the article you provided as a link. I understand better what
you mean by "each query gets only one plan" and "parameters do not change
that."
I am trying to internalize this knowledge and understand it within the
context of my attached statement, which contains the OR condition. As you
originally mentioned, I really have "two different queries crammed into one."
As I originally noted, when I remove the ( @.date IS NULL ) portion from the
WHERE clause I get a Clustered Index Seek. Does this mean, that when the plan
is created, that in order for the optimizer to cache only one plan (in this
case, using my pseudo dynamic SQL), the optimizer, in essence, will go with a
plan that will satisfy both conditions, and the plan that satisfies both
conditions is an Index Scan? Is my understanding correct?
David Browne wrote:
>> David,
>> Thanks for your response. I ran this in test and (true enough!) the same
>> plan
>> is used whether or not @.date is null. At this point, I am trying to
>> understand why the same plan is used. Could you explain further, please,
>> specifically why the same plan is used?
>Each query gets only one plan. The values you happen to bind to the
>parameters don't change that. So whatever the plan is for the query, it has
>to work for all possible values of the parameters.
>Read:
>Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
>http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>David
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5f9e9495ccc86@.uwe...
> David,
> I read through the article you provided as a link. I understand better
> what
> you mean by "each query gets only one plan" and "parameters do not change
> that."
> I am trying to internalize this knowledge and understand it within the
> context of my attached statement, which contains the OR condition. As you
> originally mentioned, I really have "two different queries crammed into
> one."
> As I originally noted, when I remove the ( @.date IS NULL ) portion from
> the
> WHERE clause I get a Clustered Index Seek. Does this mean, that when the
> plan
> is created, that in order for the optimizer to cache only one plan (in
> this
> case, using my pseudo dynamic SQL), the optimizer, in essence, will go
> with a
> plan that will satisfy both conditions, and the plan that satisfies both
> conditions is an Index Scan? Is my understanding correct?
>
Yes. That is it, exactly.
David|||you would have a LOT better luck if you added anohter variable, and did
the dateadd before the select statement.
The way you wrote it, the system has to calculate the dateadd for each
and every row, and it might not be obvious to the engine how simple
your querty could be.
set @.date2 =DateAdd(d, 1, @.date)
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( t1.dt BETWEEN @.date AND @.date2)
OR ( @.date IS NULL )
sounds weird, but sometimes moving the null part around encourages or
discourages the use of hte index. Depends on how that particular engine
parses - you want it to parse and sue the index first.
Worst case you can use a hint to force the engine to use the index.