Solved

SQL SubQuery Slow

Posted on 2014-10-28
6
136 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
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 46

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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