SQL Server Performance Forum – Threads Archive

time to run sp vs time to run query

I created an application on a development SQL server and scripted the tables, views, stored procs, etc into a deployment script and executed that script onto the production server. That worked aok. Now, if I execute a given stored procedure in QA against the original db it takes about 1 min 17 seconds to return 19 rows. Not great, but workable. If I execute that same stored procedure on the prod server, it takes over 3 minutes to return the one existing row. However, if I cut-and-paste the query that’s in that stored procedure into QA and run it by itself against prod, it takes only 3 seconds, a 60:1 ratio. We anticipated that the production server would be much faster than the devl server. So, three questions:
1) Why might prod be slower?
2) Can anyone explain to me why the stored procedure takes so much longer than the query itself?
3) What can I do about it? Thank you.

Can you post the DDL?

That would take pages. What, specifically, should I provide? I double-checked to make sure that the tables in prod have the primary keys set just like in devl. Everything looks ok.

Have you updated the statistics?

I am not sure if this would help, but try to recompile the stored procedure after updating your statisticts. I believe that the optimal execution plan is already determined the first time you ran the SP, which is my reason for uncertainty in the suggested solution.

Success!
Thank you one and all. I am fairly new at this and just learned about the statistics today. I ran an execution plan and updated the statistics. I reran the sproc and the row was returned in 3 seconds. Thanks again!

]]>