Partitioned view not working correctly with @date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Partitioned view not working correctly with @date?

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-11-28 : 10:29:52

I have a partitioned view that works fine if I use Where Col1 = '2006-11-01 00:00:00.000' but if I use a variable for the date then it does any index seek in all the tables.

Index seek on only one table:

 Select Count(col1) from partitionedview1 where col1 = '2006-11-01 00:00:00.000'

Index seek on all tables:

 Declare @Date datetime
Set @Date = '2006-11-01 00:00:00.000'
Select Count(col1) from partitionedview1 where col1 = @Date

I'm not a master with partitioning so an explaination on why the variable is forcing it to look in all the tables would be great.

Thanks,
Daniel

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-28 : 10:37:24

It would be helpful if you showed the complete DDL for the view and all the underlying tables.

CODO ERGO SUM

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2006-11-28 : 12:31:08

quote:
Originally posted by Michael Valentine Jones

It would be helpful if you showed the complete DDL for the view and all the underlying tables.

Yeah.. I can't do that though. Was hoping you would just say it's due to the variable and to do it "this" way instead of "that" way.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-28 : 13:23:21

quote:
Originally posted by SQLServerDBA_Dan

quote:
Originally posted by Michael Valentine Jones

It would be helpful if you showed the complete DDL for the view and all the underlying tables.

Yeah.. I can't do that though. Was hoping you would just say it's due to the variable and to do it "this" way instead of "that" way.

OK

It's due to the variable. Do it the other way.

CODO ERGO SUM

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 13:38:02

When you use a variable in the WHERE clause the optimizer does not use the value of the variable when it optimizes the query (because the code that sets the value of the variable is in the same batch as the code that the query is in). You can try a couple of things

1. This is a case where dynamic SQL can actually be a good thing, so try this

Declare @Date datetime
Set @Date = '2006-11-01 00:00:00.000'
exec sp_executesql N'Select Count(col1) from partitionedview1 where col1 = @Date', N'@Date datetime', @Date

2. Put it in a stored procedure, because the query is optimized when the sproc is executed using the value of the sproc parameter, this is different to using a variable, so try this

CREATE PROC myProc @Date datetime
AS
Select Count(col1) from partitionedview1 where col1 = @Date

Then when you call the sproc like this

exec myProc '2006-11-01 00:00:00.000'

it should be correctly optimized.

If you have a SQL Server Magazine subscription, see
http://www.sqlmag.com/Articles/ArticleID/42801/pg/1/1.html

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-11-28 : 14:23:59

What is the datatype for Col1? Is it a Datetime?

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources