Solved

Slow Query

Posted on 2011-03-22
16
232 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to share SSIS Package? 6 35
Webservices in T-SQL 3 30
Text file into sql server 5 21
Show Results for Latest DateTime in a View 27 24
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

815 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

11 Experts available now in Live!

Get 1:1 Help Now