Solved

Insert occassionally *very* slow, indexes may be problem

Posted on 2010-09-01
10
408 Views
Last Modified: 2012-06-21
I have an INSERT statement running at a customer site that is giving me problems.

It will happen in milliseconds several times, then the next time take a couple minutes, then several in milliseconds -- followed by one that takes nearly an hour!

It's an INSERT ... (SELECT ... from TableA ...join ... join ... join)

Last month I added an index on TableA to better cover its WHERE clause & joins, and the problem was solved.

Yesterday I got the complaint again.  So I rebuilt the index I had created. No better. I updated stats on the table. No better. I rebuilt all indexes and updated all stats. No better.

The INSERT being within a trigger, it's a little hard to analyze exactly ... so for purposes of reviewing the execution plan I substituted the table into which I'm inserting for the INSERTED table.  I think that is valid ... but it is something I've changed.

The execution plan shows 80% of the time being taken to prepare the inserts into 4 indexes on the table into which we're inserting.

I suspected that meant we had a disk writing bottleneck ... but decided to check a few performance indicators ... which found the disk at 100% idle most of the time with a 0-length queue most of the time.  That doesn't sound like a bottle-neck.

I tested moving the problem indexes into a separate filegroup on a different physical disk ... but the execution plan made it look like that was no better.  And again, my disk performance indicators look OK.

What do you recommend I do to try to regain performance?

Thanks!
IndexInsertProblem.jpg
0
Comment
Question by:Daniel Wilson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 8

Assisted Solution

by:Mohit Vijay
Mohit Vijay earned 250 total points
ID: 33576993
Are you using with(nolock) or With(ReadPast) i.e. SQL Hints with your Select Statement.

like

Select A, B, C from ABC with (NOLOCK)

OR

Select A, B, C from ABC with (ReadPast)
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33577001
You should use them, if not using.
0
 
LVL 6

Accepted Solution

by:
LCSandman8301 earned 250 total points
ID: 33577228
are you reading from the table you are inserting to? this could cause the table spools.
check out scary dba's article about the table spool
http://scarydba.wordpress.com/2009/09/09/spools-in-execution-plans/
it seems like the select portion of this insert is probably the portion that is causing you issues.
so i would suggest seeing what you can do to make the actual insert simpler. maybe doing the logical portion ahead of time. but without actually seeing a similar version of the query that's the best i can tell you
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 32

Author Comment

by:Daniel Wilson
ID: 33577981
I got a marginal improvement with a switch from RIGHT JOIN to WHERE NOT EXISTS.

So here's a fragment of the query:

Yes, I'm selecting from the table ... just to be sure I'm not inserting duplicates.  (This is an ETL system and orders are imported and re-imported, so eliminating duplicates is absolutely essential.)

Not using any hints at this point ... will try ...

Insert INTO SODetail (...)
(SELECT ...
From  SODEtail_Import INS 
Where 
 Not Exists (Select ID from SODetail S Where 
S.Ord_Num = INS.Ord_num AND 
dbo.PadSuffix(s.WO_Suf) = dbo.PadSuffix(INS.WO_Suf) AND

S.Download_Seq = INS.Download_Seq AND
S.DeleteME = 0
And S.Other = INS.Other )
)

Open in new window

0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 33578377
with (NOLOCK) is in place now ... not sure whether it's helping.

I would have thought it was the SELECT portion too, but doesn't the execution plan indicate  that it's a matter of writing the indexes that's a problem?
0
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 33579103
the table spools are showing that you are writing to indexes that you are currently using. so it would be harder to write and update an index if it is currently in use.
and usually if you make one part of your query quicker it will help accross the board.
and in this case if you speed up how it reads the data it is importing. then it will speed up the writing of this data too. i have not yeat looked at your query but will be getting to it soon
0
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 33579175
it might just be me and i've never spent the time to look into it but many times what i will do is create a primary key from the data and do a not in rather than a not exists

for example

select *
from table
where col1+':'+col2+':'+col3 not in
(
  select col1match+':'+col2match+':'+col3match
  from matchtable with (nolock)
)

if nothing else you won't be using the same table so many in the query.
i hope that helps
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 33579383
It does seem I was overlooking something.  I saw 4 similar branches showing 2% + 18% each ... so thought they combined for 80% of the query.

There is, however, a SORT preparatory to a CLUSTERED INDEX insert that is taking 79% by itself.

ClusteredIndexInsert.jpg
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 33579407
Upon changing that index to NONclustered, the plan changes significantly and the overall cost drops about 70%.

ChangeToNonClustered.jpg
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 33582252
Well, this one took a variety of tactics ... concluding with a purge of some ancient and abandoned data.

So ... some of it did go back to the SELECT part of the query.

Thanks to both of you for your suggestions.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question