September 8, 2010 at 12:05 pm
Hi all,
I have a problem regarding the amount of data contained within a table.
This table is basically a link table between two tables. We have a Contact table, and a Campaign table. The problem table is the CampaignContact table, which stores a record for each Contact that is a member of a given Campaign.
The table has grown to over 200,000,000 records in size. This is making it very slow to access records in the table and add new records.
What I would like to do is to move these records to an archive table, since most of the time older records are not accessed.
However, I would like this transition to be seamless ideally. In other words, what I would like to be able to do is something like,
"If the Campaign that is being accessed is older than 6 months, then look in CampaignContactArchive, otherwise look in CampaignContact".
Is it possible to do this without re-writing all queries that are accessing that table?
September 8, 2010 at 2:00 pm
classic answer : It depends.
In some cases partitioning may be a great solution (ent edtn only)
The problem will always be : how does sqlserver detect "date range" to filter its partitions to search/scan.
Don't just implement partitioning.
Test it, play with it so you know the pros and the cons so you don't get stuck with bad partition design choices !
Books online has great info .
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
September 8, 2010 at 2:03 pm
If I partition the table, then does the database intuitively know how to access the right partition, without requiring it to do heavy database accesses?
IE, if I were to partition on date, and query the database with a date range from one partition, does it know to look at only records from that partition?
September 9, 2010 at 12:26 am
If you design it well, it may.
That's why you should especially take care of your indexes to be alligned.
This is one of the reasons why you must first read about it and play around with it on a test box.
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
September 9, 2010 at 8:24 am
Well, unfortunately Partitioned Tables are only supported by SQL Server 2005 Enterprise; we have Standard.
Would Partitioned Views work well for this?
September 9, 2010 at 10:30 am
I'll have to hand this over to the other SSC members because I have no experience with partitioned views.
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
September 9, 2010 at 10:50 am
Partitioned views can work. There is work done in the optimizer so that only the partitions needed are accessed. The keys to making this work, as always, are good I/O and network throughput to the archive database/table.
If it's in the same database, you'd want it in a separate filegroup, on separate disks.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 9, 2010 at 1:59 pm
How is the existing table clustered?
With proper clustered indexes, you should be able to access only current records for current requests even without using partitioned views, although such views would certainly be acceptable too.
Scott Pletcher, SQL Server MVP 2008-2010
September 10, 2010 at 11:43 am
Have you done an index tuning session to find out if the tables of concern are optimally indexed? Have you analyzed your queries to ensure they are efficient? 200M records isn't that big in today's SQL Server environment. Also, have you done blocking analysis? What about file IO stall and wait stats analysis?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 10, 2010 at 1:03 pm
With proper indexing and defragmentation, 200 mil rows shouldn't be an issue. I've had tables with 2 billion+ rows that were still usable.
Also, inserting data shouldn't take any longer than it used to. If it does, the table is most likely heavily fragmented and the inserts are taking a long time because of page reordering/etc on the clustered index.
edit: Just to add a quick answer to the other question...without partitioning, there is no easy way to redirect a query to a different table without using a SP.
September 13, 2010 at 3:04 am
Hello,
what indexes your table has?. I suppose you access your data in two ways:
- Asking for contacts in a given campaign.
- Asking for campaigns for a given contact.
To run these queries you need two indexes: Contact + Campaign and Campaign + Contact. If not, surely you force SQL Server to do a full scan.
And, when you access these data maybe you do it joining with other tables; maybe you code something like
SELECT * FROM CONTACTS_CAMPAIGNS INNER JOIN CAMPAIGNS WHERE some condition in CampaignDate
In this case maybe the problem is in the access plan.
Regards,
Francesc
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply