Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
Hi This is Chandrakanth. Good Morning.
 
I am working on .Net and SQL Server 2008. I have one query in SQL. Problem is when i am executing procedure with exec Procedure name with parameters
(exec CategoryDashboard '07/10/2012','Weekly','2012','0'), i am getting result in 40 to 60 seconds.
 
But when i execute entire query separately by passing parameters, it is giving result with in 5 seconds.I have already created Indexes on the tables. Can any one suggested me, how to resolve this issue.
 

Thanks
Chandrakanth
 

[edit] Moved from "solution" to question - OriginalGriff [/edit]
 
--exec [CategoryDashboardGrid] '07/10/2012','Weekly','2012','0'

CREATE Procedure [dbo].[CategoryDashboardGrid] 
(
	@DateVal Varchar(250),  
	@PnlSelValue varchar(50),
	@YearNumber int,
	@Category varchar(max)   
) 
as  
Declare @TTotalProductsValue int  
Declare @LTotalProductsValue int  
declare @WeekNo int,@MonthNo int
 
declare @SubCategoryCommasep varchar(max)  
declare @count int  
  
select distinct @count=count(Category) from iView_ProductMaster   
where Category in ((Select convert(varchar,ID) From fnSplitter(@Category)))   
  
if @count > 0  
begin  
	select @SubCategoryCommasep = COALESCE(@SubCategoryCommasep + ',', '')  + Cast(SubCategory as varchar(1000)) from iView_ProductMaster   
	where Category in ((Select convert(varchar,ID) From fnSplitter(@Category)))
	  
	print @SubCategoryCommasep  
end  
else  
begin  
	set @SubCategoryCommasep = 0  
end  
 

IF @PnlSelValue = 'Weekly' or @PnlSelValue = 	'Bi-Weekly'
    begin
  		set @DateVal = CONVERT(datetime,@DateVal,103)
  		select @WeekNo = convert(varchar(10),datepart(wk,@DateVal)), @MonthNo = convert(varchar(10),Month(@DateVal)) ,@YearNumber = convert(varchar(10),year(@DateVal)) 
  	end
else if @PnlSelValue = 	'Monthly' or @PnlSelValue='Yearly'
  	begin 
  		set @DateVal = @DateVal
  		set @YearNumber = @YearNumber
  	end
 
SELECT @TTotalProductsValue = SUM(TotalSales), @LTotalProductsValue = SUM(TotalSalesLY) FROM dbo.tbl_Aggregated_Data 
Where WeekDate = @DateVal and Yearnumber = @YearNumber and PeriodType = @PnlSelValue 
and EAN in (Select EAN from iView_ProductMaster where Category = @Category);
	
   with t as
	(select dbo.[InitCap](p.Category)as Category,Sum(d.TotalSalesLY) as 'TotalSalesLY',
	Sum(d.TotalSales) as 'TotalSales',Sum(d.TotalUnitsLY) as TotalUnitsLY,Sum(d.TotalUnits) as TotalUnits,
	Sum(StoreCountLY) as 'Storecount LY',Sum(StoreCountTY) as 'StoreCount TY'
	from tbl_Aggregated_Data d with (Nolock)  inner join iView_ProductMaster p  with (Nolock) on p.EAN = d.EAN 
	Where d.PeriodType = @PnlSelValue and d.WeekDate = @DateVal  and Yearnumber = @YearNumber 
	and d.EAN in (Select EAN from iView_ProductMaster with (Nolock) where 
	(@count =0 or p.Category in (Select convert(varchar,ID) From fnSplitter(@SubCategoryCommasep))))
	group by p.Category)
	select Category,TotalSalesLY,TotalSales,TotalUnitsLY,TotalUnits,
	Convert(decimal(18,1),dbo.ValueShare(TotalSalesLY,@LTotalProductsValue))as ValueShareLY,
		Convert(decimal(18,1), dbo.ValueShare(TotalSales,@TTotalProductsValue))as ValueShareTY ,
		Convert(decimal(18,1),dbo.ValueGrowth_Per_YA(TotalSalesLY,TotalSales)) as ValueGrowth,
	[Storecount LY],[Storecount TY]	from t;
Posted 6-Jan-13 23:31pm
Edited 6-Jan-13 23:56pm
v2
Comments
Sheikh Muhammad Haris at 7-Jan-13 4:47am
   
Post your SP details here too, it will help in answering
OriginalGriff at 7-Jan-13 4:50am
   
