SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Temp vs. CTE vs Variable table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vj12345kap
Starting Member

USA
2 Posts

Posted - 11/28/2012 :  16:24:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/28/2012 :  16:51:27  Show Profile  Reply with Quote
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

USA
2 Posts

Posted - 11/28/2012 :  17:06:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/28/2012 :  17:13:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/28/2012 :  18:55:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/29/2012 :  11:31:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000