query evaluation inconsistency (SQL Server 2000 + SP4)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard Beattie - 25 Jul 2008 15:51 GMT

I've got a simple query that i'm using in order to extract data from a
warehouse fact table using local partitioned views (with check constraint on
a column called start_date_id). The condition of the query is constructed
dynamically in order to specify the appropriate date range. The generated
query is:

select * from table_name
where start_date_id between
('2006'+'03'+'15')
and
(substring('03/15/2006 23:59:59', 7, 4) + substring( '03/15/2006
23:59:59', 1, 2)+ substring( '03/15/2006 23:59:59' , 4, 2))

This query is not able to determine correct partitions and queries all of my
partitions. However the following query is able to determine the correct
partitions:

select * from table_name
where start_date_id between
('2006'+'03'+'15')
and ('2006'+'03'+'15')

Evaluating the second part of either of these queries returns the string
'20060315' so I can't understand why these queries behave differently.

Any insight as to why these are evaluating differently would be much
appreciated,

Thanks,

Richard.

Reply to this Message

Roy Harvey (SQL Server MVP) - 25 Jul 2008 16:32 GMT

I see two possible explanations. One is that the compiler is
resolving the simpler expression ahead of time, while the more complex
expression is handled at run time. The other is that there are
@variables used in the actual code that are shows as literals in the
example posted.

I would try writing it a bit differently:

select * from table_name
where start_date_id >= ('2006'+'03'+'15')
and
start_date_id < DATEADD(day,1,'2006'+'03'+'15')

If nothing else it avoids the mess with 23:59:59 for the time, which
is not guaranteed to find all rows for the date.

Roy Harvey
Beacon Falls, CT

>I've got a simple query that i'm using in order to extract data from a
>warehouse fact table using local partitioned views (with check constraint on
[quoted text clipped - 27 lines]
>
>Richard.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 25 Jul 2008 16:56 GMT

> If nothing else it avoids the mess with 23:59:59 for the time, which
> is not guaranteed to find all rows for the date.

But his substring just discarded the time. So it effectively became

BETWEEN '20060315' AND '20060315'

... hence my suggestion to switch to an equality check. You are right, if
start_date_id includes time information, it should certainly be

WHERE start_date_id >= '20060315'
AND start_date_id < '20060316'

A

Reply to this Message

Roy Harvey (SQL Server MVP) - 25 Jul 2008 18:11 GMT

>But his substring just discarded the time.

Right, I didn't catch that all the substringing didn't bring along the
time component.

Roy Harvey
Beacon Falls, CT

Reply to this Message

Richard Beattie - 25 Jul 2008 17:37 GMT

Thanks for the suggestion Roy. I think you are right in that the compiler is
resolving the simpler expression (with a constant) ahead of time but as soon
as a 'substr' appears, then it is run within the query.

I tried your suggestion of changing to use > and < instead of between but
again it seems the mere use of a "substr" to get from my date string into
something meaningful defers evaluation.

However, out of interest I tried flipping the original query so the
expression involving the substr comes first (below) and this does allow the
partition constraints to be used:

select * from table_name
where start_date_id between
(substring('03/15/2006 23:59:59', 7, 4) + substring( '03/15/2006
23:59:59', 1, 2)+ substring( '03/15/2006 23:59:59' , 4, 2))
and
('2006'+'03'+'15')

So maybe it's not as simple as the presence of "substr", it seems to be the
position too. So that breaks our previous explanation of having to wait til
run time to evaluate the substr! Very strange.

Richard.

> I see two possible explanations. One is that the compiler is
> resolving the simpler expression ahead of time, while the more complex
[quoted text clipped - 46 lines]
> >
> >Richard.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 25 Jul 2008 16:32 GMT

Why are you using BETWEEN at all? And why are you using regionalized,
ambiguous strings? If start_date_id stores date information but no time,
then you should just be saying:

WHERE start_date_id = '20060315';

> I've got a simple query that i'm using in order to extract data from a
> warehouse fact table using local partitioned views (with check constraint
[quoted text clipped - 29 lines]
>
> Richard.

Reply to this Message

Richard Beattie - 25 Jul 2008 17:38 GMT

Thanks for the reply Aaron. I'm using a "between" as the two dates are
actually intended to represent a start and end date. It was just for
illustrating the strange behaviour here that I used the same date for both.

Richard.

> Why are you using BETWEEN at all? And why are you using regionalized,
> ambiguous strings? If start_date_id stores date information but no time,
[quoted text clipped - 35 lines]
> >
> > Richard.

Reply to this Message

JXStern - 25 Jul 2008 18:10 GMT

>Thanks for the reply Aaron. I'm using a "between" as the two dates are
>actually intended to represent a start and end date. It was just for
>illustrating the strange behaviour here that I used the same date for both.

I presume you are also not running substr on constant strings.

It will help if you indicate exactly which clauses are using
parameters or other variables.

Josh

>Richard.
>
[quoted text clipped - 37 lines]
>> >
>> > Richard.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 25 Jul 2008 19:27 GMT

But if you're passing the dates to this query, why do all of this
substringing etc. in the where clause? Why not do this before you get
there?

On 7/25/08 12:38 PM, in article
67711A35-0279-48B0-B129-649BC05EC2C5@microsoft.com, "Richard Beattie"
<RichardBeattie@discussions.microsoft.com> wrote:

> Thanks for the reply Aaron. I'm using a "between" as the two dates are
> actually intended to represent a start and end date. It was just for
[quoted text clipped - 41 lines]
>>>
>>> Richard.

Reply to this Message

Gert-Jan Strik - 27 Jul 2008 11:21 GMT

Richard,

I don't think you have anything to worry about.

Partitioned Views work on two levels: the optimizer can use them, and
the storage engine will use them.

What you are seeing in your example with "between ('2006'+'03'+'15') and
('2006'+'03'+'15')" is that the optimizer has evaluated this expression
at compile time, and has chosen to only put an access path to the table
who's partition contains this date.

In the other example, the query plan will have access paths to all
partitions, since the expression ("substring(...) + ..." is not
evaluated until runtime.

However, if you run the query (and SET STATISTICS IO ON before running
the query), you will see that only the relevant partition is accessed by
the engine. The IO statitics will show 0 scans, 0 logical reads and 0
physical reads for all other partitions. In other words, the other
partitions were eliminated at runtime.

Signature

Gert-Jan
SQL Server MVP

> I've got a simple query that i'm using in order to extract data from a
> warehouse fact table using local partitioned views (with check constraint on
[quoted text clipped - 27 lines]
>
> Richard.

Reply to this Message

Richard Beattie - 28 Jul 2008 10:24 GMT

Hi Gert-Jan,

You are absolutely correct! Despite the execution plan showing multiple
partitions, the IO statistics confirm there are 0 scans/logical
reads/physical reads on all partitions but one. Perfect.

Thanks very much for this piece of information.

Thanks to Aaron, Roy and JXStern for their help too.

Richard.

> Richard,
>
[quoted text clipped - 49 lines]
> >
> > Richard.

Reply to this Message