Query containing a Subquery taking too long....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query containing a Subquery taking too long....

Author

Topic

davdatong
Starting Member

4 Posts

Posted - 2012-03-30 : 04:21:08

Hi everyone. First post.
I have a slight problem. At the moment I am trying to get the total order Values from a table in the next 30 days and the next 60 days from the current date. All this data is contained in one view in the SQL server.

My query is as follows:

Select
orders.CustomerID_Long,
sum (orders.ordervalue)as ap_orderbook30days,
orders.currency,

(Select
sum (orders2.ordervalue)
FROM [Sales].[dbo].[V_Export_CRM_OpenOrders] as orders2
WHERE
(orders2.dayid BETWEEN GETDATE()AND GETDATE() +60
and orders2.CustomerID_Long = orders.CustomerID_Long))as ap_orderbook60days

FROM [Sales].[dbo].[V_Export_CRM_OpenOrders] as orders
WHERE
orders.dayid BETWEEN GETDATE()AND GETDATE() +30

and ((orders.CustomerID_Long like '001%')
or (orders.CustomerID_Long like '004%')
or (orders.CustomerID_Long like '020%')
or (orders.CustomerID_Long like '022%'))

group by orders.CustomerID_Long, orders.currency
order by orders.CustomerID_Long

This query is bringing the information I need correctly. However the problem is it is taking 6 minutes to execute. :(
It is only bringing in 1021 rows as expected.

When I remove this "orders2.CustomerID_Long = orders.CustomerID_Long))as ap_orderbook60days"

The query takes 5 seconds to finish but doesnt bring in the correct details. Where am I going wrong?

rajarajan
Starting Member

48 Posts

Posted - 2012-03-30 : 04:38:30

You Can re write like this , 1st write 30 days logic and have it in #temp1 and 60 days in #temp2

using the temp table re write the qeury
Avoid using group by function in sub query,if u want to perform some logic execute the logic first and insert into table and use the table in the subquery part
With 30days as
()
;
60days as
()

Select * from 30 days , 60 days

davdatong
Starting Member

4 Posts

Posted - 2012-03-30 : 04:49:10

Is there a way to make my query run faster without the use of temp tables?
At the moment I only have access to the Created View and don't have permission to create tables.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-30 : 05:01:49

Try:

Select
orders.CustomerID_Long,
sum (case  when orders.dayid BETWEEN GETDATE()AND GETDATE() +30 then orders.ordervalue else 0.0 end) as ap_orderbook30days,
sum (case  when orders.dayid BETWEEN GETDATE()AND GETDATE() +60 then orders.ordervalue else 0.0 end) as ap_orderbook60days,
orders.currency,

FROM [Sales].[dbo].[V_Export_CRM_OpenOrders] as orders
WHERE
orders.dayid BETWEEN GETDATE()AND GETDATE() +60

and ((orders.CustomerID_Long like '001%')
or (orders.CustomerID_Long like '004%')
or (orders.CustomerID_Long like '020%')
or (orders.CustomerID_Long like '022%'))

group by orders.CustomerID_Long, orders.currency
order by orders.CustomerID_Long

No, you're never too old to Yak'n'Roll if you're too young to die.

rajarajan
Starting Member

48 Posts

Posted - 2012-03-30 : 05:05:14

Try with the Sample

---Sample One
with temp as

(select GETDATE () as date )

select * from temp

--Drop table temp

-- Sample 2 --

select GETDATE () As date into #tmp

select * from #tmp

Drop table #tmp

davdatong
Starting Member

4 Posts

Posted - 2012-03-30 : 05:36:00

Thank you Webfred! From 6 minutes to 2 seconds!
Many Thanks again!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-30 : 05:40:38

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-30 : 07:57:25

Future versions of SQL Server doesn't like "GETDATE() + n" arithmethic. Get used to formulate real date calculations.

DECLARE	@Today DATETIME = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'),
	@Day30 DATETIME,
	@Day60 DATETIME

SELECT	@Day30 = DATEADD(DAY, 30, @Today),
	@Day60 = DATEADD(DAY, 60, @Today)

SELECT		CustomerID_Long,
		Currency,
		SUM(CASE WHEN DayID < @Day30 THEN OrderValue ELSE 0 END) AS Orderbook30days,
		SUM(OrderValue) AS Orderbook60days,
FROM		Sales.dbo.V_Export_CRM_OpenOrders
WHERE		DayID >= @Today
		AND DayID < @Day60
		AND (
			CustomerID_Long LIKE '001%'
			OR CustomerID_Long LIKE '004%'
			OR CustomerID_Long LIKE '020%'
			OR CustomerID_Long LIKE '022%'
		)
GROUP BY	CustomerID_Long,
		Currency
ORDER BY	CustomerID_Long

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-30 : 09:14:18

LIKE!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources