Performance Required - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Performance Required

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-28 : 06:50:59

Performance Issue

I have a table that has students and another table that has total number of questions and their answers given by the students. But the data is in vertical form i.e.

Student Question Answer
A 1 ABC
A 2 DEF
A 3 GHI

B 1 DEF
B 2 ABC
B 3 GHI

Now I want the out as

Student [1] [2] [3]
A ABC DEF GHI
B DEF ABC GHI

And there are 300,000 rows.

Approach 1: I looped (cursor) though the distinct questions and created the table containing all questions as columns and one column for student. I then insert all the distinct students into this table. Opened loop on students and in inner loop got all answers for questions given by student and made the dynamic update string and then executed it against this student. But this is taking 6 hours for 300,000 rows.

Approach 2:
select distinct a.student_id,
[1] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 1),
[2] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 2),
[3] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 3),
[4] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 4),
[5] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 5),
[6] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 6),
[7] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 7),

= (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 8),
[9] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 9),
[10] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 10),
[11] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 11),
[12] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 12),
[13] = (select top 1 isnull(answer, '') from Studentanswers where student_id = a.student_id
and question_id = 13)
from Student s join Studentanswers a on s.student_id = a.student_id
where a.quiz_id = 1 and
a.quiz_time between '2000-01-11 03:34:37.220' and '2006-01-11 03:34:37.220'

and this is still taking too much time.

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-28 : 07:28:57

Declare @Answers VarChar(8000)
Select @Answers = ''
Select @Answers = Answer + ',' + @Answers From YourTableName Where student = 'A'
IF len(@Answers) > 2
BEGIN
Select @Answers = Left(@Answers,len(@Answers) -1)
END
Print @Answers

Insted of ',' you can space(1) as as separator.
if you want to execute this by using select statement then you have to create function for this which will return the space separated value for each student.
or try with group by student
Hope this will work for you.

Surendra

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-28 : 07:35:03

Did read the required output. How can I show the answers in column in this way? If the answers are comma or space delimited.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-28 : 07:45:52

Just search for crosstabs, or start here:
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-28 : 08:24:46

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-28 : 08:33:59

quote:
Originally posted by bridge

Did read the required output. How can I show the answers in column in this way? If the answers are comma or space delimited.

Declare @Str1 nvarchar(1000)
Declare @cnt int
Declare @MaxCol int
--Select @MaxCol = count(*) from YourTableName Where student = 'A'
-- or you can take max...
Select @MaxCol = 11 --or you can keep it as fix
Select @cnt = 1
Select @str1 = 'Create table #temp ('
While @cnt <= @MaxCol
Begin
Select @str1 = @str1 + '[' + ltrim(str(@cnt)) + '] varchar(50),'
Select @cnt = @cnt + 1
End
IF len(@str1) > 2
BEGIN
Select @str1 = Left(@str1,len(@str1) -1) + ')'
END
Print @str1
Exec sp_executesql @str1
-----------------------------------------
Declare @Answers VarChar(8000)
Select @Answers = ''
Select @Answers = Answer + ',' + @Answers From YourTableName Where student = 'A'
IF len(@Answers) > 2
BEGIN
Select @Answers = '''' + Left(@Answers,len(@Answers) -2)
END
Print @Answers
-----------------------------------------
Declare @Str1 varchar(1000)
Declare @cnt int
Select @str1 = 'Insert into #temp values(' + @answers + ')'
Print @str1
Exec (@str1)
-----------------------------------------
select * from #temp
--drop table #temp

--------------------------------------------------------------
Try this.... To insert all the rows at once create dynamic insert statement and use fucntion to return for all the users.
As per your requirement you can create a dynamic table.

Surendra

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-28 : 08:48:15

Sorry.....
Instead of this line
Select @Answers = Answer + ',' + @Answers From YourTableName Where
Use
Select @Answers = Answer + ''',''' + @Answers From YourTableName Where student = 'A'

Surendra

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-28 : 09:26:07

>>As per your requirement you can create a dynamic table.

No need

Read the Link I specified

Madhivanan

Failing to plan is Planning to fail

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-29 : 01:35:45

Yes Madhivanan,
I suggested him almost the same logic but in different way..... but let bridge decide, which is suitable for him. :-)

Surendra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources