CODEWITHSUNDEEP
insertsql-server-2008-r2
JCBWS
JCBWS

Reputation: 48

Insert Statement into same table causes inconsistent bottlenecking

I'm currently having an issue with a particular SQL Server table used within a database, I cannot run an insert statement on this table when the records I'm selecting are coming from itself.

Here's the statement:

insert into [Custom Work] ([Description], [Price], [Cost], [Labour Cost], [Made In Material], [Bought Out Material], [Weight], [Machine Shop], [Axles], [Stakes/Bunks], [Beam], [GNK], [Parts], [Line], [Step 1], [Step 2], [Blast], [Paint], [Finish], [Finish - GNK], [Final Assembly], [Shipping], [Eng Hours], [Option Date])
   select 
       [Description], [Price], [Cost], [Labour Cost], 
       [Made In Material], [Bought Out Material], [Weight], 
       [Machine Shop], [Axles], [Stakes/Bunks], [Beam], 
       [GNK], [Parts], [Line], [Step 1], [Step 2], 
       [Blast], [Paint], [Finish], [Finish - GNK], 
       [Final Assembly], [Shipping], [Eng Hours], [Option Date] 
   from 
       [Custom Work]
   where 
       Description = 'custom stuff'
       and Quote# = 123456

When I insert records from another table (say when we're adding a standard option as custom work), it works great like it always has! But when I insert records from itself, it will work here and there but then times out (when run from Access) or goes on forever (if run from SSMS).

This started happening after I made some table structure changes, however I made the same structure changes to the standard options table we have... so, if I can't insert records into itself I shouldn't be able to insert records from another table! :|

I've also ran a trace to help diagnose this issue, but it doesn't provide much for leads...

There's no triggers set against this table, select/update statements also work fine (from all the experimentation I've done)! I've even restarted SQL Server (running SQL Server 2008 R2) after hours (which usually fixes this issue for me), but it didn't this time.

I'm guessing corrupt table properties (foreign/primary keys, constraints, indexes, statistics, etc.) perhaps? Any help would be greatly appreciated :)

EDIT: Would a mere drop and create statement on this table fix this issue? If not, drop and recreating all the table properties?

Upvotes: 0

Views: 465

Answers (2)

JCBWS
JCBWS

Reputation: 48

After discussing this via chat with Yugz, this issue was persisting with this specific table because my front end Acccess db still had locked records on this table (which caused my lock-free insert statement to spin and hinder SQL performance). Also, I haven't been rebuilding my SQL database indexes on a regular basis.

So, for anyone else having this issue, make sure you front-end applications selecting data isn't locking down records and rebuild your indexes! :)

Upvotes: 0

Yugz
Yugz

Reputation: 677

Have you tried using WITH (NOLOCK)?

insert into [Custom Work] ([Description], [Price], [Cost], [Labour Cost], [Made In Material], [Bought Out Material], [Weight], [Machine Shop], [Axles], [Stakes/Bunks], [Beam], [GNK], [Parts], [Line], [Step 1], [Step 2], [Blast], [Paint], [Finish], [Finish - GNK], [Final Assembly], [Shipping], [Eng Hours], [Option Date])
   select 
       [Description], [Price], [Cost], [Labour Cost], 
       [Made In Material], [Bought Out Material], [Weight], 
       [Machine Shop], [Axles], [Stakes/Bunks], [Beam], 
       [GNK], [Parts], [Line], [Step 1], [Step 2], 
       [Blast], [Paint], [Finish], [Finish - GNK], 
       [Final Assembly], [Shipping], [Eng Hours], [Option Date] 
   from 
       [Custom Work] WITH (NOLOCK)
   where 
       Description = 'custom stuff'
       and Quote# = 123456

Upvotes: 1

Related Questions