Click here to Skip to main content
15,889,346 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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....

SQL
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
Posted
Updated 20-Jul-12 0:26am
v2

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[^]
 
Share this answer
 
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[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900