Solved

SQL SubQuery Slow

Posted on 2014-10-28
6
142 Views
Last Modified: 2014-11-04
I have a transactional database table.  I would like to retrieve data for current years and then previous years.  The query itself takes 2 seconds to run returning about 25,000 rows.  Issue is that when I created a subquery from same table for previous year and so on, it gets really slow.  My 2 seconds turn into a minute, and then 5 minutes, and then so on and so on.

Is there a better way to make it faster?

Here's what my sql query code looks like.
SELECT	DISTINCT IT.ITEM_ID, A.AVG_COST_1, B.AVG_COST_2, C.AVG_COST_3
FROM	INVENTORY_TRANSACTION IT

		LEFT OUTER JOIN
		(
		SELECT	IT.ITEM_ID, AVG(IT.COST) AVG_COST_1
		FROM	INVENTORY_TRANSACTION IT
		WHERE	DATEPART(YEAR, CREATE_DATE) = DATEPART(YEAR, GETDATE())
		GROUP BY IT.ITEM_ID
		) A ON A.ITEM_ID = IT.ITEM_ID
		
		LEFT OUTER JOIN
		(
		SELECT	IT.ITEM_ID, AVG(IT.COST) AVG_COST_2
		FROM	INVENTORY_TRANSACTION IT
		WHERE	DATEPART(YEAR, CREATE_DATE) = DATEPART(YEAR, GETDATE()) -1
		GROUP BY IT.ITEM_ID
		) B ON B.ITEM_ID = IT.ITEM_ID
		
		LEFT OUTER JOIN
		(
		SELECT	IT.ITEM_ID, AVG(IT.COST) AVG_COST_3
		FROM	INVENTORY_TRANSACTION IT
		WHERE	DATEPART(YEAR, CREATE_DATE) = DATEPART(YEAR, GETDATE()) -2
		GROUP BY IT.ITEM_ID
		) C ON C.ITEM_ID = IT.ITEM_ID

WHERE	DATEPART(YEAR, CREATE_DATE) = DATEPART(YEAR, GETDATE())
ORDER BY IT.ITEM_ID

Open in new window


Here's my expected result:
example
0
Comment
Question by:holemania
[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
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408442
Can you confirm which version of SQL Server you are on please.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408468
How about this:

With MyTable as
(select Item_ID, DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, CREATE_DATE) as myYearDiff, Avg(Cost) as AvgCost
from Inventory_Transaction
group by Item_ID, DATEPART(YEAR, CREATE_DATE)),
myPvt as
(Select *
from MyTable
pivot (sum(AvgCost) for myYearDiff in ([0], [1], [2])) as MyP)
select Item_ID, [0] as ThisYear, [1] as LastYear, [2] as YearBeforeLast
from myPvt

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40408474
You can create a View and use the View in the SELECT.
CREATE VIEW AverageCost AS 
		SELECT	ITEM_ID, DATEPART(YEAR, CREATE_DATE) ITEM_YEAR, AVG(COST) AVG_COST
		FROM	INVENTORY_TRANSACTION
		GROUP BY ITEM_ID, DATEPART(YEAR, CREATE_DATE)

SELECT	IT.ITEM_ID, A.AVG_COST_1, B.AVG_COST_2, C.AVG_COST_3
FROM	INVENTORY_TRANSACTION IT
		LEFT OUTER JOIN
		(
		SELECT	ITEM_ID, AVG_COST AVG_COST_1
		FROM	AverageCost
		WHERE	ITEM_YEAR = DATEPART(YEAR, GETDATE())
		) A ON A.ITEM_ID = IT.ITEM_ID
		
		LEFT OUTER JOIN
		(
		SELECT	ITEM_ID, AVG_COST AVG_COST_2
		FROM	AverageCost
		WHERE	ITEM_YEAR = DATEPART(YEAR, GETDATE())-1
		) B ON B.ITEM_ID = IT.ITEM_ID
		
		LEFT OUTER JOIN
		(
		SELECT	ITEM_ID, AVG_COST AVG_COST_2
		FROM	AverageCost
		WHERE	ITEM_YEAR = DATEPART(YEAR, GETDATE())-2
		) C ON C.ITEM_ID = IT.ITEM_ID

WHERE	DATEPART(YEAR, CREATE_DATE) = DATEPART(YEAR, GETDATE())
ORDER BY IT.ITEM_ID

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40408704
To gain efficiency:
1) don't use a function on the table column
2) get all years at once, rather than using a separate query for each year
SELECT  ITEM_ID,
            MAX(CASE WHEN ITEM_YEAR = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) THEN AVG_COST END) AS AVG_COST_1,
            MAX(CASE WHEN ITEM_YEAR = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) THEN AVG_COST END) AS AVG_COST_2,
            MAX(CASE WHEN ITEM_YEAR = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0) THEN AVG_COST END) AS AVG_COST_3            
FROM (
		SELECT	IT.ITEM_ID, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS ITEM_YEAR, AVG(IT.COST) AVG_COST
		FROM	INVENTORY_TRANSACTION IT
		WHERE	IT.CREATE_DATE >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0) AND
		        IT.CREATE_DATE < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
		GROUP BY IT.ITEM_ID, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
) AS subquery1
GROUP BY ITEM_ID

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40409981
and
3) ensure that INVENTORY_TRANSACTION.CREATE_DATE is indexed

If you adopt Scott Pletcher's suggested query, and then still have performance issues, please
a. identify what indexes do exist on the table
b. prepare an execution plan and attach the .sqlplan file here
0
 

Author Closing Comment

by:holemania
ID: 40422524
Thank you.  That worked very well and took only seconds to run.
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
Union & Crosstab qrys 101! 6 70
sql update 2 45
removing unwanted rows from an sql server ranked table 13 39
Access 2003 query, remove dupes 17 20
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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