Agreed - we need to see the SP (and the code fragment that calls it) along with the comparable query. Otherwise we are just guessing!
Use the "Improve question" widget to edit your question and provide better information.
OriginalGriff at 7-Jan-13 4:58am
   
I have moved your "solution" into the body of your question - please use the "improve question" widget to provide better information, or you move your question from the "unanswered" list to the "answered" list and reduce the chances of a useful reply.
Sheikh Muhammad Haris at 7-Jan-13 5:07am
   
and also mention which query are you running separately ? (that gives result in no time)
ChandrakanthGaddam at 7-Jan-13 5:12am
   
Hi
Thanks for responding my questions. I have executed entire query by passing parameters. Please find the Query below.
 
---------------------------------------------------------
 
declare @DateVal Varchar(250) = '07/10/2012',
@PnlSelValue varchar(50) = 'Weekly',
@YearNumber int = 2012,
@Category varchar(max)= '0'

Declare @TTotalProductsValue int
Declare @LTotalProductsValue int
declare @WeekNo int,@MonthNo int

declare @SubCategoryCommasep varchar(max)
declare @count int

select distinct @count=count(Category) from iView_ProductMaster
where Category in ((Select convert(varchar,ID) From fnSplitter(@Category)))

if @count > 0
begin
select @SubCategoryCommasep = COALESCE(@SubCategoryCommasep + ',', '') + Cast(SubCategory as varchar(1000)) from iView_ProductMaster
where Category in ((Select convert(varchar,ID) From fnSplitter(@Category)))

print @SubCategoryCommasep
end
else
begin
set @SubCategoryCommasep = 0
end
 
IF @PnlSelValue = 'Weekly' or @PnlSelValue = 'Bi-Weekly'
begin
set @DateVal = CONVERT(datetime,@DateVal,103)
select @WeekNo = convert(varchar(10),datepart(wk,@DateVal)), @MonthNo = convert(varchar(10),Month(@DateVal)) ,@YearNumber = convert(varchar(10),year(@DateVal))
end
else if @PnlSelValue = 'Monthly' or @PnlSelValue='Yearly'
begin
set @DateVal = @DateVal
set @YearNumber = @YearNumber
end
 
SELECT @TTotalProductsValue = SUM(TotalSales), @LTotalProductsValue = SUM(TotalSalesLY) FROM dbo.tbl_Aggregated_Data
Where WeekDate = @DateVal and Yearnumber = @YearNumber and PeriodType = @PnlSelValue
and EAN in (Select EAN from iView_ProductMaster where Category = @Category);

with t as
(select dbo.[InitCap](p.Category)as Category,Sum(d.TotalSalesLY) as 'TotalSalesLY',
Sum(d.TotalSales) as 'TotalSales',Sum(d.TotalUnitsLY) as TotalUnitsLY,Sum(d.TotalUnits) as TotalUnits,
Sum(StoreCountLY) as 'Storecount LY',Sum(StoreCountTY) as 'StoreCount TY'
from tbl_Aggregated_Data d inner join iView_ProductMaster p on p.EAN = d.EAN
Where d.PeriodType = @PnlSelValue and d.WeekDate = @DateVal and Yearnumber = @YearNumber
and d.EAN in (Select EAN from iView_ProductMaster where
(@count =0 or p.Category in (Select convert(varchar,ID) From fnSplitter(@SubCategoryCommasep))))
group by p.Category)
select Category,TotalSalesLY,TotalSales,TotalUnitsLY,TotalUnits,
Convert(decimal(18,1),dbo.ValueShare(TotalSalesLY,@LTotalProductsValue))as ValueShareLY,
Convert(decimal(18,1), dbo.ValueShare(TotalSales,@TTotalProductsValue))as ValueShareTY ,
Convert(decimal(18,1),dbo.ValueGrowth_Per_YA(TotalSalesLY,TotalSales)) as ValueGrowth,
[Storecount LY],[Storecount TY] from t;
 

Thanks
Chandrakanth
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Google this parameter sniffing in sql server 2008 it sometimes makes a difference to localize the parmeters.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi,
 
Try with the Sql Tunning to identify where exactly it's taking time. try to optimize that portions. See the below link for more details
http://blog.sqlauthority.com/2012/04/20/sql-server-performance-tuning-part-2-of-2-analysis-detection-tuning-and-optimizing/[^]
 
Thanks
--RA
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 406
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 205
3 Raul Iloc 170
4 Maciej Los 164
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100