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.

  • 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