Solved

Slow Query

Posted on 2011-03-22
16
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +5
16 Comments
 
LVL 18

Assisted Solution

by:deighton
deighton earned 100 total points
ID: 35189065
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
ID: 35189215
In total there are 752000 rows in the table.
0
 
LVL 9

Accepted Solution

by:
Roman Gherman earned 200 total points
ID: 35189230
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Expert Comment

by:IsildursHeir
ID: 35189319
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
ID: 35189405
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
ID: 35189458
Hey rushShah,

Isn't that the same as my solution?

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

Expert Comment

by:jimtpowers
ID: 35190595
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
ID: 35192047
@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
 

Author Comment

by:AnnaJames77
ID: 35197013
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
ID: 35197093
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
ID: 35197289
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
ID: 35197741
You will have to use it in Stored Procedure
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35198972
>>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
ID: 35199533
@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
ID: 35200481
>>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
ID: 35200719
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

717 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