July 31, 2002 at 3:57 pm
I am using SQL Server 2000. In one of my databases I have a table with nearly 5 million rows. The INSERTs are taking up to 12 seconds to execute, which is pretty bad since I am running a batch application that does dozens of thousands of INSERTS. I have tried to remove the PRIMARY KEY (the only index I had) and the performance is still the same.
What can be done to improve performance of the INSERT statements ?
July 31, 2002 at 5:46 pm
Are these a single transaction?
Check the log and data phyiscal location, you may have a hardware issue.
Are you sure this is hte only index?
Steve Jones
July 31, 2002 at 5:54 pm
Is your database automatically extending? Change the incrementation value to something much bigger, say 250Mb. This will reduce physical fragmentation.
As Steve says, this could be caused by a hardware issue, such as inappropriate RAID config, or even using an EIDE drive (people do unfortunately!!).
Is the amount of data being inserted causing page splits? Are you inserting text data, or things like nvarchars?
July 31, 2002 at 11:37 pm
If the table has failing triggers, they can cause extremely slow progress. We were inserting into a table that had an incorrect trigger which was trying to use a null value. As it wasn't working as it should, we dropped the trigger. The inserts now took about 3 minutes for 20000 rows instead of 45 minutes...another thing to watch out for!
August 1, 2002 at 1:57 am
try this command.....
DBCC DBREINDEX (authors, '', 70)
it will reindex the data on clustered index with fill factor
One more thing even problem exists then
run dbcc showcontig statement on that table and check for all indexes if they are defraged.
Last thing rename the table and create a new table and insert the whole data from renamed table and then apply indexes
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
August 1, 2002 at 6:51 am
quote:
Are these a single transaction?Check the log and data phyiscal location, you may have a hardware issue.
Are you sure this is hte only index?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
Yes, this the only index, and the INSERT is a single statement. All the inserts on this table are taking longer than expected, however I ran a Profiler against it and found out that minimum execution time is about 3 seconds with peaks of 12 seconds. Since there are no other indexes or constraints defined for the table, this is puzzling me.
August 1, 2002 at 6:57 am
quote:
Is your database automatically extending? Change the incrementation value to something much bigger, say 250Mb. This will reduce physical fragmentation.As Steve says, this could be caused by a hardware issue, such as inappropriate RAID config, or even using an EIDE drive (people do unfortunately!!).
Is the amount of data being inserted causing page splits? Are you inserting text data, or things like nvarchars?
Yes, it is configured to extend 10% of the size, since the whole database has about 10 Gb of size, this means that now the increments will be in 1 Gb segments. The database has still with 2 Gb free space after the last increment. The INSERT statement contains 10 varchar fields and 5 money fields. How can I check if there are constant page splits ?
August 1, 2002 at 6:59 am
quote:
try this command.....DBCC DBREINDEX (authors, '', 70)
it will reindex the data on clustered index with fill factor
One more thing even problem exists then
run dbcc showcontig statement on that table and check for all indexes if they are defraged.
Last thing rename the table and create a new table and insert the whole data from renamed table and then apply indexes
Prakash
I did try to use DTS to export to a new table on another database, and I created no indexes at all not even a primary key, and even with this the INSERTs took up to 12 seconds. I wonder if there is anything that can be done at database level to optimise this.
August 1, 2002 at 7:14 am
How well are other queries doing, such as SELECT statements?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 1, 2002 at 7:57 am
quote:
How well are other queries doing, such as SELECT statements?K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
The SELECTs are excellent, below 50ms response time over the same table. The UPDATEs are also good, because I don't change any field on the primary key. The INSERTs are the killer statements.
August 1, 2002 at 9:11 am
If you have triggers on the table, remove them, then try insert and put back trigger again when you are done. I had this type of problem on a table. But when I removed the trigger and ran the insert performance was much better.
October 16, 2002 at 7:08 am
If you still encounter the same problem, the solution might be found on the following page:
Read under the section "Increasing Performance"
MNKR
MNKR
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply