November 10, 2009 at 11:18 am
this is basically my scenario. This is the kind of queries I use in mostly of my Reports. I'm planning to get a denormalize tables and run a daily process (SSIS) to populate the denormalize tables to performance the Report.
You will see the CB table (this is the new partitioned table) and its join. Since this table is big. I Thought that the partition will help in this kind of queries.
What do you think?
Insert #BTB
selectDistinct -- (this runs in 0 seconds)
cba.Ad
, cb.RangeID
, cba.Date
, cb.Name
fromdbo.Ss
joindbo.CBAcbawith(nolock)on s.Ad = cba.Ad
and cba.Status = 1
joindbo.CBcbwith(nolock) on cba.cbid = cb.cbid
Join#AdVersion a on cba.Ad = a.Ad
WhereClientID = 41
AndDate between '2009-09-28 00:00:00.000' and '2009-11-03 23:59:59.997'
andRequestDate between '2009-09-28 00:00:00.000' and '2009-11-03 23:59:59.997'
insert #Impression
select b.RangeID
, b.Ad
, stbs.BoxID
, c.[P]
, stbs.STID
, b.Date
from#BTb b with(nolock)
join CB c with(nolock) on b.RangeID = c.RangeID -- This is the partitioned table
and b.Ad = c.Ad and c.Problem = 0 and c.Feeder = 0
join #AdProfile a with(nolock) on c.Ad = a.Ad
and c.[P] = a.[P]
join dbo.SV stbsv with(nolock) on stbsv.Ad = b.Ad
and stbsv.StartDate <= b.Date
and stbsv.EndDate >= b.Date
join dbo.STBS stbs with(nolock) on c.BoxID = stbs.BoxID
and stbs.[P] = c.[P]
and stbs.VersionID = stbsv.VersionID
November 10, 2009 at 12:55 pm
MTY-1082557 (11/10/2009)[hr
I want to know if I change the partition by date I will be able to see a performance.
It's hard to say without trying it. I have enough experience in working with partitions to know the concepts and how they can apply as a solution to specific problems. But just like any other sort of performance tuning, it becomes a game of try, fail, adjust.
In the other thread that we worked on together, you had a specific problem that partitioning was a possible solution for. Here, you seem to be just trying to improve performance of a query? Partitioning has enough setup and maintenance involved in it that I wouldn't necessarily think of it as a one-size-fits-all solution.
What specific problems are you having with your reports that you need to fix (aka, what's going on that you are considering partitioning for your reporting tables) ?
November 10, 2009 at 1:23 pm
This table has around 200millions of records and it has to be part of the Archive Data as well. Partition by Date is the best partition for this table as the first one we worked on before. The reason I decided to do partition by RangeID it’s because this table is used for all my reports (query sent in earlier post) and I thought it will be faster to read a specific Partition. But, it just didn’t work. Since, the table is part of the archive process, then I have to consider the Partition by Date.
November 10, 2009 at 1:32 pm
In the other tread, I posted an article by Kimberly Tripp on partitioning. Re-read the section on partitioning indexes.
You may want to consider partitioning your table (clustered index) by date and then adding that date column into an index that includes your range ID and whatever other columns you use for all of your reports. This would align your range indexes with the data partitions (physical layout of the data on disk) and may help with your reporting performance. Again, I say may, cuz I don't know for sure...you'll have to try it out.
For the record, any large tables that you partition for the purpose of archiving or date range searching should be partitioned by date.
November 10, 2009 at 1:49 pm
Thank you. Actually, I applied that to my other table. I partitioned my indexes where the date is used.
Im restructuring my second table. let you know how it goes.
November 10, 2009 at 2:12 pm
With partitioning the first question one should pose is:
What kind of horizontal split of data would support the goal I aim for.
Since your main query uses a date ranges those columns need to be investigated for cardinality etc, to see if a horizontal split would make sense.
Seems to be an obvious choice to me.
Another goal of partitioning, is to take advantage of IO bandwith by splitting to files on separate spindles ! Putting al your partitions in the same physical file, IMO will augment the chances of suffering OS-file level fragmentation as well as db-file internal fragmentation because chunks of paritions will be mixed around in "clouds" of data.
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
November 11, 2009 at 10:09 am
Ok. I have my table partitioned by date. An index was patitioned as well.
I actually, partitioned 1 more tables that is joined to this table (Parent-Child relationship).
The Report could improve in 7 seconds. the Execution Plan shows the partitions and the clustered indexes that I updated as well.
Im pretty happy with the results.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply