eliminating the use of temp tables with derived tables

SQL Apprentice Question
I am trying to rewrite my procedure to use a derived table instead of temp
table to test if this increases performance.

I was using this article to help but I receive a error message where I am
using a fuction to build my parameter list. Could someone review my current
proc to provide a suggestion in using a derived table instead of using a temp
table?


CREATE TABLE #Metrolist (metroid varchar(100))


INSERT #Metrolist
SELECT * FROM db.rcudf_ConvertMetroTabletoList(@metroidlist)


BEGIN TRAN


SELECT


CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day,
0,r.datecreated) ,0)
WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0,
r.datecreated),-1)
WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month,
0,r.datecreated),0)
WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year,
0,r.datecreated), 0)
END as 'DATE',
Count(*) as 'Leads',
m.metroname, m.metroid


FROM rentclicks.rcadreplies as r
JOIN db.rcads as a on a.adid = r.adid
JOIN db.rczipcode AS z ON z.zipcode = a.zip
JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid
JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid
JOIN #Metrolist as ml on ml.metroid = m.metroid


WHERE r.datecreated >= @startdate AND r.datecreated < DATEADD(dd, 1,
@enddate)


GROUP BY CASE WHEN @groupingtimeframe = 'daily' THEN
DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0)
WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0,
r.datecreated),-1)
WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month,
0,r.datecreated),0)
WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year,
0,r.datecreated), 0)
END, m.metroname, m.metroid


ORDER BY CASE WHEN @groupingtimeframe = 'daily' THEN
DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0)
WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0,
r.datecreated),-1)
WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month,
0,r.datecreated),0)
WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year,
0,r.datecreated), 0)
END ASC




Celko Answers
>> I am trying to rewrite my procedure to use a derived table instead of temp table to test if this increases performance. <<


Worry about the things that REALLY matter in in the software life cycle
of a successful project; maintainability is first (80%+ of the cost of
software is here), portability (15%+ of the cost is here) and
performance shows up as a cost factor only when it really stinks and it
is usually the easiest (i.e. cheapest) one to fix.

Oh, you might want to learn Standard SQL. The syntax is INSERT INTO,
not the abbreviated INSERT, like a hillbilly dropping words in his
sentences. We do not use single around a column alias.


Good SQL programmers do not use Sybase/SQL Server style temp tables but
write with derived tables, VIEWs and CTEs. And would **never** use a
function call if we did use a temp table. What that says is that this
programmer does not understand the basic idea of a declarative
language.


What is the difference berween a zip and a zip_code? Two names means
two TOTALLY DIFFERENT DATA ELEMENTS. But it also means that you did
not build a data dictionary for the enterprise, doesn't it?


Finally, you did a lot of proprietary temporal math to make sure that
your query cannot use indexes. Build a calendar table with the proper
date ranges:


CREATE TABLE ReportRanges
(cal_date DATETIME NOT NULL PRIMARY KEY, --, start date
day_end_date DATETIME NOT NULL,
week_end_date DATETIME NOT NULL,
month_end_date DATETIME NOT NULL,
year_end_date DATETIME NOT NULL);


Now use a predicate like this:


{@my_period BETWEEN cal_date
AND CASE @report_period
WHEN 'dailey' THEN day_end_date
WHEN 'weekly' THEN week_end_date
WHEN 'monthly' THEN month_end_date
WHEN ''annual' THEN year_end_date
ELSE NULL END


SQL is a declarative language that is designed for data. It is not a
procedural language the way you are trying to use it. You are trying
to talk English with Japanse grammar rules :)