Taking time when executing procedure with exec with paramters when comparing with execute entire query by passing parameters in SQL Server 2008

Question

0.00/5 (No votes)

See more:

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]

SQL

--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 22:31pm

ChandrakanthGaddam

Updated 6-Jan-13 22:56pm

OriginalGriff

v2

Add a Solution

Comments

[no name] 7-Jan-13 4:47am

Post your SP details here too, it will help in answering

OriginalGriff 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 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.

[no name] 7-Jan-13 5:07am

and also mention which query are you running separately ? (that gives result in no time)

ChandrakanthGaddam 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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mycroft Holmes · Answer 1 · 2013-01-07T21:45:00

Solution 2

Google this parameter sniffing in sql server 2008 it sometimes makes a difference to localize the parmeters.

Posted 7-Jan-13 21:45pm

Mycroft Holmes

Rajesh Anuhya · Answer 2 · 2013-01-07T21:50:00

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

Posted 7-Jan-13 21:50pm

Rajesh Anuhya