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 |
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2013-02-22 : 13:42:58
|
Hi Guys,I have this query that does the job, but it is too slow.. I have the correct indexes...Is there a way to make this query little faster:SELECT b.STUDENT_ID, COURSES = STUFF ( ( SELECT ','+ rtrim(a.COURSE) FROM STUDENT_COURSE a WHERE a.STUDENT_ID = b.STUDENT_ID FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))FROM STUDENT_COURSE bGROUP BY b.STUDENT_ID It pulls a comma separated list of courses for each student_id.Please suggest.Thanks,Laura |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-22 : 14:41:48
|
Can you see if this is any faster?SELECT b.STUDENT_ID, STUFF ( ( SELECT ','+ rtrim(a.COURSE) FROM STUDENT_COURSE a WHERE a.STUDENT_ID = b.STUDENT_ID FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))FROM (SELECT DISTINCT STUDENT_ID FROM STUDENT_COURSE) AS b |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-02-22 : 15:15:47
|
Is the STUDENT_ID column indexed in STUDENT_COURSE? What does the execution plan show?My guess is this index may speed it up:CREATE INDEX ix_student_id_inc_course ON STUDENT_COURSE(STUDENT_ID) INCLUDE(COURSE) |
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2013-02-22 : 15:42:39
|
Thanks Rob I added that index. Surprisingly that index slowed down the query further. Probably has to do with something else. I will check.quote: Originally posted by robvolk Is the STUDENT_ID column indexed in STUDENT_COURSE? What does the execution plan show?My guess is this index may speed it up:CREATE INDEX ix_student_id_inc_course ON STUDENT_COURSE(STUDENT_ID) INCLUDE(COURSE)
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-22 : 16:10:33
|
Can you run the following query?select count(*) as a, count(distinct student_id) as b from STUDENT_COURSE The code I posted should have speeded up the query by approximately by a factor of a/b.Also, surprising that Rob's suggestion slowed it down. What do the query plans show with and without the index? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-22 : 16:14:11
|
Also, can you try this? Logically the same, but not sure if the query plan would be different:SELECT b.STUDENT_ID, STUFF(a.CoursesXML.value('.','VARCHAR(256)'),1,1,'') AS CoursesFROM (SELECT DISTINCT STUDENT_ID FROM STUDENT_COURSE) AS b CROSS APPLY ( SELECT ',' + RTRIM(a.COURSE) FROM STUDENT_COURSE a WHERE a.STUDENT_ID = b.STUDENT_ID FOR XML PATH(''),TYPE ) AS a(CoursesXML); |
|
|
|
|
|
|
|