Subquery - SQL Server Forums

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

Subquery

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-08-17 : 17:02:20

I have the 2 queries below. They're basically the same except for the fact I tried to switch the subquery in the 1st query to be the main query in the 2nd and the main query in the 1st query to be the subquery in the 2nd. The 1st query runs in about 2 seconds and the 2nd query took over 50 minutes before I canceled it. Any thoughts or assistance as to why it's taking the 2nd query so long?

Thanks

--2 seconds run time

DECLARE @LYFirstDate DATETIME
DECLARE @LYLastDate DATETIME
DECLARE @CurrentYear varchar (4)
SET @LYFirstDate = '01/'+'01/'+ CAST(year(getdate())-1 as Varchar(4))
SET @LYLastDate = '12/'+'31/'+ CAST(year(getdate())-1 as Varchar(4))
SET @CurrentYear = year(getdate())

SELECT DISTINCT a.Cust, a.StnNum, convert(varchar(10), Min(a.DateShipped),101) as FirstDateShipped
FROM Shipments a
where a.Cust <> '' and YEAR(a.dateshipped) = @CurrentYear and a. PostedFlag = 'y'
and not exists
(SELECT DISTINCT b.Cust, b.StnNum
FROM Shipments b
WHERE (b.DateShipped between @LYFirstDate and @LYLastDate) and b. PostedFlag = 'y' and b.Cust <> ''
and (a.cust = b.Cust and a.stnnum = b.StnNum)
group by b.Cust, b.StnNum)
GROUP BY a.cust, a.StnNum
order BY a.cust, a.StnNum

--45+ minutes run time
DECLARE @LYFirstDate DATETIME
DECLARE @LYLastDate DATETIME
DECLARE @CurrentYear varchar (4)
SET @LYFirstDate = '01/'+'01/'+ CAST(year(getdate())-1 as Varchar(4))
SET @LYLastDate = '12/'+'31/'+ CAST(year(getdate())-1 as Varchar(4))
SET @CurrentYear = year(getdate())

SELECT DISTINCT b.Cust, b.StnNum, CONVERT(varchar(10), max(b.dateshipped),101) as LastDayShipped
FROM Shipments b
WHERE (b.DateShipped between @LYFirstDate and @LYLastDate) and b. PostedFlag = 'y' and b.Cust <> ''
and not exists
(SELECT DISTINCT a.Cust, a.StnNum
FROM Shipments a
where a.Cust <> '' and YEAR(a.dateshipped) = @CurrentYear and a. PostedFlag = 'y'
and (b.Cust = a.Cust and b.StnNum = a.StnNum)
group by a.Cust, a.StnNum)

group by b.Cust, b.StnNum
order BY b.Cust, b.StnNum

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-18 : 07:22:31

check the execution plans and you'll see the difference there...

also, you don't need to use distinct with group by as well, group by already ensures that the rows would be distinct from one another

also, exists (not exists), you don't need the overhead of grouping by if you only want to know existence...

YEAR(a.dateshipped) = @CurrentYear -- you can skip formatting...use datediff

--------------------
keeping it simple...

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-08-18 : 08:47:26

Where and how would I check the execution plans to notice a difference?

I will remove the distinct since I have group by and see how that works out.

@CurrentYear = 2010, not a time frame so I'm a little confused as to how I'd use the datediff function to determine 2010 because I thought the datediff function returns the time between two dates?

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-18 : 09:26:59

execution plan:
on your query window, click on the icon for execution plan, near the execute button or issue:
SET SHOWPLAN_ALL ON before the queries

for @currentyear
--I noticed you had to extract the year from getdate(), you can skip that:

datediff(yy,a.dateshipped,getdate())=0

--------------------
keeping it simple...

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-08-18 : 09:39:01

I tried to set that to ON, but don't have the authority to do so.

I removed the distinct function and it still took over 50 minutes.

I'll try the datediff you have above to see if that helps any.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-08-18 : 09:45:12

Functions on column names (eg YEAR(a.dateshipped) etc) are a bad idea as they stop the column being SARGable.
(Google this)

Your queries should look something like:


SELECT
	a.Cust, a.StnNum, convert(varchar(10), MIN(a.DateShipped),101) AS FirstDateShipped
FROM Shipments a
WHERE a.Cust <> ''
	AND a.PostedFlag = 'y'
	AND a.dateshipped >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
	AND a.dateshipped < DATEADD(year, DATEDIFF(year, 1, GETDATE()), 0)
	AND NOT EXISTS
	(
		SELECT *
		FROM Shipments b
		WHERE b.Cust = agresso.Cust
			AND b.stnnum = a.stnnum
			AND b.Cust <> ''
			AND b.PostedFlag = 'y'
			AND b.dateshipped >= DATEADD(year, DATEDIFF(year, 0, GETDATE()) - 1, 0)
			AND b.dateshipped < DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
	)
GROUP BY a.Cust, a.StnNum
ORDER BY cust, StnNum


SELECT
	a.Cust, a.StnNum, convert(varchar(10), MAX(a.DateShipped),101) AS LastDayShipped
FROM Shipments a
WHERE a.Cust <> ''
	AND a.PostedFlag = 'y'
	AND a.dateshipped >= DATEADD(year, DATEDIFF(year, 0, GETDATE()) - 1, 0)
	AND a.dateshipped < DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
	AND NOT EXISTS
	(
		SELECT *
		FROM Shipments b
		WHERE b.Cust = agresso.Cust
			AND b.stnnum = a.stnnum
			AND b.Cust <> ''
			AND b.PostedFlag = 'y'
			AND b.dateshipped >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
			AND b.dateshipped < DATEADD(year, DATEDIFF(year, 0, GETDATE()) + 1, 0)
	)
GROUP BY a.Cust, a.StnNum
ORDER BY cust, StnNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources