• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Slow Query

The query of SSRS report takes long time .

select a.InvoiceID,a.CustomerCode,a.CustName,a.dimension,
a.dimension2_,a.dimension3_ ,
a.SALES_AMOUNT,a.COST_AMOUNT,a.GROSS_PROFIT,a.MARGIN_PER
from (
select InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT,
case
when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
else
sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 END MARGIN_PER

FROM  salesdim

where invoicedate between @startdate and @enddate
and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)

GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_)a
where a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc
0
AnnaJames77
Asked:
AnnaJames77
  • 4
  • 3
  • 2
  • +5
5 Solutions
 
deightonCommented:
when you select the records

'where invoicedate between @startdate and @enddate
and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)'

how many records is that, and how many records are there in the view in total?
0
 
AnnaJames77Author Commented:
In total there are 752000 rows in the table.
0
 
Roman GhermanSenior Software EngineerCommented:
Hi,

Try this:
select 
	a.InvoiceID,
	a.CustomerCode,
	a.CustName,
	a.dimension,
	a.dimension2_,
	a.dimension3_ ,
	a.SALES_AMOUNT,
	a.COST_AMOUNT,
	a.GROSS_PROFIT,
	case 
		when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
		when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
		when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
		else GROSS_PROFIT/LineAmountMST * 100 
	END MARGIN_PER
from 
	(
	select 
		InvoiceID,
		CustomerCode,
		CustName,
		dimension,
		dimension2_,
		dimension3_,
		sum(LineAmountMST) SALES_AMOUNT,
		SUM(linecostAmount) COST_AMOUNT,
		sum(LineAmountMST- linecostAmount) GROSS_PROFIT
		--case 
		--	when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
		--	when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
		--	when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
		--	else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 
		--END MARGIN_PER
		

	FROM  
		salesdim
	where 
		invoicedate between @startdate and @enddate
	and itembuyergroupid  = @itembuyergroupid
	and itemgroupid = @itemgroupid
	and salesoriginid = @salesoriginid
	and CustomerCode = @Cust
	and dimension = @Dept
	and dimension2_ = @CC
	and dimension3_ = @Purpose
	GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
	) a
where 
	a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
IsildursHeirCommented:
In the following lines

and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)

I understand @itembuyergroupid etc all come from  variables/parameters passed to SSRS. Is it possible for you to tweak them to replace IN with exists and then check if that gives a better execution plan? Often, subquery with IN is inferior to exists in performance but not always. You may check this article for more details - http://blog.sqlauthority.com/2010/06/05/sql-server-convert-in-to-exists-performance-talk/
0
 
rushShahCommented:
try this,


select * from (
select 
	a.InvoiceID,
	a.CustomerCode,
	a.CustName,
	a.dimension,
	a.dimension2_,
	a.dimension3_ ,
	a.SALES_AMOUNT,
	a.COST_AMOUNT,
	a.GROSS_PROFIT,
	case 
		when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
		when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
		when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
		else GROSS_PROFIT/LineAmountMST * 100 
	END MARGIN_PER
from 
	(
	select 
		InvoiceID,
		CustomerCode,
		CustName,
		dimension,
		dimension2_,
		dimension3_,
		sum(LineAmountMST) SALES_AMOUNT,
		SUM(linecostAmount) COST_AMOUNT,
		sum(LineAmountMST- linecostAmount) GROSS_PROFIT
		--case 
		--	when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
		--	when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
		--	when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
		--	else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 
		--END MARGIN_PER
		

	FROM  
		salesdim
	where 
		invoicedate between @startdate and @enddate
	and itembuyergroupid  = @itembuyergroupid
	and itemgroupid = @itemgroupid
	and salesoriginid = @salesoriginid
	and CustomerCode = @Cust
	and dimension = @Dept
	and dimension2_ = @CC
	and dimension3_ = @Purpose
	GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
	) a ) t
where 
	t.MARGIN_PER between @marginFrom and @marginTo
order by t.MARGIN_PER desc

Open in new window

0
 
Roman GhermanSenior Software EngineerCommented:
Hey rushShah,

Isn't that the same as my solution?

You just added select * from (....)
0
 
jimtpowersCommented:
I assume salesdim is properly indexed. Have you checked the estimated execution plan to see if SQL Server thinks there is a missing index? Also, I have noticed in my SSRS reports, they all run SIGNIFICANTLY faster if the query processing is in a stored procedure and I just call the SP from the report.
0
 
Nick67Commented:
@jimtpowers is right
Have a look at the estimated execution plan and see where the time is being spent.
Have a look at the fattest slice, and see if there may need to be indexes added.

