存储过程大约需要60秒 [英] stored procedure taking almost 60 sec to excute

问题描述

好心的帮助.我有以下建议，需要大约一分钟才能完成执行.任何想法都会被接受.在网上检查并应用其他人说的建议！没有改善....

  USE  [xxx]
转到
/*   *****对象:StoredProcedure [dbo].[Reports.Stock.StockSummary]脚本日期:07/20/2012 12:31:30 ******/
- 在ANSI_NULLS上设置
转到
设置  QUOTED_IDENTIFIER  >打开
转到


更改  proc  [dbo].[Reports.Stock.StockSummary]
(
 @ StartDate   as   date ，
 @ EndDate   as   date ，
 @ DistributorUserID   varchar ( 50 )

)
为

声明  @ sqlStatement   as   nvarchar (最大值)；
声明  @ sqlCondition   as   nvarchar (最大值)；
 set   @ sqlStatement  = ' ';
 set   @ sqlCondition  = ' ';
 set   @ sqlCondition  = ' ';

选择  DISTINCT  Products.ProductCode，Products.Description  As 产品名称，
dbo.[GetOpeningStock](Products.ProductID， @ StartDate ， @ DistributorUserID )OpeningStock，
dbo.GetIssuesBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)问题问题，
dbo.GetAdjustmentBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)按进行调整，
dbo.GetOpeningStock(Products.ProductID， @ StartDate ， @ DistributorUserID )
+ dbo.GetIssuesBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)
+ dbo.GetAdjustmentBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID) As  IssuesTotal，
dbo.GetSaleQuantityBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)销售，
dbo.GetFocQuantityBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)作为 FOC，
 0   as 转移， 0  替换，
dbo.GetSaleQuantityBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)+ dbo.GetFocQuantityBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID) As  TotalDisposal，
-  dbo.[GetClosingStock](Products.ProductID，@ StartDate，@ EndDate，@ DistributorUserID)作为CurrentStock 
(dbo.GetOpeningStock(Products.ProductID， @ StartDate ， @ DistributorUserID )
+ dbo.GetIssuesBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)
+ dbo.GetAdjustmentBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)-
dbo.GetSaleQuantityBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)+
dbo.GetFocQuantityBySalesMan( @ StartDate ， @ EndDate ， @ DistributorUserID ，Products.ProductID)) as  CurrentStock
 FROM 产品加入 StockHistory  on  StockHistory.ProductCode = Products.ProductCode
位置 StockHistory.OpeningStock>  0 
 AND  StockHistory.DistributorUserID =  @ DistributorUserID 


- 设置@sqlStatement = @sqlStatement + @sqlCondition; 
- 打印@sqlStatement; 
-  EXEC sp_executesql @sqlStatement  

解决方案

在SQL Profiler中运行存储过程，并为您推荐索引以优化执行.

http://msdn.microsoft.com/en-us/library/ff650692.aspx [ ^ ]

使用SQL Server的查询分析器查找查询成本和性能.查看瓶颈在哪里，并尝试对其进行微调.

您正在使用很多的嵌套SP，这会影响性能.也许您可以在数据库中创建视图，然后使用它们获取数据(不确定是否已经这样做了！)

参考文献:
查询分析器，位于以下位置:
-程序> Microsoft SQL Server 2008 R2>用于查询分析器的SQL Server Management Studio.
-程序> Microsoft SQL Server 2008 R2>性能工具>用于Profiler的SQL Server Profiler.

DB中的视图:
MSDN:创建VIEW [ SQL Server 2005中的视图概述 [ ^ ]

kindly help.i have the following sp bt it take almost a minute to complete execution.any ideas will be accepted.checked on the net and applied what others r saying bt alas! no improvement....

USE [xxx]
GO
/****** Object:  StoredProcedure [dbo].[Reports.Stock.StockSummary]    Script Date: 07/20/2012 12:31:30 ******/
--SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER proc [dbo].[Reports.Stock.StockSummary]
(
	@StartDate as date,
	@EndDate as date,
	@DistributorUserID varchar(50)

)
as

declare @sqlStatement as nvarchar(max);
declare @sqlCondition as nvarchar (max);
set @sqlStatement ='';
set @sqlCondition ='';
set @sqlCondition = '';

SELECT  DISTINCT Products.ProductCode, Products.Description As ProductName, 
dbo.[GetOpeningStock](Products.ProductID,@StartDate,@DistributorUserID) OpeningStock,
dbo.GetIssuesBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As Issues,
dbo.GetAdjustmentBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As Adjustment, 
dbo.GetOpeningStock(Products.ProductID,@StartDate,@DistributorUserID) 
+ dbo.GetIssuesBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) 
+ dbo.GetAdjustmentBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As IssuesTotal,
dbo.GetSaleQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As Sales, 
dbo.GetFocQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As FOC,
0 as Transfers, 0 as Replacement,
dbo.GetSaleQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) + dbo.GetFocQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As TotalDisposal,
--dbo.[GetClosingStock](Products.ProductID,@StartDate,@EndDate,@DistributorUserID) as CurrentStock
(dbo.GetOpeningStock(Products.ProductID,@StartDate,@DistributorUserID) 
+ dbo.GetIssuesBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) 
+ dbo.GetAdjustmentBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID)-
dbo.GetSaleQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) + 
dbo.GetFocQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID)) as CurrentStock
FROM Products join StockHistory on StockHistory.ProductCode = Products.ProductCode
WHERE  StockHistory.OpeningStock > 0
AND StockHistory.DistributorUserID = @DistributorUserID


--set @sqlStatement = @sqlStatement + @sqlCondition;
--print @sqlStatement;
--EXEC sp_executesql @sqlStatement

解决方案

Run you stored procedure in the SQL Profiler and have it recommend indexes for you to optimize the execution.

http://msdn.microsoft.com/en-us/library/ff650692.aspx[^]

Use Query analyzer of SQL Server to find the query cost and performance. See where the bottle neck is and try to fine tune it.

You are using lots of nested SP''s, that is going to impact performance. May be you can make views in your database and then use them to get data (not sure if you are already doing it not!)

References:
Query Analyzer, find at location:
- Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio for Query Analyzer.
- Programs > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler for profiler.

Views in DB:
MSDN: Create VIEW[^]
Overview of Views in SQL Server 2005[^]

这篇关于存储过程大约需要60秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！