SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Slow INSERTs on a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mason_Dugaard
Starting Member

3 Posts

Posted - 04/05/2007 :  13:57:41  Show Profile  Reply with Quote
Hello all. I've got a problem with really slow INSERTs on one (and only one) of the tables in a database. For example, using SQL Management Studio, it takes 4 minutes and 48 seconds to insert 25 rows. There are only about 8 columns in the table and only about 1500 records. All the other tables in the database are very fast for inserts.

Another odd thing uniquely associated with INSERTs on this table: prior to inserting the 25 new rows of data, SQL Management Studio tells me that it inserted 463 rows of data which I know did not happen. Here's the INSERT statement:

INSERT INTO FieldOps(StudySiteID
, QA_StructureID
, Notes
, PersonID)
SELECT DISTINCT StudySiteKey
, QA_StructureKey
, SampleComments1
, '25'
FROM ScriptOutput_Nitrate
WHERE (ScriptOutput_Nitrate.StudySiteKey IS NOT NULL)


and SQL Management Studio (eventually) says:
(463 row(s) affected)
(463 row(s) affected)

(25 row(s) affected)

The table has an index on the primary key (INT data type with auto increment). I tried running the following code to fix things but it made no difference:

USE [master]
GO
ALTER DATABASE [FieldData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

use FieldData
GO
DBCC CHECKTABLE ('FieldOps', REPAIR_REBUILD) With ALL_ERRORMSGS
GO

USE [master]
GO
ALTER DATABASE [FieldData] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO


I'm guessing that the problem might be related to the index (??). I don't know... Does anyone here have a suggestion as to what I should do to fix this problem.

Edited by - Mason_Dugaard on 04/05/2007 13:58:37

mcrowley
Aged Yak Warrior

771 Posts

Posted - 04/05/2007 :  14:02:40  Show Profile  Reply with Quote
Sounds like you have a trigger on the FieldOps table. That is probably what is slowing you down. If not that, how quick is the select statement?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/05/2007 :  14:15:35  Show Profile  Reply with Quote
Indexes would be a proble if you had too many of them, not the other way around...the fatsest inserts would be on a table with no indexes.

I would do sp_who2 active to see if there is any blocking



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 04/05/2007 :  14:22:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's a trigger causing this. There's no other way to get that output that you are seeing when you run that simple insert statement.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 04/05/2007 14:22:56
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/05/2007 :  14:26:40  Show Profile  Reply with Quote
True enough, but I still expect some processes (probably the triggers) are causing the contention



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Mason_Dugaard
Starting Member

3 Posts

Posted - 04/05/2007 :  14:40:54  Show Profile  Reply with Quote
Thanks for all the replies. A little follow-up on the comments:
1) I don't think there are any triggers associated with this table. At least none show up when I view the table's dependencies in SQL Management Studio.
2) SELECT statements are very fast (less than 1 second to return all rows).
3) I ran the sp_who2 command and got the following results related to the table in question:
SPID 91, RUNNABLE, "SELECT INTO", CPU TIME: 62
SPID 151, sleeping, "AWAITING COMMAND", CPU TIME: 47109 --IS THIS THE PROBLEM?
SPID 152, sleeping, "AWAITING COMMAND", CPU TIME: 219

All ideas appreciated! Thanks again for the help!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 04/05/2007 :  14:51:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run this:

EXEC sp_helptrigger 'FieldOps'

What does it return?

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Mason_Dugaard
Starting Member

3 Posts

Posted - 04/05/2007 :  18:08:43  Show Profile  Reply with Quote
Ha! Sure enough there was a trigger! And it wasn't even doing anything useful!

trigger_name: tr_ThermographDataSetsNew

I've fixed the problem. Thanks again for the help! tkizer is, in fact, an Almighty SQL Goddess.

Thank you all so very, very much!

Edited by - Mason_Dugaard on 04/05/2007 18:13:56
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 04/05/2007 :  18:35:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well mcrowley is the first one to mention the trigger. So he gets most of the credit!

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/06/2007 :  11:05:07  Show Profile  Reply with Quote
SQLe Goddess? I knew her when she was a lowly princess...in any case...when you seemultiple rows modified, something, as in a trigger, is happening...good night, and good luck



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000