Comment On It seems my app is running a little slow ...

Quite a few users were complaining about application performance so Keith investigated the problem. He came across this in a stored procedure that pointed him in the right direction  ...

set @strSQL = 'SELECT Count(*) FROM tblAnswers where QuestionText =
''1. Did any associate verbally acknowledge you within 30 seconds
after you entered the store?'''
EXEC @iQ1Total = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''Yes'' and QuestionText = ''1. Did any associate verbally
acknowledge you within 30 seconds after you entered the store?'''
EXEC @iQ1Yes = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''No'' and QuestionText = ''1. Did any associate verbally
acknowledge you within 30 seconds after you entered the store?'''
EXEC @iQ1No = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where QuestionText =
''2. How long did you wait to be assisted from the time you entered
the store? Mark one only:'''
EXEC @iQ2Total = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''a. Immediately (0:00-0:59)'' and QuestionText = ''2. How long did
you wait to be assisted from the time you entered the store? Mark
one only:'''
EXEC @iQ2a = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''b. 1-5 mins (1:00-5:00)'' and QuestionText = ''2. How long did you
wait to be assisted from the time you entered the store? Mark one
only:'''
EXEC @iQ2b = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''c. 15-30 mins (15:01-30:00)'' and QuestionText = ''2. How long did
you wait to be assisted from the time you entered the store? Mark
one only:'''
EXEC @iQ2c = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''d. 30+ mins (30:01 or longer)'' and QuestionText = ''2. How long
did you wait to be assisted from the time you entered the store?
Mark one only:'''
EXEC @iQ2d = @strSQL
 
set @strSQL = 'SELECT Count(*) FROM tblAnswers where QuestionText =
''3. Throughout the interaction, was the associate friendly (not
rude, abrupt, disinterested)?'''
EXEC @iQ3Total = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''Yes'' and QuestionText = ''3. Throughout the interaction, was the
associate friendly (not rude, abrupt, disinterested)?'''
EXEC @iQ3Yes = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''No'' and QuestionText = ''3. Throughout the interaction, was the
associate friendly (not rude, abrupt, disinterested)?'''
EXEC @iQ3No = @strSQL

<<SQL for questions 4 to 49 snipped... (Just use your imagination)>>


set @strSQL = 'SELECT Count(*) FROM tblAnswers where QuestionText =
''10. Was the store''s exterior and parking lot clean and well
maintained?'''
EXEC @iQ50Total = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''Yes'' and QuestionText = ''10. Was the store''s exterior and
parking lot clean and well maintained?'''
EXEC @iQ50Yes = @strSQL
set @strSQL = 'SELECT Count(*) FROM tblAnswers where AnswerText =
''No'' and QuestionText = ''10. Was the store''s exterior and parking
lot clean and well maintained?'''
EXEC @iQ50No = @strSQL

Naturally, all of the variables were selected into a temp table and selected back as a result set. As for the bonus punchline, the questions were displayed as ...

lblQ1.text = "1. Did any associate verbally acknowledge you within 30 seconds after you entered the store?"
lblQ1Yes.text = "Yes: " & rs("Q1yes")
lblQ1No.text = "No: " & rs("Q1no")