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.
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 CLADataWhere AdTermID IN (492633))SELECT cc.CourseCode ,cc.LetterGrade ,cc.NumericGrade ,c.OutComeScoreFROM CLAData c,CLA_cte ccWhere c.AdTermID = cc.AdTermId and c.AdEnrollID = cc.AdEnrollID and c.syStudentID = cc.syStudentIDorTEMPSelect syStudentId ,CourseCode ,LetterGrade ,NumericGrade ,AdTermId ,AdEnrollIDinto #CLA_cte from CLADataWhere AdTermID IN (492633)SELECT cc.CourseCode ,cc.LetterGrade ,cc.NumericGrade ,c.OutComeScoreFROM CLAData c,#CLA_cte ccWhere c.AdTermID = cc.AdTermId and c.AdEnrollID = cc.AdEnrollID and c.syStudentID = cc.syStudentIDdrop table #CLA_cteOr 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 CLADataWhere AdTermID IN (492633)SELECT cc.CourseCode ,cc.LetterGrade ,cc.NumericGrade ,c.OutComeScoreFROM CLAData c,@CLA_cte ccWhere 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)? |
|
|
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- CTEThe 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. |
|
|
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. |
|
|
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... :) |
|
|
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 OptimizerTG |
|
|
|
|
|
|
|