Opinion wanted

Posted on 2007-10-02
Last Modified: 2010-03-19
What do you guys think of an SQL Server 2000 Database with a table with close to 30,000,000 records growing by 70,000 per day without any keys whatsoever and no indices at all. The database is being used for several online real-time queries every day.
I have told my manager, that something shouldbe done, but she's not really interested....
Question by:IbDyhr
    LVL 92

    Accepted Solution

    Hello IbDyhr,

    Given that this table is large, is growing, and is being used actively for queries, some sort of
    indexing is warranted.  Exactly how the table be indexed is, of course, dependent on what
    queries are being run, of course...


    LVL 22

    Assisted Solution

    Tell the users who are running the real-time queries it could run a lot faster if they mention performance to your boss.

    LVL 8

    Assisted Solution

    that is a large number. you really really need some kind of indexing, it will make the queries run times and times faster.

    you can also consider horizontally partitioning the data; divide the data into different tables on a criteria, like record month or something, and create a "partitioned view" to access the data like a single table. see "partitioned views"

    Author Comment

    Thanks guys - just what I would have expected.

    Just "counting" the records in the table (Select count(*)... etc) takes about 4 minutes now - definitely not a sustainable situation.


    Assisted Solution

    I would have to agree with the above comments. Some type of indexing would be very beneficial to the performance of the database. Of course you should also look at the size of the record itself not just the number of records. Can you calculate the size of one record and then determine the expected growth of the database over a given time period?

    Another option that could help is developing an archive of the records. Keep a certain amount of data on the Live system and then regularly archive the older data to another system / table. This will definatly free up the space in the database. Of course this would depend on how much data is required for the daily queries.
    LVL 44

    Assisted Solution

    However, be aware that if you create an index on the table, then INSERTs into the table will take longer, as the new index will need to update with each insert.



    Author Comment

    Thanks again.....

    The tip about archiving can unfortunately not be used, as we are talking about financial transactions - so far only covering 1 year, and up to 5 years must be available "just like that".

    I am aware of the extra overhead regarding indices, but in this table data are constantly added (to the end of the table) - nothing is deleted, so I believe the indexing maintenance overhead should be without problems especially for something including dates.

    I will have a further look into the suggestion regarding the "partitioned views"

    Thank again

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This is about my first experience with programming Arduino.
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now