Solved

Slow Query

Posted on 2011-03-22
16
228 Views
Last Modified: 2012-06-27
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
Comment
Question by:AnnaJames77
  • 4
  • 3
  • 2
  • +5
16 Comments
 
LVL 18

Assisted Solution

by:deighton
deighton earned 100 total points
Comment Utility
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
 

Author Comment

by:AnnaJames77
Comment Utility
In total there are 752000 rows in the table.
0
 
LVL 9

Accepted Solution

by:
Roman Gherman earned 200 total points
Comment Utility
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
 

Expert Comment

by:IsildursHeir
Comment Utility
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
 
LVL 8

Expert Comment

by:rushShah
Comment Utility
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
 
LVL 9

Expert Comment

by:Roman Gherman
Comment Utility
Hey rushShah,

Isn't that the same as my solution?

You just added select * from (....)
0
 
LVL 4

Expert Comment

by:jimtpowers
Comment Utility
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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 200 total points
Comment Utility
@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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:AnnaJames77
Comment Utility
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
 
LVL 9

Assisted Solution

by:Roman Gherman
Roman Gherman earned 200 total points
Comment Utility
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
 

Author Comment

by:AnnaJames77
Comment Utility
Hi roma, should i use a stored procedure for the query or can i use in the dataset of ssrs report
0
 
LVL 9

Expert Comment

by:Roman Gherman
Comment Utility
You will have to use it in Stored Procedure
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>In total there are 752000 rows in the table. <<
That may explain why it is taking so long.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 200 total points
Comment Utility
@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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>We haven't asked what 'long' and 'slow' mean in this context<<
Right, I was hoping that the author would get the hint.
0
 

Expert Comment

by:IsildursHeir
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now