August 20, 2008 at 9:50 am
Thanks for all your help.
Using Dynamic SQL works fine but a pain to recode.
August 20, 2008 at 2:35 pm
I jumped the gun a bit.
The dynamic sql works fine when I'm only accessing 1 partition.
when expand the date range to include multiple partitions, it does a full scan again.
partioned by "YearMonth" computed column: (YYYMM)
Clustered index on YearMonth,Time
An idea as to why when accessing multiple partitions, it does a full scan again?
Thanks
August 20, 2008 at 2:50 pm
1) is there an implicit conversion happening in the year month value?
2) Can you print out the dynamic sql you are executing and provide it to us? Also, take that printout and run it manually in SSMS and see what the query plan is.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2008 at 6:32 am
Here's the basis of the SP. Partioned on YearMonth (YYYYMM). Clustered index on (YearMonth,Time)
create stored proc test @startdate datetime, @enddate datetime
as
declare @start varchar(20), @end varchar(20)
set @start = convert(varchar(20),@startdate,120)
set @end = convert(varchar(20),@enddate,120)
declare @fnstart int, @fnend int,@sqltxt nvarchar(max)
--this function convert to format YYYMM
set @fnstart= dbo.FN_YearMonth(@start)
set @fnend= dbo.FN_YearMonth(@end)
set @sqltxt = 'insert into #download
select userid, bookid, count(*), type
from bookaccess b with(nolock)
where
yearmonth between ' + cast(@fnstart as varchar) + ' and ' + cast(@fnend as varchar) + '
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
'
exec sp_executesql @sqltxt
the sql prints out to this...
insert into #download
select userid, bookid, count(*), type
from bookaccess b with(nolock)
where
yearmonth between 200805 and 200806
and time Between '2008-05-01 00:00:00' and '2008-06-30 00:00:00' and b.userid in (select userid from #uu)
and b.type in (2,5,7,9,21)
group by userid, bookid, type
the execution plan when run in ssms is...
|--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,[Expr1017],0)))
|--Stream Aggregate(GROUP BY:(.[UserID], .[BookID], .[Type]) DEFINE:([Expr1017]=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]>=(200805) AND [B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth] ='2008-05-01 00:00:00.000' AND [B24DW].[dbo].[BookAccess].[Time] as .[Time]<='2008-06-30 00:00:00.000' 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:([PtnIds1016]) PARTITION ID:([PtnIds1016]))
| |--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_Time_IDX] AS ))
|--Table Scan(OBJECT:([tempdb].[dbo].[#uu]))
As you can see, it does a scan of all partitions.
When I change the dates to only be one month, which will access only one partition, the plan looks like this...
|--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,[Expr1018],0)))
|--Stream Aggregate(GROUP BY:(.[UserID], .[BookID], .[Type]) DEFINE:([Expr1018]=Count(*)))
|--Parallelism(Gather Streams, ORDER BY:(.[UserID] ASC, .[BookID] ASC, .[Type] ASC))
|--Nested Loops(Left Semi Join, WHERE:([B24DW].[dbo].[BookAccess].[UserID] as .[UserID]=[#uu].[userid]))
|--Sort(ORDER BY:(.[UserID] ASC, .[BookID] ASC, .[Type] ASC))
| |--Filter(WHERE:([B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth]>=(200805) AND [B24DW].[dbo].[BookAccess].[YearMonth] as .[YearMonth] ='2008-05-01 00:00:00.000' AND [B24DW].[dbo].[BookAccess].[Time] as .[Time]<='2008-05-31 00:00:00.000' 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)))
| |--Clustered Index Scan(OBJECT:([B24DW].[dbo].[BookAccess].[BookAccess_Time_IDX] AS ))
|--Table Scan(OBJECT:([tempdb].[dbo].[#uu]))
August 21, 2008 at 7:29 am
1) You have not been upfront with us. Your original post did not have nearly the stuff in it this query does:
insert into #download
select userid, bookid, count(*), type
from bookaccess b with(nolock)
where
yearmonth between 200805 and 200806
and time Between '2008-05-01 00:00:00' and '2008-06-30 00:00:00' and b.userid in (select userid from #uu)
and b.type in (2,5,7,9,21)
group by userid, bookid, type
You have TWO IN clauses (both of which can lead to bad performance) and an aggregate/group by. Those can be addressed after we get the partition elimination working. 🙂
2) What is your exact table definition - especially the partitioning function stuff? I just noted something that says it is a computed value instead of explicit? That could well be the problem here. AFAIK, partition functions really should be very simple constructs, without any UDFs, system functions, etc. Equlities, betweens, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2008 at 7:57 am
My appologies...
Here's the table structure and current indexes.
CREATE TABLE [dbo].[BookAccess](
[BookAccessID] [uniqueidentifier] NOT NULL,
[SessionID] [uniqueidentifier] NOT NULL,
[UserID] [uniqueidentifier] NOT NULL,
[BookID] [int] NOT NULL,
[ChunkID] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[Type] [int] NOT NULL,
[BytesAccessed] [int] NOT NULL,
[ByPaidUser] [tinyint] NOT NULL,
[ApplicationID] [uniqueidentifier] NULL,
[CollectionStr] [varchar](255) NULL,
[GreekingLevel] [int] NOT NULL,
[ServerID] [tinyint] NULL,
[AccessTime] [datetime] NULL,
[YearMonth] AS (CONVERT([int],CONVERT([char](4),datepart(year,[time]),(0))+case when datepart(month,[time])<(10) then '0'+CONVERT([char](1),datepart(month,[time]),(0)) else CONVERT([char](2),datepart(month,[time]),(0)) end,(0))) PERSISTED
)
CREATE CLUSTERED INDEX [BookAccess_YearMonth_IDX] ON [dbo].[BookAccess]
(
[YearMonth] ASC,
[Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PS_YearMonth_BookAccess]([YearMonth])
GO
CREATE NONCLUSTERED INDEX [UserIDTime_NDX] ON [dbo].[BookAccess]
(
[UserID] ASC,
[Time] ASC
)
INCLUDE ( [BookID],
[Type],
[YearMonth]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [BookDB_NDX]
--Partition Function
CREATE PARTITION FUNCTION [PF_YearMonth](int) AS RANGE LEFT FOR VALUES (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)
--Partition Scheme
CREATE PARTITION SCHEME [PS_YearMonth_BookAccess] AS PARTITION [PF_YearMonth] TO ([BookAccess199904], [BookAccess199905], [BookAccess199906], [BookAccess199907], [BookAccess199908], [BookAccess199909], [BookAccess199910], [BookAccess199911], [BookAccess199912], [BookAccess200001], [BookAccess200002], [BookAccess200003], [BookAccess200004], [BookAccess200005], [BookAccess200006], [BookAccess200007], [BookAccess200008], [BookAccess200009], [BookAccess200010], [BookAccess200011], [BookAccess200012], [BookAccess200101], [BookAccess200102], [BookAccess200103], [BookAccess200104], [BookAccess200105], [BookAccess200106], [BookAccess200107], [BookAccess200108], [BookAccess200109], [BookAccess200110], [BookAccess200111], [BookAccess200112], [BookAccess200201], [BookAccess200202], [BookAccess200203], [BookAccess200204], [BookAccess200205], [BookAccess200206], [BookAccess200207], [BookAccess200208], [BookAccess200209], [BookAccess200210], [BookAccess200211], [BookAccess200212], [BookAccess200301], [BookAccess200302], [BookAccess200303], [BookAccess200304], [BookAccess200305], [BookAccess200306], [BookAccess200307], [BookAccess200308], [BookAccess200309], [BookAccess200310], [BookAccess200311], [BookAccess200312], [BookAccess200401], [BookAccess200402], [BookAccess200403], [BookAccess200404], [BookAccess200405], [BookAccess200406], [BookAccess200407], [BookAccess200408], [BookAccess200409], [BookAccess200410], [BookAccess200411], [BookAccess200412], [BookAccess200501], [BookAccess200502], [BookAccess200503], [BookAccess200504], [BookAccess200505], [BookAccess200506], [BookAccess200507], [BookAccess200508], [BookAccess200509], [BookAccess200510], [BookAccess200511], [BookAccess200512], [BookAccess200601], [BookAccess200602], [BookAccess200603], [BookAccess200604], [BookAccess200605], [BookAccess200606], [BookAccess200607], [BookAccess200608], [BookAccess200609], [BookAccess200610], [BookAccess200611], [BookAccess200612], [BookAccess200701], [BookAccess200702], [BookAccess200703], [BookAccess200704], [BookAccess200705], [BookAccess200706], [BookAccess200707], [BookAccess200708], [BookAccess200709], [BookAccess200710], [BookAccess200711], [BookAccess200712], [BookAccess200801], [BookAccess200802], [BookAccess200803], [BookAccess200804], [BookAccess200805], [BookAccess200806], [BookAccess200807], [BookAccess200808], [BookAccess200809], [BookAccess200810], [BookAccess200811], [BookAccess200812], [BookAccess200901])
FYI, there's over 300M records in the table
Thanks
August 21, 2008 at 8:18 am
just my 2 ct.
I've read somewhere (cannot recall where, but I guess it was in one of Jeff Modens replies) that sqlserver may also experience problems when using between with datatime columms and that it would actualy make a difference by using col >= date1 and col <= date2
btw did you check with Cumulative Update 9 for SP2 ?
documented in KB #956006.
You can read about / obtain the cumulative update at the following KB:
http://support.microsoft.com/kb/953752
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply