Post reply

Huge Number of Records - Failed to Be Loaded

  • July 25, 2005 at 5:11 am

    #86870

    Experts out there. I have a problem with the growing size of table records.

    Version: SQL Server 2000

    Total records: appx 1.04 billion

    As briefed in the above statements, I've came across a table, with records grown to the size of approximately 1.04 billion. As a result, the web page takes ages to load the data (I've yet to witness successful retrieval). Even the Query Analyzer is having hard time to display this table

    For your information, the SQL statement is not complex, just a count statement and group the data by date, without involving joins.

    Could anyone suggest me with solution, if there was any? Is this a known problem, and is there any patch?

    Thanks in advance for your kindness in sharing the valuable solution. Thanks.

  • July 25, 2005 at 5:17 am

    #576713

    That's a whole lots of records and is going to take quite a bit of tempdb space to do anything with.

    I'm assuming that you have plenty of disk space and an appropriate index on the date column but just in case can you please post the DDL for it.

    With this number of rows i'd be seriously looking at partitioning the table which may give you somewhere to start looking

  • July 25, 2005 at 5:29 am

    #576719

    Thanks Mike

    The table was created as follows:

    CREATE TABLE objview(objId VARCHAR(35) REFERENCES obj(objId), viewDate DATETIME, ip VARCHAR(255))

    And the query is as simple as:

    SELECT DISTINCT YEAR(viewDate) AS year FROM objview WHERE objId=? ORDER BY YEAR(viewDate)

  • July 25, 2005 at 5:35 am

    #576722

    No problem. If it were me i'd change the use of the YEAR to datapart as it currently wont allow you to make use of any index on the viewdate column (I'm guessing that you have an index on viewdate and objview?).

    SELECT DATEPART(yy, viewDate) AS year FROM objview WHERE objId=? ORDER BY DATEPART(yy, viewDate)

    I thought you said that you wanted to do a count on the number of records which would be something like this

    SELECT DATEPART(yy, viewDate) AS year, count(*) as total FROM objview WHERE objId=? GROUP BY DATEPART(yy, viewDate) ORDER BY DATEPART(yy, viewDate)

  • July 25, 2005 at 7:11 am

    #576747

    If your data for past years is static then I would look at having a specific table into which your write your summary information. That way the only time you need to query your 1.4 billion records is when you need a count for the current year.

    LinkedIn Profile

  • July 25, 2005 at 7:13 am

    #576748

    which brings us back to partitioned tables again

  • July 25, 2005 at 6:37 pm

    #577028

    Thanks to all the valuable suggestions given here. I appreciate it very much. Thanks 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply