Want to optimise my asp database queries - how many queries are too many?

I am building an ASP application to help students learn another language. The application contains 47 exercises. Each exercise is almost identical in terms of question structure. There are three multiple choice questions per page, but each question can have a differing number of choices. therefore, to build the pages dynamically using one ASP template, I have to make three database calls, one for each question.

A student can also save his answers, and I have to retrieve them, that makes three more database requests.

On this page, I also have three other database requests for different reasons.

That makes a total of 9 database SELECT statements each time an exercise is loaded.

(Once the selects are done, the recordsets are loaded into arrays and the recordset is closed.)

My question:
========
Is that too many database SELECT statements per page considering that hundreds of people will be using the application simultaneously? Is that a big server load??

LVL 1
vanaudenAsked:
Who is Participating?
 
arbertCommented:
"Well again, I would need to use a DO WHILE loop to run throught the resulting recordset of about 15 records... Is that significantly more server intensive than making 3 requests only, without the DO WHILE loop running throught the recordset?"

You want to make as FEW trips to the server as possible.  A Do...While loop against 15 records isn't going to be resource intensive...
0
 
lluthienCommented:
imho:
no it is not.

1> students will likely have to think about excercises. so they wont be making multiple requests per second per student.
2> 9 selects is not a lot per page.
3> your select statements seem to be straightforward, so not a whole lot of calculating is involved.


sqlserver should be well-equiped for this.
0
 
ispalenyCommented:
You can run all selects at once in one query and use
set recordset=recordset.nextrecordset
to walk thrue them.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rafranciscoCommented:
To load the questions, I guess you will just need 2 select statements, the first to get the 3 questions for the page, and the second to get the multiple choices for the 3 questions.   The second result set will have the question ID so that in the front-end you can just link it there.
0
 
lluthienCommented:
however, keep a look out on the saving of answers,
updating or inserting tends to lock the table, which will slow down the system significantly,
if 100's of students are using it.

so make sure the selects are done with "(with nolock)"
0
 
vanaudenAuthor Commented:
rafrancisco,

I think that if I do it your way, I would need to run throught the second recordset (which would contain all of my possible answers for all questions) with a DO WHILE.

What I am doing now is using the GetRows method to automatically place my three Recordsets into three arrays because I figured that using arrays is not as server intensive as running through one recordset with a DO WHILE.

What do you think about this? I am not sure.
0
 
arbertCommented:
"To load the questions, I guess you will just need 2 select statements, the first to get the 3 questions for the page, and the second to get the multiple choices for the 3 questions."

Why wouldn't this just be one statement with a join?
0
 
rafranciscoCommented:
>> What do you think about this? I am not sure. <<

Sorry to say but I am not very well versed with ASP.  If it was ASP.NET, that would be a different story.

>> Why wouldn't this just be one statement with a join? <<

Actually, yes it can be in just one statement but I was thinking more of how it will be handled on the client side.
0
 
vanaudenAuthor Commented:
Well again, I would need to use a DO WHILE loop to run throught the resulting recordset of about 15 records... Is that significantly more server intensive than making 3 requests only, without the DO WHILE loop running throught the recordset?

Does anyone know?
0
 
vanaudenAuthor Commented:
Wow, a lot of Info I never knew. Thank you all!

I have decided to build one stored procedure in SQL 2000 and then run through the recordset with a DO ... WHILE loop.

As for locking the records, students all save to seperate records, so no locking or unlocking is necessary.

I think this will greatly optimize my server requests.

I will have to say that the information I needed most was given by arbert and lluthien.

Thank you again!


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.