Solved

SQL SubQuery Slow

Posted on 2014-10-28
6
131 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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

13 Experts available now in Live!

Get 1:1 Help Now