You also have your summing going on inside your temptable.
You are therefore potentially throwing away some of the sums that you are doing.

What are these variables that you are using IN with?

and itembuyergroupid in (@itembuyergroupid)
and itemgroupid in (@itemgroupid)
and salesoriginid in (@salesoriginid)
and CustomerCode in (@Cust)
and dimension in (@Dept)
and dimension2_ in (@CC)
and dimension3_ in (@Purpose)

Are they arrays or temptables?  If they are single values you should be using =, not IN

Look to do your summing outside of a temptable, and after the data is pinned to its smallest possible set
The code may give you the idea--although you will most likely need to debug it.
-- a table variable to hold the values while they get eliminated by the margin calculation
Declare @Vartable1 table (
InvoiceID int IDENTITY(1,1) Primary Key, 
CustomerCode WhateverDataType,
CustName WhateverDataType,
dimension WhateverDataType,
dimension2_ WhateverDataType,
dimension3_ WhateverDataType,
LineAmountMST WhateverDataType,
linecostAmount WhateverDataType,
lineamountmst WhateverDataType,
linecostamount WhateverDataType

--fill the table variable
insert into @Vartable1 (
InvoiceID, 
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
LineAmountMST,
linecostAmount,
lineamountmst,
linecostamount)

select InvoiceID,CustomerCode,CustName,dimension,
dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamount
FROM  salesdim
where invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose 

--now get the final summing done outside the table variable
--and do the summing for MARGIN_PER inside a temptable, and eliminate the ones you don't need final summed


select InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT, MARGIN_PER

from (

select InvoiceID,CustomerCode,CustName,dimension,
dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamount,
case 
when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
else
sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 END MARGIN_PER

from @Vartable1
where MARGIN_PER between @marginFrom and @marginTo
)

GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
order by a.MARGIN_PER desc

Open in new window

0
 
AnnaJames77Author Commented:
Thank you all for your replies. I tried creating stored procedure and creating  temp table and summing up outside. But i cant find any difference in performance. The view is not indexed.
0
 
Roman GhermanSenior Software EngineerCommented:
Hi,

and if using temp table like this:
select 
		InvoiceID,
		CustomerCode,
		CustName,
		dimension,
		dimension2_,
		dimension3_,
		sum(LineAmountMST) SALES_AMOUNT,
		SUM(linecostAmount) COST_AMOUNT,
		sum(LineAmountMST- linecostAmount) GROSS_PROFIT
INTO #temp
FROM  
	salesdim
where 
	invoicedate between @startdate and @enddate
and itembuyergroupid  = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
	
select 
	a.InvoiceID,
	a.CustomerCode,
	a.CustName,
	a.dimension,
	a.dimension2_,
	a.dimension3_ ,
	a.SALES_AMOUNT,
	a.COST_AMOUNT,
	a.GROSS_PROFIT,
	case 
		when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
		when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
		when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
		else GROSS_PROFIT/LineAmountMST * 100 
	END MARGIN_PER
from 
	#temp a
where 
	a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc

DROP TABLE #temp

Open in new window

0
 
AnnaJames77Author Commented:
Hi roma, should i use a stored procedure for the query or can i use in the dataset of ssrs report
0
 
Roman GhermanSenior Software EngineerCommented:
You will have to use it in Stored Procedure
0
 
Anthony PerkinsCommented:
>>In total there are 752000 rows in the table. <<
That may explain why it is taking so long.
0
 
Nick67Commented:
@acperkins
Maybe.
We haven't asked what 'long' and 'slow' mean in this context :)

@AnnaJames77 hasn't said what looking at the estimated execution plan has told her.
If she has taken a couple different runs at the syntax, and gets the same performance, then the same underlying issue--perhaps a lack of an index--is underlying it all

Typically, having indexes on columns used in WHERE clauses can be helpful
itembuyergroupid
itemgroupid
salesoriginid
CustomerCode
dimension
dimension2_
dimension3_

Especially if there are text data types involved.
Look in the estimated execution plan for full table scans of the big table.
Do what you can to get those changed to an index scan
0
 
Anthony PerkinsCommented:
>>We haven't asked what 'long' and 'slow' mean in this context<<
Right, I was hoping that the author would get the hint.
0
 
IsildursHeirCommented:
If you want to get some help in finding out if there are missing indexes, you may try the DMVs - http://blogs.msdn.com/b/queryoptteam/archive/2006/04/06/570176.aspx
http://msdn.microsoft.com/en-us/library/ms345434.aspx
Haven't used in SQL 2008 - there might be more improvements to DMVs in there.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now