Sloooooow stored procedure

Question

Sloooooow stored procedure

Tom Morman

SSC-Addicted

Points: 433
More actions
June 28, 2006 at 1:07 pm

#118522

I have an sp that truncs a table and then inserts new rows. The sp takes over 2 minutes to insert about 80,000 rows while the same code in query analyzer takes just a few seconds. I cleared the proc cache (dbcc FreeProcCache) but this did not help. Does any one have any suggestions as to what the problem might be?
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Not enough info.

What is the source of the inserted rows ? A join of 2 or more tables ? Are there input parameters to the stored prcoedure that are used to filter the rows being inserted ?

Tom Morman SSC-Addicted Points: 433 More actions · Answer 2

the sql is very straight forward:

Truncate TableName

insert into TableName (FieldList)

Select Fields

From TableA a

Join TableB b on a.Acct_id = b.Acct_id

The sql being run in query analyzer is identical to that in the sp. There are no input parameters being passed, no additional filtering.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Do you run only SELECT in QA or INSERT?

How many indexes you have on the table being inserted?

What are the fill factors on them?

Is it used intensively for selects?

_____________
Code for TallyGenerator

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 4

Is the execution plan the same for both runs?

The truncate may cause a re-compilation of the SP.

Lowell SSC Guru Points: 323518 More actions · Answer 5

wouldn't the sp need to be recompiled after the table is truncated, as the previous executtion plan would probably be invalid? i would think the proc is slow because of a bad execution plan.

i'd think that forcing a recompile on the proc would be desired:

create proc pr_procname with recompile as ....

i'd suggest calling a separate proc from within the stored proc instead:

create proc pr_innerproc as

insert into TableName (FieldList)

Select Fields

From TableA a

Join TableB b on a.Acct_id = b.Acct_id

GO

create proc pr_procname as

Truncate TableName

exec pr_innerproc

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!

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 6

All of the above are good suggestion. I would add to that:

Check the connection default settings; compare them with the QA connection settings.

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Tom Morman SSC-Addicted Points: 433 More actions · Answer 7

very good suggestions- Thanks!

Question regarding recompilig the sp- sp_recomplile 'spName' will recopile any give sp. Creating the sp with "WITH recompile" causes the sp to be recompiled each time it is run. Does dbcc FreeProcCache cause all sp's to be recompiled the next time they are run?