Hi, I used SQL Server 2000, Now I have problem about insert into table very slow. I have record in table 10,000,000 records. I would like to know how to tune database to solve insert very slow or other suggesstion. Thanks.
Make sure the table is indexed properly (index wizard). Remove any unused records (if you don't want to get rid of them completely then create a records_old table to put them in). Do all of those records need to be in the same table? Let's say this is a table that holds content. You have 10 sites that use this data, but no two sites use the same data. Then break it into several smaller tables. Create views for retrieving the data. This will put less load on the table itself. Upgrade your hardware. A 10mill records table is not that big of a table. Lack of RAM may be your biggest issue (or a poor CPU choice).
As already suggested running the Index Tuning Wizard is a good idea, but best to limit it to Indexes only, not Indexed Views as in the past I've had a few issues with Indexed Views. Another couple of things, are you running any maintanence jobs? Such as re-indexing, index defrag and re-org and shrink. All of those will also help performance of databases. Something else is are the database file fragmented on the disk? A tool such as PefectDisk or Diskeeper will help solve that. Jen
Hi, Thank all for your suggesstion. I solved problem by create new database same old database and transfer all data to new database, the problem was solved. I have one question, my problem is database structure or not? Thank you in advance.
I have a suggestion and it's from my own experience, start playing around with MySQL, you will find it faster...
WTF ? Can you let us know what the table structure looks like and what keys and indexes you have on it ?
Considering your now happy with it with the same structure I doubt it is, looks like it was just table and index fragmentation which the migration of the data from one DB to another has for the moment "cured". All of which will reoccur over time unless you maintain your database. Take the advice that's already been given, set up maintenances plans (Weekly is fine), and run the Index Tuning wizard. Jen
Thank you so much Jen. I am beginner with sql server, I would like to know how to run the index tuning wizard or other plans?. please tell me step by step. Thank you in advance
Where is the database? Is it on a web hosters SQL Server or one of your own? Do you also have the SQL Server Enterprise Manager or similar tool available to you? Its a lot easier with those tools, otherwise you may have to wing it a bit. Jen
Hi Jen, Database is at my office's server and I have SQL Server Enterprise Manager (SQL Server 2000). Thanks.
When was the last time you ran DBCC REINDEX on that table? Since you moved to another database, that just means that the keys on the tables were not optimized to the fullest. You should look to be doing that once a week or so depending on how often you delete and insert data into the tables. Also, as an FYI, putting an index on a table will NOT speed up an insert. It will actually slow it down because the data must be inserted into the table, and for every index you have, you must add that new data set to it.
Hi, I have 2 question. 1. Should be reindex once a week? 2. "you must add that new data set to it" How can I do? please tell me step by step Thanks.
It's a good idea to reindex regulary. How many times depends on how the fillfactor is set and how much data you add (and if index pages get split). If you have an index on a column with increasing values, you don't have reindex because new rows are added at the end of the column.. only if there's also data deleted you need to reindex. If you have an index on a column where new rows are entered random, reindexing is more important. Check the properties on a nonclustered index, write down how many pages the index uses, how much fragmentation there is. Reindex. Check how much fragmentation is now and how many pages are used. If this is significant (say less than 20 percent) it's useful to reindex regular. If you want to reindex weekly, check again a week later to see how many difference reindexing makes. A project I'm working on now uses GUIDs.. Foreign keys indexes use 40 percent less pages after reindexing each week..