none
When a table gets too big RRS feed

  • Question

  • I a MSSQL2000 Database I an looking after - I have one table in particular that is causing me problems.

    The table contains 2,159,800 records - and is growing at a rate of circa 10,000 per day.

    As the database has grown - it has become noticeably slower to the point where application performance is dramatically reduced. As an example, a simple SELECT count(*) query can take upto 2 minutes and any query that does not reference the unique primary key can take significantly longer.

    The main issue is that all of the data is required; although as the data becomes older it also becomes less relevant. So it is not possible to simply archive the data.

    With regards to best practices : how should I manage the tables of this system to improve performance?

    Should I create separate tables or a separate 'archive' database and then ensure that all applications aware of the newly create archive?

    Is it a simple case of a hardware upgrade to the server?

    Are there more intelligent alternative or strategies to consider?

     

     

     

     

    Monday, March 5, 2007 4:50 PM

Answers

  • Unless those records are very wide (hundreds of bytes), that number is nothing.

    Make sure the table has a primary key, a clustered index, and indexes on columns used for lookups (either via joins or WHERE clauses).  Multi-column indexes might be needed.

    The table may be getting fragmented, too.  In SQL 2000, you can use DBCC commands or "Maintenence Plans" to correct this.  SQL Server 2005 has more flexibility here.

    With a properly tuned database, it's possible to have tables with hundreds of millions of records and still get excellent performance :-)

    -Ryan / Kardax

    Monday, March 5, 2007 5:14 PM

All replies

  • Unless those records are very wide (hundreds of bytes), that number is nothing.

    Make sure the table has a primary key, a clustered index, and indexes on columns used for lookups (either via joins or WHERE clauses).  Multi-column indexes might be needed.

    The table may be getting fragmented, too.  In SQL 2000, you can use DBCC commands or "Maintenence Plans" to correct this.  SQL Server 2005 has more flexibility here.

    With a properly tuned database, it's possible to have tables with hundreds of millions of records and still get excellent performance :-)

    -Ryan / Kardax

    Monday, March 5, 2007 5:14 PM
  •  Ryan Lamansky wrote:

    Unless those records are very wide (hundreds of bytes), that number is nothing.

    Make sure the table has a primary key, a clustered index, and indexes on columns used for lookups (either via joins or WHERE clauses).  Multi-column indexes might be needed.

    The table may be getting fragmented, too.  In SQL 2000, you can use DBCC commands or "Maintenence Plans" to correct this.  SQL Server 2005 has more flexibility here.

    With a properly tuned database, it's possible to have tables with hundreds of millions of records and still get excellent performance :-)

    -Ryan / Kardax

     

    calculating the dates off the top of my (rusty) head - I would estimate the database to be circa 6500-6750 in width. Is the issue more to do with database design?

     

    Monday, March 5, 2007 5:40 PM
  • That is a very wide table. However, with good indexing, (and the correct hardware), you should still be getting sub-second returns on queries. Querying millions of rows 'should' still be sub-second.

    It really sounds like a problem with Indexes.

    Monday, March 5, 2007 8:22 PM
    Moderator
  • I'm in agreement with Arnie Rowland... that's extremely wide, but may still be salvagable with good indexes...

    Cutting down the width, though, by moving to a more normalized database structure, will help immensely.  When I was talking about hundreds-of-millions-of-rows-in-a-table-still-being-very-fast I was most definitely not thinking about multi-kilobyte rows (about 100 bytes in that case).

    -Ryan / Kardax

    Monday, March 5, 2007 10:13 PM
  • Thank you all,

     

    I have implemented this over my databases slowly over the course of this week and notice dramatic improvements in data query speeds.

     

    Going forward, I take note of your points regarding the database design and will look to make changes to the structure. This should be a simple task as 95% of data access is via Stored Procedures.

    Friday, March 9, 2007 8:52 AM