February 16, 2004 at 10:15 am
Hi
I am having a problem in the database (SQL Server 2000) A table is growing quite rapidly and it has already reached few millions so giving timeouts on sps.This table is about to grow more.
I was thinking of implementing some thing inteligent dont know what...So that the code accessing this table does not change and I split the table into few tables and create something (don't know what) so that logicall the code sees the same table but physically i have more than one table thus easy to manage
Can soem one help me out on this
Vivek
February 16, 2004 at 10:41 am
Take a look at 'Partitioned Views' in SQL Server Books OnLine.
Basically, you'll horizontally split your big table up into smaller tables, and then build a view that selects all the fields from each table using UNION ALL.
When properly configured, SQL Server will limit the tables it searches for data based on the query SARGs.
The key to making it work is defining a CHECK CONSTRAINT on one of the columns in your primary key that will decide what data goes to each table. (Be sure to always use the 'check existing data on create' option with that check constraint.)
There will be a little extra work to do depending on your table, for example, you cannot use a IDENTITY column with a Partitioned view, and all inserts must reference every column, even if the insert is not putting any data in that column.
If you have an identity column in the original table, you can get around that by using an INSTEAD OF INSERT trigger on the view that determines the next available number for that column.
There are major benefits to this approach, even though you will have to do some additional work and testing to pull it off. The main benefit is that any SELECT against the view that specifies a value for the partition column (the one with the CHECK constraint) will provide the Query Optimizer with enough information to only search the correct table, which can drastically cut down query times and resource contention.
Scan over the BOL articles and try some things out, then please feel free to start asking more questions if you feel this is the right approach for you!
-Eddie
Eddie Wuerch
MCM: SQL
February 16, 2004 at 2:57 pm
While partitioned views are certainly an option, do I understand you correct, that reviewing table design is not an option?
What about having a closer look at the index(es) on that table and monitor if they are properly selected?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 17, 2004 at 8:52 am
Indexes were also my first thought. Check to see that your indexes are built well (logically) and that whatever stored procedures are accessing them are actually hitting them through SQL Profiler. If you do have good indexes but the SP's aren't hitting them, then you need to use optimizer hints to force the correct index to be used.
I don't think partitioning your table is a good idea at this point, you might be setting yourself up for more heartaches. If your table is accumulating a lot of "completed" records that won't be updated nor are likely to be queried, I'd consider creating a history table then have your query/reporting system also look at this through union queries. It's easy to write a sql script to run daily that would move all records (example) over 120 days that are completed to history in order to keep your live file more manageable in size.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply