left outer join - SLOW - 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

SQL Server 2005 Forums

Transact-SQL (2005)

left outer join - SLOW

Author

Topic

foupons
Starting Member

1 Post

Posted - 2007-06-11 : 18:03:39

I want to do a pull that will get me the count of clicks for a link for each of the last seven days.

The problem is that not every day has a click, and I want each day to have a row even if it has 0 clicks. I can manage this if I create a table containing each day then left outer. But this is very slow compared to the inner join on the dates.

Is there an Over() or outer apply that is faster? What is the best/fastest way to do this?

the restule table should be

DAY Clicks
7/5 10
7/6 0
7/7 65
7/8 12
7/9 0
7/10 45
7/11 27

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-11 : 19:11:51

One that comes to mind, is that you could run yuor query to get your counts, then left join to that result. SOmething like:

SELECT 
	temp.Date
	COALESCE(derived.Clicks, 0) AS Clicks
FROM
	#Temp temp -- all the days you care about
LEFT OUTER
	(
		SELECT
			Date,
			SUM(Clicks)
		FROM
			MyTable
		WHERE
			Date BETWEEN <StartDate> AND <EndDate>
	) derived
	ON
	temp.Date = derived.Date
ORDER BY
	temp.Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources