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
 Temp vs. CTE vs Variable table

Author  Topic 

vj12345kap
Starting Member

2 Posts

Posted - 2012-11-28 : 16:24:47
I have about 5 million rows and I'm trying to figure out the best way to write the following query.

CTE:

WITH CLA_cte (syStudentID ,CourseCode ,LetterGrade ,NumericGrade, AdTermID, AdEnrollID)
AS
(
Select
syStudentId
,CourseCode
,LetterGrade
,NumericGrade
,AdTermId
,AdEnrollID
from CLAData
Where AdTermID IN (492633)

)

SELECT
cc.CourseCode
,cc.LetterGrade
,cc.NumericGrade
,c.OutComeScore
FROM CLAData c
,CLA_cte cc
Where
c.AdTermID = cc.AdTermId
and c.AdEnrollID = cc.AdEnrollID
and c.syStudentID = cc.syStudentID

or
TEMP
Select
syStudentId
,CourseCode
,LetterGrade
,NumericGrade
,AdTermId
,AdEnrollID
into #CLA_cte
from CLAData
Where AdTermID IN (492633)



SELECT
cc.CourseCode
,cc.LetterGrade
,cc.NumericGrade
,c.OutComeScore
FROM CLAData c
,#CLA_cte cc
Where
c.AdTermID = cc.AdTermId
and c.AdEnrollID = cc.AdEnrollID
and c.syStudentID = cc.syStudentID


drop table #CLA_cte

Or Declared Variable Table:

declare @CLA_cte table(
syStudentID int
,CourseCode nvarchar(15)
,LetterGrade nvarchar(15)
,NumericGrade numeric(8,2)
,AdTermId int
,AdEnrollID int
);

Insert Into @CLA_cte ( syStudentID ,CourseCode ,LetterGrade ,NumericGrade ,AdTermId, AdEnrollID )
Select
syStudentId
,CourseCode
,LetterGrade
,NumericGrade
,AdTermId
,AdEnrollID
from CLAData
Where AdTermID IN (492633)


SELECT
cc.CourseCode
,cc.LetterGrade
,cc.NumericGrade
,c.OutComeScore
FROM CLAData c
,@CLA_cte cc
Where
c.AdTermID = cc.AdTermId
and c.AdEnrollID = cc.AdEnrollID
and c.syStudentID = cc.syStudentID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-28 : 16:51:27
I suspect that there are better solution than any that you have posted. Can you describe what you are trying to do? Why do you need to do a join? Can you simply just select the columns you need and apply your filter or are you trying to do an OUTER JOIN (hard to tell without ansi-style syntax)?
Go to Top of Page

vj12345kap
Starting Member

2 Posts

Posted - 2012-11-28 : 17:06:48
Sorry, the purpose of the question was to see which method is faster:
- Declared tables
- Temp tables
- CTE

The query syntax that I used isn't that important. Right now, our approach is to just use Temp tables and we're trying to examine other approaches.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-28 : 17:13:56
I would do with temp table with indexes for 5 million rows.Or try to avoid if you can. Make sure tempdb has space.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-28 : 18:55:20
Again, without more details it's hard to say. The right answer is: It depends. Since you are only talking 5M rows, I'm not sure it really matters much. But, what affect does applying the predicate do (Where AdTermID IN (492633))? Does that filter the 5M down to 2 rows, 1M rows or??

There are a lot of variables to perfornace tuning. I'd test various methods and see what comes out the other side. I tend to avoid temp tables, as I'm rather adverse to generating extra IO, unless they actually help performance. However, as I said, it depends... :)
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-29 : 11:31:00
Just because it is possible to accomplish the same results for a given task using 3 different techniques or tools doesn't mean that those tools should be compared with each other. Only when you have a specific statement that you want to optimize can you then directly compare different options. You should learn how to look at and understand the execution plans, look at IO duration, and READs statitstics to answer the question for yourself. But understand that those stats only apply to that statement and not necessarily the tool you used.

for instance, for the purpose of finding rows in one table that don't exist in another LEFT OUTER JOIN WHERE NULL may outperform a WHERE NOT EXISTS. But that doesn't mean that WHERE NOT EXISTS should be discarded. There are plenty of different uses for that in other applications. The same for CTEs, #TempTables, and tableVariables. Three very different tools each with their own benefits and drawbacks.

The best solution usually the simplest. And in your example the simplest solution looks like just a straight select out of the table.


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -