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
 Better Way to Write This?

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 b
GROUP 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
Go to Top of Page

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)
Go to Top of Page

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)


Go to Top of Page

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?
Go to Top of Page

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 Courses
FROM
(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);
Go to Top of Page
   

- Advertisement -