Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL SubQuery Slow

Posted on 2014-10-28
6
Medium Priority
?
145 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 52

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
Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 49

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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