Code running SLOW in stored procedure, but not in query analyser. SQL Server.

[bjswift]

I have a stored procedure I am troubleshooting. It involves a three branch IF statement, and I pulled the code from the condition that I am testing, into query analyser. The query runs in about 1 min 36seconds in query analyser, but with the exact same parameters running the proc using an exec statement, it never finishes. It has gotten up to 10 minutes or so, before I stopped it. I will probably try to let it run when I'm on a lunch break, just to see if it'll ever complete..

Has this ever happened to anyone? Anyone know why the exact same code running inside a proc will not finish, but outside will???

Thanks for any insight.

Brandon.

[Vaderman]

Maybe if you posted your code, someone will have a better opportunity to assist you.

Regards

[bjswift]

Posting the code is not relevant. My problem is not with the code not running, for it executes perfectly outside a stored procedure (in query analyzer), but extremely long inside a stored procedure.

The proc falls into one of three branches, each branch is the same query except for different where clauses. (I guess I could rewrite a dynamic SQL statement and see if that works) However, even if I take out the conditional statements, and only execute one of the branches, it still runs slow in the proc.

I checked the execution plan, and the majority are seeks, no scans. I just don’t get how code will run within seconds when in query analyzer, but almost 30 minutes inside a proc!

[bjswift]

I just ran a drop on the old procedure, and created a new procedure called somthing different, with the exact SAME code and conditions ect...

It ran fine!!! What is up with sql server and running this proc? Is it possible that the stored procedure was hung up from a previous attempt to execute but I stopped it in the middle, and it never fully canceled? However, this doesn't explain why one of the three branches executed successfully...

:(

[bjswift]

Ok. Somoene please explain this. I have 2 stored procedures now, one called Intranet_SalesTrend_TEST and the other one is called MyTestProc.

They have the exact same code, I used the exact same create procedure statement for both (just changed the name). Yet, the Intranet_SalesTrend_Test proc will not execute, and the MyTestProc will...

[exterminator]

Check the max allowable length for a stored procedure or a trigger in the corresponding database server.

[bjswift]

Check the length allowed for the stored procedure? Or check the length of the name? Because I know I've used stored procedures which are longer, and have a longer name.
Maybe I don't understand what you mean by check the length.

[Krzemo]

Problem is connected to stored procedure execution engine.

For the best performance, stored procedures keeps cached query plan (which is stored during procedure compilation). It saves time which in other way SQL server would waste for making new query plan each time SP is executed.
But sometimes it leads to problems, because query plan can be outdated and not optimal (for exaple when U create SP when tables are empty and after that tables become very large). So from time to time U should update statistics on tables and force stored procedures to recompile (by recreate them or by "sp_recompile").

U should also execute sp_refreshview on views based on tables in which design changes (to keep metadata data up to date).

These are basic administrative tasks ...

Hope it helps,
Krzemo.