![]() Sp_executesql: -Runs fine outside of a stored procedureĮXECUTE sp_executesql -Runs fine outside of a stored procedure ![]() The original: -Runs fine outside of a stored procedure To sum up, the following run fast from QA, but slow when put into a stored procedure: My next attempt will be to have StoredProcedureA call StoredProcedureB call StoredProcedureC call StoredProcedureD to query the view.Īnd failing that, have the stored procedure call a stored procedure, call a UDF, call a UDF, call a stored procedure, call a UDF to query the view. Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)īut what can account for SQL Server being unable to run as fast as SQL Sever running a query, if not parameter sniffing. Why is the stored procedure version so slow? What can possibly account for SQL Server running ad-hoc SQL faster than a different kind of ad-hoc SQL?Ĭhange the code at all Microsoft SQL Server 2000 - (Intel X86)Ĭopyright (c) 1988-2003 Microsoft Corporation I tried moving the definition of the view directly into the stored procedure (violating 3 business rules, and breaking an important encapsulation), and that makes it only about 6x slower. selecting from the view from a stored procedure is 40x slower?.moving the query to a view, and selecting from the view is fast.No table contains computed columns, indexed or otherwise.įor the hell of it I tried creating the view with SET ANSI_NULLS ON The view only references underlying tables. The entity " Report_Opener" is a view, which is not indexed. WHERE SessionGUID = BY CurrencyTypeOrder, Rank' I've tried converting the procedure to dynamic SQL: CREATE PROCEDURE dbo.ViewOpener uniqueidentifier So that it's plan is never cached, and I've tried forcing a recompile at execute: EXECUTE ViewOpener WITH RECOMPILE I've also tried defining the stored procedure WITH RECOMPILE: CREATE PROCEDURE dbo.ViewOpener uniqueidentifier I've tried forcing recompiles, and prevent parameter sniffing, by using a decoy variable: CREATE PROCEDURE dbo.ViewOpener uniqueidentifier AS I've dropped and recreated the stored procedure in order to get it to generate a new plan. ![]() Procedure to CREATE PROCEDURE dbo.ViewOpener uniqueidentifier ASĪnd back again, trying to really trick it into recompiling. WHERE SessionGUID = BY CurrencyTypeOrder, Rankīut putting the same SQL in a stored procedure runs slow, and with a totally different execution plan CREATE PROCEDURE dbo.ViewOpener uniqueidentifier ASĪnd it still runs the same (badly), and I've also changed the stored A query runs fast: DECLARE uniqueidentifier
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |