how can i handle large database table in sql 2005 ?

hello guys,
i have large database table contains over 600 million records and all the data type is integer, and the data grow everyday
the problem is when i want to select record from this table, the procedure takes long time to execuate and some times the execuation terminated.
DarabsehAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BrandonGalderisiConnect With a Mentor Commented:
I'll assume "right now there is now indexing" is "right now there is NO indexing".  You need to have indexes that will cover your select statements.  If you are only gaining 2K records per day, you won't have a lot of overhead with maintaining indexes during DML operations.  Do you at least have a clustered index on an identity column or something.  Without a clustered index you have a heap.  And without any indexes, any query with a where clause will still do a table scan.  Your indexes have to be built for HOW you intend to use the table.
0
 
BrandonGalderisiConnect With a Mentor Commented:
Are you trying to pull all records, or just some records?  Are the fields you are filtering on indexed?  Are you joining to other tables, or are you just selecting the integers?
0
 
DarabsehAuthor Commented:
actually the table already contains 600 million records, everyday this table increased by 2000 records automatically.
right now there is now indexing, and there is no relation between other table like foriegn key relationship but i make this relation in the selection statment
0
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.

 
adatheladConnect With a Mentor Commented:
For a start, you need indexes else you won't get any performance. That should be your first port of call.

Next, if you have SQL 2005 Enterprise Edition, you should consider partitioning your tables:
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
0
 
DarabsehAuthor Commented:
actually i am confised now between two issues,
1- is the indexing will be helpfull enough in the performance for the select statements(if you have a formula or ratio for the performance gains by indexing will be great)
2- is indexing better than partitioning or spliting into different tables in this case?.
0
 
adatheladConnect With a Mentor Commented:
1) without a doubt, indexing will help performance. If you imagine a book with 500 pages, with no index. To find every reference to a particular subject you have to flick through every page individually checking if each one is relevant. If there is an index, you can jump straight to the specific pages of the book which may be say 5 out of the 500. Now imagine the situation with your table - 600 million records and you are looking for records where FieldA='Some Value'....without an index on FieldA, SQL will be doing a Table Scan which is scanning through each of the 600 million records to find the ones that match the criteria. Not at all efficient. Adding an index on FieldA would help no end as it can use the index to identify the rows that satisfy the condition. There's no hard and fast formula/ratio for performance gains by indexing - it's all dependent on the scenario. The "downside" of an index is that it will slightly slow down INSERTs/UPDATEs to the table as the index needs to be updated. So there is a balance to be struck (don't create unnecessary indexes on columns that are never queried on etc).

2) I'd say initially, you need to sort the indexes. Leave this option for now - see what the performance is like with indexes and go from there. You definitely need indexes. Table partitioning is something to consider when you have larges amounts of data as you can split the data into seperate partitions, each of a smaller size, therefore each can have a smaller index which is more efficient to query/update. But like I say, go with 1) first. Partitions may be something to be aware of for future.
0
 
DarabsehAuthor Commented:
thank you for helping me
0
All Courses

From novice to tech pro — start learning today.