SQL Server Performance Forum – Threads Archive

Returning a list

What is the fastest way to do this: I have a student table, course table and enrolled courses<pre id="code"> Students ID Name Course ID Desc EnrolledCourses ID StudID CourseID</pre id="code"> To get all courses enrolled by the student:<pre id="code">SELECT cs.Name, c.Desc EnrolledCourse FROM EnrolledCourses ec JOIN Course c ON c.ID = ec.CourseID JOIN Students s ON s.ID = ec.StudID ORDER BY s.ID</pre id="code"> what i want is this: <pre id="code">SELECT [Name], fnGetEnrolledCourses([ID]) FROM Students that returns this Jones Math, Physics Sam Physics, Philosophy </pre id="code"> fnGetEnrolledCourses is a function that returns a string. The code behind this function is a simple query: <pre id="code">SELECT c.Desc FROM Courses c JOIN EnrolledCourses ec ON ec.CourseID = c.ID WHERE ec.StudID = @StudID</pre id="code"> and I loop through the result set using a cursor. I need a better way to do this as it takes the query to finish executing for 26-32 seconds for just 180rows [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]

Do you need something like this?
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
Madhivanan Failing to plan is Planning to fail

Use subqueries or co-corelatd sub query and try….this wll give faster result set…
SURYA

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by JusticeLeague I need a better way to do this as it takes the query to finish executing for 26-32 seconds for just 180rows [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> Umh, even if there aren’t any indexes on your tables, the query shouldn’t need a that long time to finish for just 180 rows. Or is this a typo? – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Frank, original poster is using a cursor … Use a function like this: CREATE FUNCTION fnGetEnrolledCourses (@StudId INT)
RETURNS VARCHAR(8000)
AS BEGIN SET NOCOUNT ON
DECLARE @ReturnString VARCHAR(8000)
SET @ReturnString = ” SELECT @ReturnString = @ReturnString
+ CASE WHEN LEN(@ReturnString) = 0 THEN ” ELSE CHAR(13) + CHAR(10) END
+ c.Desc
FROM Courses c JOIN EnrolledCourses ec ON ec.CourseID = c.ID
WHERE ec.StudID = @StudID RETURN (@ReturnString) END

Sorry that this is a late reply. I’m still having issues with this solution. I tried the suggested solution but the length of execution time did not change. Using a cursor and the sugested query gives a result w/same length of execution time (always 18sec/180 rows). Does this mean that this is the best time I could get using this approach? So I really have to revert to formatting on the UI side?

Hi ya, We’re assuming that you have:
– a clustered index on Course( ID )
– a non-clustered index on EnrolledCourses( StudID, CourseID ) how much data is in each table, because as Frank says this is an amazingly long amount of time to get back 180 rows…? in any case this will always be faster to do in the UI, and more flexible since the description could in theory contains CR/LF itself which makes it impossible to exact each individual description Cheers
Twan

JusticeLeague, You’re not very clear – are you still using the cursor-based approach? Then try the solution that is in the last post before your latest reply. Apart from that, like Twan is suggesting, check the indexes – not the proper indexes, then you won’t get proper performance!

Read the link I specified Madhivanan Failing to plan is Planning to fail

]]>