August 18, 2008 at 8:14 am
I have a large table that is partitioned by yearmonth. When I query on this table by the partition using between, it performs super slow (if at all). If I hard code a value with =, then it is super fast.
Example:
declare @start datetime, @end datetime
set @start = '01/01/2007'
set @end = '08/14/2008'
select *
from table2
where yearmonth between dbo.fn_yearmonth(@start) and dbo.fn_yearmonth(@end)
and time between @start and @end
dbo.fn_yearmonth just parses out the date into yearmonth
if I were to change the query to this...
select *
from table2
where yearmonth = 200701
and time between @start and @end
it works fine. We are running version 9.0.3.235
I read about a bug and hotfix with this
http://support.microsoft.com/kb/923849
I do not see this hotfix in the latest SP and it won't let me install the hotfix due to being on a later SP release.
Is anyone having the same problem? Is this still an unsolved Microsoft bug?
Thanks,
Paul
August 18, 2008 at 8:45 am
Service Packs are cumulative and include any previous hot fixes.
Post the DDL and the execution plans. I think the issue is probably in the function being called and possibly in return types. It is pretty likely that your query with the BETWEEN is causing a table scan rather than being able to effectively use an index. If you end up with a scan on a partitioned table, it scans all of the partitions.
August 18, 2008 at 11:49 am
When this runs in management studio as a query, it runs fine. As soon as I create it as a stored proc, is when it hangs.
The plan for the stored proc is doing a table scan
the plan when run in MS is doing an index seek.
August 18, 2008 at 4:34 pm
As indicated before, post the entire execution plan for each.
Also, just as something to check, make sure you're not getting nailed by the parameter sniffing issue. If your between values are being passed in by parameters, try assigning the parameters to internal variables, and using those variables in the query.
Basically the issue is, the query optimizer isn't able to estimate cardinality effectively so its going back to "guess" mode. This is why its doing a table scan.
Make sure you have updated statistics on the table as well, since you're indexing on a clustered ascending key. New values aren't in the heuristics and more than likely the threshold for autoupdate hasn't been reached (500 changes + 20% of total rowcount). Just run a simple UPDATE STATISTICS on the table usually suffices, and it won't take too long to run with the default scan density.
August 18, 2008 at 4:45 pm
You might be running up against parameter sniffing, but I think you can get around that by changing you query to:
declare @start datetime, @end datetime
set @start = '01/01/2007'
set @end = '08/14/2008'
/* the above is defined in the procedure */
declare @start_year_month int, @end_year_month int;
set @start_year_month = dbo.fn_yearmonth(@start);
set @end_year_month = dbo.fn_yearmonth(@end);
select *
from table2
where yearmonth between @start_year_month and @end_year_month
and time between @start and @end
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 19, 2008 at 6:29 am
Here's the execution plan when I run the SP as suggested with assigning local variables for the between.
declare @fnstart int, @fnend int;
set @fnstart = dbo.fn_yearmonth(@start);
set @fnstart = dbo.fn_yearmonth(@end);
select *
from table2
where yearmonth between @start_year_month and @end_year_month
and time between @start and @end
--------------------------------------------------------------------------
|--Table Insert(OBJECT:([tempdb].[dbo].[#download]), SET:([#download].[userid] = [B24DW].[dbo].[BookAccess].[UserID] as .[UserID],[#download].[bookid] = [B24DW].[dbo].[BookAccess].[BookID] as .[BookID],[#download].[hitcnt] = [Expr1011],[#download].[type] = [B24DW].[dbo].[BookAccess].[Type] as .[Type]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1016],0)))
|--Stream Aggregate(GROUP BY:(.[UserID], .[BookID], .[Type]) DEFINE:([Expr1016]=Count(*)))
|--Sort(ORDER BY:(.[UserID] ASC, .[BookID] ASC, .[Type] ASC))
|--Parallelism(Gather Streams)
|--Nested Loops(Left Semi Join, WHERE:([B24DW].[dbo].[BookAccess].[UserID] as .[UserID]=[#uu].[userid]))
|--Filter(WHERE:([B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth]>=[@fnstart] AND [B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth] =[@start] AND [B24DW].[dbo].[BookAccess].[Time] as .[Time]<=[@end] AND ([B24DW].[dbo].[BookAccess].[Type] as .[Type]=(2) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(5) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(7) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(9) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(21))))
| |--Compute Scalar(DEFINE:(.[YearMonth]=[B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth]))
| |--Compute Scalar(DEFINE:(.[YearMonth]=CONVERT(int,CONVERT(char(4),datepart(year,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0)+CASE WHEN datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time])<(10) THEN '0'+CONVERT(char(1),datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0) ELSE CONVERT(char(2),datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0) END,0)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1013]) PARTITION ID:([PtnIds1013]))
| |--Parallelism(Distribute Streams, Demand Partitioning)
| | |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14)),((15)),((16)),((17)),((18)),((19)),((20)),((21)),((22)),((23)),((24)),((25)),((26)),((27)),((28)),((29)),((30)),((31)),((32)),((33)),((34)),((35)),((36)),((37)),((38)),((39)),((40)),((41)),((42)),((43)),((44)),((45)),((46)),((47)),((48)),((49)),((50)),((51)),((52)),((53)),((54)),((55)),((56)),((57)),((58)),((59)),((60)),((61)),((62)),((63)),((64)),((65)),((66)),((67)),((68)),((69)),((70)),((71)),((72)),((73)),((74)),((75)),((76)),((77)),((78)),((79)),((80)),((81)),((82)),((83)),((84)),((85)),((86)),((87)),((88)),((89)),((90)),((91)),((92)),((93)),((94)),((95)),((96)),((97)),((98)),((99)),((100)),((101)),((102)),((103)),((104)),((105)),((106)),((107)),((108)),((109)),((110)),((111)),((112)),((113)),((114)),((115)),((116)),((117)),((118))))
| |--Clustered Index Scan(OBJECT:([B24DW].[dbo].[BookAccess].[BookAccess_YearMonth_IDX] AS ))
|--Table Scan(OBJECT:([tempdb].[dbo].[#uu]))
I created a separate test SP with just the piece of code is is giving me a problem. The execution plan is below. This runs fine with using the parameter variables in the between and not local.
|--Table Insert(OBJECT:([tempdb].[dbo].[#download]), SET:([#download].[userid] = [B24DW].[dbo].[BookAccess].[UserID] as .[UserID],[#download].[bookid] = [B24DW].[dbo].[BookAccess].[BookID] as .[BookID],[#download].[hitcnt] = [Expr1011],[#download].[type] = [B24DW].[dbo].[BookAccess].[Type] as .[Type]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1021],0)))
|--Stream Aggregate(GROUP BY:(.[UserID], .[BookID], .[Type]) DEFINE:([Expr1021]=Count(*)))
|--Sort(ORDER BY:(.[UserID] ASC, .[BookID] ASC, .[Type] ASC))
|--Nested Loops(Left Semi Join, WHERE:([B24DW].[dbo].[BookAccess].[UserID] as .[UserID]=[#uu].[userid]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1017]) PARTITION ID:([PtnIds1017]))
| |--Filter(WHERE:([PtnIds1017]>=RangePartitionNew([B24DW].[dbo].[FN_YearMonth](CONVERT_IMPLICIT(datetime,[@StartDate],0)),(0),(199904),(199905),(199906),(199907),(199908),(199909),(199910),(199911),(199912),(200001),(200002),(200003),(200004),(200005),(200006),(200007),(200008),(200009),(200010),(200011),(200012),(200101),(200102),(200103),(200104),(200105),(200106),(200107),(200108),(200109),(200110),(200111),(200112),(200201),(200202),(200203),(200204),(200205),(200206),(200207),(200208),(200209),(200210),(200211),(200212),(200301),(200302),(200303),(200304),(200305),(200306),(200307),(200308),(200309),(200310),(200311),(200312),(200401),(200402),(200403),(200404),(200405),(200406),(200407),(200408),(200409),(200410),(200411),(200412),(200501),(200502),(200503),(200504),(200505),(200506),(200507),(200508),(200509),(200510),(200511),(200512),(200601),(200602),(200603),(200604),(200605),(200606),(200607),(200608),(200609),(200610),(200611),(200612),(200701),(200702),(200703),(200704),(200705),(200706),(200707),(200708),(200709),(200710),(200711),(200712),(200801),(200802),(200803),(200804),(200805),(200806),(200807),(200808),(200809),(200810),(200811),(200812)) AND [PtnIds1017]<=RangePartitionNew([B24DW].[dbo].[FN_YearMonth](CONVERT_IMPLICIT(datetime,[@EndDate],0)),(0),(199904),(199905),(199906),(199907),(199908),(199909),(199910),(199911),(199912),(200001),(200002),(200003),(200004),(200005),(200006),(200007),(200008),(200009),(200010),(200011),(200012),(200101),(200102),(200103),(200104),(200105),(200106),(200107),(200108),(200109),(200110),(200111),(200112),(200201),(200202),(200203),(200204),(200205),(200206),(200207),(200208),(200209),(200210),(200211),(200212),(200301),(200302),(200303),(200304),(200305),(200306),(200307),(200308),(200309),(200310),(200311),(200312),(200401),(200402),(200403),(200404),(200405),(200406),(200407),(200408),(200409),(200410),(200411),(200412),(200501),(200502),(200503),(200504),(200505),(200506),(200507),(200508),(200509),(200510),(200511),(200512),(200601),(200602),(200603),(200604),(200605),(200606),(200607),(200608),(200609),(200610),(200611),(200612),(200701),(200702),(200703),(200704),(200705),(200706),(200707),(200708),(200709),(200710),(200711),(200712),(200801),(200802),(200803),(200804),(200805),(200806),(200807),(200808),(200809),(200810),(200811),(200812))))
| | |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14)),((15)),((16)),((17)),((18)),((19)),((20)),((21)),((22)),((23)),((24)),((25)),((26)),((27)),((28)),((29)),((30)),((31)),((32)),((33)),((34)),((35)),((36)),((37)),((38)),((39)),((40)),((41)),((42)),((43)),((44)),((45)),((46)),((47)),((48)),((49)),((50)),((51)),((52)),((53)),((54)),((55)),((56)),((57)),((58)),((59)),((60)),((61)),((62)),((63)),((64)),((65)),((66)),((67)),((68)),((69)),((70)),((71)),((72)),((73)),((74)),((75)),((76)),((77)),((78)),((79)),((80)),((81)),((82)),((83)),((84)),((85)),((86)),((87)),((88)),((89)),((90)),((91)),((92)),((93)),((94)),((95)),((96)),((97)),((98)),((99)),((100)),((101)),((102)),((103)),((104)),((105)),((106)),((107)),((108)),((109)),((110)),((111)),((112)),((113)),((114)),((115)),((116)),((117)),((118))))
| |--Index Seek(OBJECT:([B24DW].[dbo].[BookAccess].[BookAccess_UserIDTime_NDX] AS ), SEEK:((.[YearMonth], .[Time]) >= ([B24DW].[dbo].[FN_YearMonth](CONVERT_IMPLICIT(datetime,[@StartDate],0)), CONVERT_IMPLICIT(datetime,[@StartDate],0)) AND (.[YearMonth], .[Time]) =CONVERT_IMPLICIT(datetime,[@StartDate],0) AND [B24DW].[dbo].[BookAccess].[Time] as .[Time]<=CONVERT_IMPLICIT(datetime,[@EndDate],0) AND ([B24DW].[dbo].[BookAccess].[Type] as .[Type]=(2) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(5) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(7) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(9) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(21))) ORDERED FORWARD PARTITION ID:([PtnIds1017]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#uu]))
I appreciate the help!
August 19, 2008 at 9:36 am
Yep. Parallelism. Looks like SQL's having issues with selectivity and dropping back to "guess mode" and parallelizing the query. Either increase the sp_configure value for 'cost threshold for parallelism' from 5 seconds to something like 20 seconds, or use something like WITH (MAXDOP = 1).
Looking at it, it certainly seems that you may be experiencing the parameter sniffing issue as well. Try converting your datetime sting values in one stored procedure to true datetime datatypes, and call the second "worker" stored procedure, passing the datetime values.
Datetime implicit datatype conversions can really kill you, especially when SQL Server parameterizes the query.
August 19, 2008 at 1:00 pm
Here's the execution plan when I add (maxdop 1)
insert into #download select userid, bookid, count(*), type from bookaccess b with(nolock) where yearmonth between @fnstart and @fnend and time between @start and @end and b.userid in (select userid from #uu) and b.type in (2,5,7,9,21) group by userid, bookid, type option (maxdop 1)
|--Table Insert(OBJECT:([tempdb].[dbo].[#download]), SET:([#download].[userid] = [B24DW].[dbo].[BookAccess].[UserID] as .[UserID],[#download].[bookid] = [B24DW].[dbo].[BookAccess].[BookID] as .[BookID],[#download].[hitcnt] = [Expr1011],[#download].[type] = [B24DW].[dbo].[BookAccess].[Type] as .[Type]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1016],0)))
|--Stream Aggregate(GROUP BY:(.[UserID], .[BookID], .[Type]) DEFINE:([Expr1016]=Count(*)))
|--Sort(ORDER BY:(.[UserID] ASC, .[BookID] ASC, .[Type] ASC))
|--Hash Match(Right Semi Join, HASH:([#uu].[userid])=(.[UserID]), RESIDUAL:([B24DW].[dbo].[BookAccess].[UserID] as .[UserID]=[#uu].[userid]))
|--Index Scan(OBJECT:([tempdb].[dbo].[#uu]))
|--Filter(WHERE:([B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth]>=[@fnstart] AND [B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth] =[@start] AND [B24DW].[dbo].[BookAccess].[Time] as .[Time]<=[@end] AND ([B24DW].[dbo].[BookAccess].[Type] as .[Type]=(2) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(5) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(7) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(9) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(21))))
|--Compute Scalar(DEFINE:(.[YearMonth]=[B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth]))
|--Compute Scalar(DEFINE:(.[YearMonth]=CONVERT(int,CONVERT(char(4),datepart(year,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0)+CASE WHEN datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time])<(10) THEN '0'+CONVERT(char(1),datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0) ELSE CONVERT(char(2),datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0) END,0)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1013]) PARTITION ID:([PtnIds1013]))
|--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14)),((15)),((16)),((17)),((18)),((19)),((20)),((21)),((22)),((23)),((24)),((25)),((26)),((27)),((28)),((29)),((30)),((31)),((32)),((33)),((34)),((35)),((36)),((37)),((38)),((39)),((40)),((41)),((42)),((43)),((44)),((45)),((46)),((47)),((48)),((49)),((50)),((51)),((52)),((53)),((54)),((55)),((56)),((57)),((58)),((59)),((60)),((61)),((62)),((63)),((64)),((65)),((66)),((67)),((68)),((69)),((70)),((71)),((72)),((73)),((74)),((75)),((76)),((77)),((78)),((79)),((80)),((81)),((82)),((83)),((84)),((85)),((86)),((87)),((88)),((89)),((90)),((91)),((92)),((93)),((94)),((95)),((96)),((97)),((98)),((99)),((100)),((101)),((102)),((103)),((104)),((105)),((106)),((107)),((108)),((109)),((110)),((111)),((112)),((113)),((114)),((115)),((116)),((117)),((118))))
|--Clustered Index Scan(OBJECT:([B24DW].[dbo].[BookAccess].[BookAccess_YearMonth_IDX] AS ))
another note:
if I change the "between @fnstart and @fnend"
to "between @fnstart and @fnstart" it works great. The moment I put the other variable in there it crawls. (Even though they are the same value)
This would be what you are referring to as parameter sniffing?
Are there other ways to avoid this?
I also tried to us Option(optimize for (@fnstart=200805),optimize for (@fnend=200805))
Below is the execution plan for this.
insert into #download select userid, bookid, count(*), type from bookaccess b with(nolock) where yearmonth between @fnstart and @fnend and time between @start and @end and b.userid in (select userid from #uu) and b.type in (2,5,7,9,21) group by userid, bookid, type option (optimize for(@fnstart=200805),optimize for (@fnend=200805))
|--Table Insert(OBJECT:([tempdb].[dbo].[#download]), SET:([#download].[userid] = [B24DW].[dbo].[BookAccess].[UserID] as .[UserID],[#download].[bookid] = [B24DW].[dbo].[BookAccess].[BookID] as .[BookID],[#download].[hitcnt] = [Expr1011],[#download].[type] = [B24DW].[dbo].[BookAccess].[Type] as .[Type]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1016],0)))
|--Stream Aggregate(GROUP BY:(.[UserID], .[BookID], .[Type]) DEFINE:([Expr1016]=Count(*)))
|--Sort(ORDER BY:(.[UserID] ASC, .[BookID] ASC, .[Type] ASC))
|--Parallelism(Gather Streams)
|--Hash Match(Right Semi Join, HASH:([#uu].[userid])=(.[UserID]), RESIDUAL:([B24DW].[dbo].[BookAccess].[UserID] as .[UserID]=[#uu].[userid]))
|--Parallelism(Distribute Streams, Broadcast Partitioning)
| |--Index Scan(OBJECT:([tempdb].[dbo].[#uu]))
|--Filter(WHERE:([B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth]>=[@fnstart] AND [B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth] =[@start] AND [B24DW].[dbo].[BookAccess].[Time] as .[Time]<=[@end] AND ([B24DW].[dbo].[BookAccess].[Type] as .[Type]=(2) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(5) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(7) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(9) OR [B24DW].[dbo].[BookAccess].[Type] as .[Type]=(21))))
|--Compute Scalar(DEFINE:(.[YearMonth]=[B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth]))
|--Compute Scalar(DEFINE:(.[YearMonth]=CONVERT(int,CONVERT(char(4),datepart(year,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0)+CASE WHEN datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time])<(10) THEN '0'+CONVERT(char(1),datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0) ELSE CONVERT(char(2),datepart(month,[B24DW].[dbo].[BookAccess].[Time] as .[Time]),0) END,0)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1013]) PARTITION ID:([PtnIds1013]))
|--Parallelism(Distribute Streams, Demand Partitioning)
| |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14)),((15)),((16)),((17)),((18)),((19)),((20)),((21)),((22)),((23)),((24)),((25)),((26)),((27)),((28)),((29)),((30)),((31)),((32)),((33)),((34)),((35)),((36)),((37)),((38)),((39)),((40)),((41)),((42)),((43)),((44)),((45)),((46)),((47)),((48)),((49)),((50)),((51)),((52)),((53)),((54)),((55)),((56)),((57)),((58)),((59)),((60)),((61)),((62)),((63)),((64)),((65)),((66)),((67)),((68)),((69)),((70)),((71)),((72)),((73)),((74)),((75)),((76)),((77)),((78)),((79)),((80)),((81)),((82)),((83)),((84)),((85)),((86)),((87)),((88)),((89)),((90)),((91)),((92)),((93)),((94)),((95)),((96)),((97)),((98)),((99)),((100)),((101)),((102)),((103)),((104)),((105)),((106)),((107)),((108)),((109)),((110)),((111)),((112)),((113)),((114)),((115)),((116)),((117)),((118))))
|--Clustered Index Scan(OBJECT:([B24DW].[dbo].[BookAccess].[BookAccess_YearMonth_IDX] AS ))
Any other thoughts?
Thanks again!
August 19, 2008 at 1:13 pm
Here are a few good web resources on parameter sniffing.
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
http://nflanagan.blogspot.com/2006/07/parameter-sniffing.html
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
http://www.sqlmag.com/Article/ArticleID/94369/sql_server_94369.html
Basically there are a few ways to defeat it. This sounds to me like you're running into this issue.
Is there a way you can zip up and attach the graphical query plans to a post? Usually these provide a lot more detail.
August 19, 2008 at 1:23 pm
If it's parameter sniffing, then how come if I change the condition in the query to this...
"where yearmonth = @fnstart
and time between @start and @end"
this works fine
or
"where yearmonth between @fnstart and @fnstart
and time between @start and @end"
This works fine as well.
August 19, 2008 at 1:26 pm
I missed one piece of this:
declare @start datetime, @end datetime
set @start = '01/01/2007'
set @end = '08/14/2008'
/* the above is defined in the procedure */
declare @start_year_month int, @end_year_month int;
set @start_year_month = dbo.fn_yearmonth(@start);
set @end_year_month = dbo.fn_yearmonth(@end);
select *
from table2
where yearmonth between @start_year_month and @end_year_month
and time between @start and @end
What you really need to do is get the parameters into the right format for each column you are checking. For example, I am assuming that the column 'yearmonth' is an integer column in the format YYYYMM and the 'time' column is an integer in the format of HHMM. If that is true, then you would need the following:
CREATE PROCEDURE dbo.MyProcedure
@start datetime
,@end datetime
AS
declare @start_year_month int
,@end_year_month int
,@start_time int
,@end_time int;
set @start_year_month = dbo.fn_yearmonth(@start);
set @end_year_month = dbo.fn_yearmonth(@end);
set @start_time = replace(convert(char(5), @start, 108), ':', '');
set @end_time = replace(convert(char(5), @end, 108), ':', '');
select * -- note: use actual column list here instead of '*'
from table2
where yearmonth between @start_year_month and @end_year_month
and time between @start_time and @end_time;
The other thing you need to be aware of is how the tables are partitioned. If they are partitioned on the yearmonth column you should be fine. However, if they are partitioned on another column or the partition has additional columns your query will need to access all partitions which will take a lot longer to process.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 19, 2008 at 1:27 pm
It all has to do with the cached query plan and how SQL Server determines whether it can reuse the plan or generate (recompile) a new one. Sometimes SQL Server isn't so "bright" when it comes to figuring this out.
August 19, 2008 at 1:34 pm
Yes, it is partitioned on yearmonth YYYYMM
Time is datetime datatype
August 19, 2008 at 2:14 pm
So, if the time is a datetime - is it the actual datetime? In other words, is the time column something like: 1900-01-01 hh:mm:ss.xxx. Or is it the actual datetime (i.e. 2008-01-01 hh:mm:ss.xxx)?
If it is the actual datetime - then you want to filter based upon the actual input parameters. If it is a datetime that really only has the time portion stored, then you need to convert your input parameters appropriately.
Since your partitions are on the yearmonth column - that would explain why you are getting different performance in your tests. In one test you are only looking at a single partition - the other tests are crossing partitions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2008 at 9:47 am
1) Use dynamic sql to execute the EXACT string that you would type into QA with NO variables. This will ensure proper query plan and partition elimination.
set quoted_identifier off
declare @sql varchar(1000)
set @sql = "select *
from table2
where yearmonth between 200701 and 200808
and time between '01/01/2007' and '08/14/2008'"
exec (@sql)
2) Your original post wasn't fair. Don't compare a query with a very large between clause to a query with an equality. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply