| Author |
Topic |
|
Mason_Dugaard
Starting Member
3 Posts |
 Posted - 2007-04-05 : 13:57:41
|
| 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_NitrateWHERE (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]GOALTER DATABASE [FieldData] SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOuse FieldDataGODBCC CHECKTABLE ('FieldOps', REPAIR_REBUILD) With ALL_ERRORMSGSGOUSE [master]GOALTER DATABASE [FieldData] SET MULTI_USER WITH ROLLBACK IMMEDIATEGOI'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. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-04-05 : 14:02:40
|
| 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? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-05 : 14:22:03
|
| 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 Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Mason_Dugaard
Starting Member
3 Posts |
Posted - 2007-04-05 : 14:40:54
|
| 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: 219All ideas appreciated! Thanks again for the help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-05 : 14:51:12
|
| Run this:EXEC sp_helptrigger 'FieldOps'What does it return?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Mason_Dugaard
Starting Member
3 Posts |
Posted - 2007-04-05 : 18:08:43
|
| 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! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-05 : 18:35:53
|
| Well mcrowley is the first one to mention the trigger. So he gets most of the credit!Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Slow INSERTs on a table