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??
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??
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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)"
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)"
ASKER
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.
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.
"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?
Why wouldn't this just be one statement with a join?
>> 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.
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.
ASKER
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?
Does anyone know?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
set recordset=recordset.nextre
to walk thrue them.