Executing Stored Procedure Vs. Code within procedure

Question

Executing Stored Procedure Vs. Code within procedure

Eichpeel

Ten Centuries

Points: 1079
More actions
July 23, 2008 at 6:07 pm

#72768

I have been wrestling with the following issue for 3 days and do not understand why the procedure is taking forever while the code takes only a few minutes. Here is the scenario.
1. I have a procedure that takes one parameter @FileID such as
_ImportValidate @FileID
2. The above procedure contains several steps validating records (about 30)
When I simply execute the code inside the procedure by putting a test harness at the top just declaring the @FileID and setting it, it takes about 3 minutes to go through about 30 steps validating 125000 records.
When I call the procedure directly such as EXEC [dbo].[_ImportValidate] @FileID, it takes several hours.
I do not get it. Exactly same code, same environment, same data... HELP!
Much appreciated.
"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

It may be operating off a bad cached plan... try the WITH RECOMPILE option in the stored procedure. It could also be "parameter sniffing"... lot's of articles available for how to prevent that if you Google or Yahoo 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)

Eichpeel Ten Centuries Points: 1079 More actions · Answer 2

Jeff,

Thank you for the feedback. Here are a few things I tried:

- with recompile

- drop and re-created the procedure

- reindexed the entire database

- ran dbcc checkdb

Never heard at parameter sniffing but I will definitely take a look at it. Thanks again.

"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Thanks for the feedback...

By the way, being a bit of a code formatting and documentation freak, I really like your tagline on your posts. 😉

--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)

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

Yeah,

I was also same kind of problem having problem some days before...

then I used updatestats and few above displayed method (with recompile, drop and re-created the procedure) then it worked fine for me..:)

check it too..

Cheers!

Sandy.

--

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 5

hey Eichpeel & Jeff Moden ,

I am sure it will work, because I applied the same and got the success..

one of query took 27 seconds to execute..but when it executed by stored procedure..no result at all...(same prob).....but by using this I resolved it.

just make a "update statistics table_name"

just apply this to all the tables which is present in your query.

and run the procedure....sure..it will resolve your problem..

be happy now..:)

Cheers!

Sandy.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

It does smell like a parameter sniffing problem. See the three articles on parameter sniffing linked from this page[/url].

Any chance of posting the proc so that we can take a look?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass