asked
Large amount of data
Dear all, I have a question about a large data table. I have a table with more than 400 million records, I would like to hear any Idea how to manage with it? Any idea will be helpful for me. Thanking you in advanced for your effort Gogolo.
Avoid the table designer to make any changes on very large tables. The table is in the worst case copied into a new table. Use SQL scripts to alter the table
I'm not sure I understand your question. Are you asking: - How to **ALTER** the table (adding/removing columns, constraints, indexes etc) - What to do with the table in order to get optimal performance in the database. In the first case - I agree with @Håkan Winther. Don't use any GUI tools to alter the table, it will cause unnecessary headache and possibly gigantic transaction logs etc. In the second case, it will depend largely on what's in the table, how much transactions there are, how often it's selected from etc.
To answer that, you must give much more information about your data. How much transactions do you have on the table, how much is it selected from, what does the data look like (is it periodic data etc), is the table very wide? Is the database normalized or not? If the table is transaction intensive and you have periodic data in it, you might want to consider partitioning the data in the table, so that you only have the latest month/year/period in one partition and the rest of the data in other partition(s). That might or might not speed up performance for both modifications and selects. But again - it will depend on the data and how it's used.
Related to ALTER for adding/removing columns to the table I will probably a little bit in contrast with @Håkan Winther and @Magnus Ahlkvist. To Add or Remove columns from very large table it is better to create a new table with more/less columns and then INSERT SELECT data to it from the original table and then DROP the original table and rename the new one, which is approach which also the SSMS uses. It is better from 2 things. if you use ALTER directly on that table, ALL data will be read and then written back with added/removed columns. If adding a column you will receive page splits and also large amounts of data written to transaction log as all th changes will be logged. Wen you create a new table, you will populate it by BULK INSERT which is minimally logged operation (if you are using a FULL RECOVERY mode, you can temporarily switch to BULK mode for that purposes). Also your data will be correctly aligned without any page splits and will be placed sequentially. The only pro of this approach is, that you need double the size in the data file group. But on the other side the first approach requires large amount of data in TLog.
First question is, are you having performance problems with the table now? If so, what are they? You may simply need one or more indexes. The most common cause of performance problems is the code, not the structure. Have you looked at the code being run against the table? Is it avoiding common issues like functions on columns in the WHERE clause and stuff like that? Finally, if all of this has been carefully addressed, and I would do these things first, you can look at addressing performance issues with hardware. If the data structure supports it, you can look into partitioning where you split the storage up across multiple disk drives in order to distribute access. That's one of the hardest things to do and only helps when you can distribute the data and purchase the hardware to support it.
Not automatically, no. Maintenance will take longer, updating stats will take more time, rebuilding indexes will take more time, backups take longer... all that is inevitable. But there is not automatic on queries taking longer. If the index is good for the data and the stats are up to date, you should see good performance, even for lots of rows.
You will know what sort of data this and how it is used, accessed, archived better than us but you may want to investigate partitioning (tables and indexes)
http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx and
http://msdn.microsoft.com/en-us/library/ms188730.aspx and much more via "your preferred search engine" as a way to manipulate large chunks of data that can be grouped together with common characteristics - eg Date ranges on a order date column. Whether the data is mainly updated, inserted or deleted will influence how you can best optimise it.
