Insert data with select statement is very slow.

Question

Insert data with select statement is very slow.

pphimchaidee

SSC Eights!

Points: 859
More actions
September 5, 2011 at 3:50 pm

#247413

I have a complex query that takes abouts 3-4 minuites running to get 80,xxx rows. That is not a big deal. The problem is that it take so long time to insert the result set to a destination table. There is no index on the destination table. Would the insert be faster if creating index on it?
The query is like
Insert into TABLE_NAME
select (complex query )
Please suggest me a solution. Thank you.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I would suggest to get a good answer to your question you post the table definition, and the actual query text AND a copy of the execution plan ... For each of these click on both the links in my signature box to learn how to post the requested information quick and easily.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

baabhu SSCertifiable Points: 6205 More actions · Answer 2

Please identity the area where it can slow down

1) How long does the complex select takes to produce the result.

2) check the tables involved in the complex select.

3) Insert record to the target table 1 record to identity the time taken to insert.

These analysis will help to understand which part is taking time.

pphimchaidee SSC Eights! Points: 859 More actions · Answer 3

Hi everyone,

I have attached actual execution plans of main select query and insert query.

The Select query itself retrieves 80826 rows within 3 minutes.

For the Insert query execution plan, I try to insert data only 10 records. It took 24 minutes.

I'm not sure if that is enough info, pls let me know if you want any info.

Thank you

LutzM SSC Guru Points: 107049 More actions · Answer 4

Please attach the actual query since the execution plan does not show the full query.

My pot shot would be this task would benefit from a divide'n'conquer approach (based on the complexity of the execution plan and the re-usage of several large tables).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 5

i see lots of issues; many , many of your tables are HEAPs, meaning without a primary key and a nice clustered index to access them, they will slow things down.

your destination table, [Repl_AxWalton].[ODS].[UDI_ Production_Pam] is a heap, as well as

[WFWaitlist],

[Phase Region]

[WFCLIENTFILESALESORDER]

[WFCLIENTFILE]

[All Land Cos]

[_PROC]

[_ActInst]

[_Act]

[ADDRESS]

and a few more. adding primary keys and clustered indexes will go a long way to fixing performance issues, as well as space recovery considerations.

one of the expensive items was the RID lookup on [Repl_AXWalton].[WF].[WFCategory], .

looks like the LandUnit's expression for getting the value was very expensive as well:

CASE

WHEN LEFT(wfia.LandUnit,6) = 'KC1160'

THEN 'KC1160' + '-' + '0' + SUBSTRING(wfia.LandUnit, 8, 3)

ELSE wfia.LandUnit END LandUnit,

i'd start first with eliminating heaps with tables with PKs, clustered indexes,a dn then start working on adding indexes, i think.

hope this helps!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 6

also the index on [Repl_AxWalton].[WF].[WFInventoryAllocation]: the statsitics on it made it scan a lot more rows: it was estimated @ 215,560 but it actually scanned 431,012,220 rows.

i'd rebuild the index or update statsutics with fullscan on that table, too.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 7

did you make any more progress on this?

I'd love to hear if you added clustered indexes and if they made a difference.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pphimchaidee SSC Eights! Points: 859 More actions · Answer 8

Hi Lovell,

I'm doing what you have recommended. Anyway, I'm not sure what is wrong today with the server. The main query is even worse. Anything going on, I'll let you know.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

As Lowell pointed out, you have an accidental cross join (think "many-to-many" join and look for the arrows with counts larger than the table or index they're coming from) in the stand alone SELECT and, because of the INSERT, an ever worse one shows up on the SELECT for the insert.

A lot of people make the mistake of joining too many tables. Split this query up by using a Temp Table or two... isolate the minimum rows that you can in the Temp Tables. You can very likely get this query down to something that runs in less than 3 seconds. It's just going to take a little time, effort, and research on your part to fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pphimchaidee SSC Eights! Points: 859 More actions · Answer 10

Hello Jeff,

I am changing the query as you mention. The performance is getting better. Now I got the Arithmatic overflow error but I think I could handle it.

Thank you for you guys sharing knowledge with me. 🙂

Pam