存储过程性能问题 [英] stored procedure performance issue
问题描述
我有计费应用程序，它具有一些报告生成功能.运行 6-7 个月后，我面临有线问题.我的 sql 语句在 management studio 编辑器中完美运行(执行 4 秒)，但是当我将相同的查询放入 SP 时，它需要(1 分 9 秒来执行相同的查询).表有 150K 行.下面是我在 SP 中使用的查询.我的应用显示超时到期异常消息.我尝试将连接字符串中的超时设置为 180 秒.(对于临时解决方案)但没有积极的结果.
i have billing app which has some report generation feature. after running 6-7 months i am facing wired problem. i sql statement runs perfectly (4 sec to execute) in management studio editor, but when i put same query to SP it takes (1 min 9 sec to execute same query). table has 150K rows. below is the query i am using in SP. My app shows Timeout expired exception message. I tried setting timeout in connection string to 180 secs. (for temp. solution) but no positive result.
ALTER PROCEDURE [dbo].[rpt_GetShiftEndReport](
@BillDate varchar(10),
@JobShift int,
@MonthStartDate varchar(10),
@MonthEndDate varchar(10)
)
AS
--begin tran
SET NOCOUNT ON;
SELECT Products.pCode AS ProductCode, MIN(Products.pName) AS ProductName, MIN(Products.pSize) AS ItemSize, MIN(I.gName) AS GroupName, Sales_Trans.Price,
SUM(Sales_Trans.Sales_Qty) AS SalesQty, SUM(Sales_Trans.Sales_Value) AS SalesValue, SUM(Sales_Trans.Break_Qty) AS BreakQty,
SUM(Sales_Trans.Break_Value) AS BreakValue, SUM(Sales_Trans.Return_Qty) AS ReturnQty, SUM(Sales_Trans.Return_Value) AS ReturnValue,
MIN(Products.CloseStock) AS Stock, MIN(Products.pGroup) AS GroupCode,
dbo.GetCummulativeSales(@MonthStartDate, @MonthEndDate, Products.pCode) AS CummSales
FROM Products INNER JOIN
(SELECT SalesLog_1.ProductCode, SalesLog_1.Price, SalesLog_1.Quantity AS Sales_Qty, SalesLog_1.Price * SalesLog_1.Quantity AS Sales_Value,
0 AS Break_Qty, 0 AS Break_Value, 0 AS Return_Qty, 0 AS Return_Value
FROM SalesLog AS SalesLog_1 INNER JOIN
Sales ON SalesLog_1.MemoNo = Sales.MemoNo
WHERE (SalesLog_1.BillDate = @BillDate) AND (Sales.JobShift = @JobShift)
UNION ALL
SELECT ProductCode, Price, 0 AS Sales_Qty, 0 AS Sales_Value, 0 AS Break_Qty, 0 AS Break_Value, Quantity AS Return_Qty,
Price * Quantity AS Return_Value
FROM SalesReturn
WHERE (BillDate = @BillDate) AND (JobShift = @JobShift)
UNION ALL
SELECT ProductCode, Price, 0 AS Sales_Qty, 0 AS Sales_Value, Quantity AS Break_Qty, Price * Quantity AS Break_Value, 0 AS Return_Qty,
0 AS Return_Value
FROM Breakages
WHERE (BillDate = @BillDate) AND (JobShift = @JobShift)) AS Sales_Trans ON Products.pCode = Sales_Trans.ProductCode INNER JOIN
ItemGroup AS I ON I.gCode = Products.pGroup
GROUP BY Products.pCode, Sales_Trans.Price
ORDER BY GroupCode, ItemSize DESC;
任何人都可以建议我现在做什么.我不认为它的查询问题可能没有.行.
can any one suggest me what to do now. i don't think its query problem may no. of rows.
推荐答案
如果您确定 sql 完全相同并且参数相同，那么您可能遇到了参数嗅探问题.
If you are sure that the sql is exactly the same and that the params are the same then you could be experiencing a parameter sniffing problem .
这是一个非常罕见的问题.我只遇到过一次这种情况，从那以后我一直把问题编码掉.
It's a pretty uncommon problem. I've only had it happen to me once and since then I've always coded away the problem.
从这里开始快速了解问题:
Start here for a quick overview of the problem:
http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx
http:///elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/
尝试在 sp 中声明一些局部变量并为它们分配参数值.使用局部变量代替参数.
try declaring some local variables inside the sp and allocate the vales of the parameters to them. The use the local variables in place of the params.
这是一个功能不是一个错误，但它让你去@"$@
It's a feature not a bug but it makes you go @"$@
这篇关于存储过程性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
