Opinion wanted

Posted on 2007-10-02
Medium Priority
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 93

Accepted Solution

Patrick Matthews earned 400 total points
ID: 20003137
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

JimBrandley earned 400 total points
ID: 20003178
Tell the users who are running the real-time queries it could run a lot faster if they mention performance to your boss.


Assisted Solution

MrRobot earned 400 total points
ID: 20003269
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"
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 20003618
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

lloyd36 earned 400 total points
ID: 20007016
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

Arthur_Wood earned 400 total points
ID: 20007815
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

ID: 20010601
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Introduction to Processes
Loops Section Overview

864 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