Some inteligent way of implementing?????
-
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
-
You can partition the table and then create view that presents it to the application as one object. You can even name it the same as the 'old' table and the application would not know that it is not a table.
-
What kind of a server contains this table? Could indexing or otherwise optimizing the table be a better solution than partitioning it? I don't know what the current index configuration is, but it might just be lacking some good indexing - just a thought.
Ryan
-
The only issue with implementing using a partitioned view is that you cannot use identity in any of your base tables, so if you have you WILL NOT see a performance improvement.
I have seen some of the same problems and a lot can be done by indexing correctly, what you are joining on, and how you are accessing the data. Some examples of SP's using the tael might be helpful.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I would start at the very basic with reviewing the table design. You can't get good performace on a poorly designed table. Next would be the indexes (especially the clustered index) on that table. Having that done next thing would be to review the code accessing the data. After all 'a few million' rows are not too much for a highly optimized RDBMS. If performance still hasn't improved you might want to consider sclaing out as already been mentioned.
Just my $0.02 cents!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I forgot, when scaling out I would start with an own filegroup on a separate disk for that table.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply