Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-04-29
10
Medium Priority
?
207 Views
Last Modified: 2011-09-20
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??

0
Comment
Question by:vanauden
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 11

Assisted Solution

by:lluthien
lluthien earned 1000 total points
ID: 13895511
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 13895520
You can run all selects at once in one query and use
set recordset=recordset.nextrecordset
to walk thrue them.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13895534
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 11

Expert Comment

by:lluthien
ID: 13895546
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
 
LVL 1

Author Comment

by:vanauden
ID: 13895637
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
 
LVL 34

Expert Comment

by:arbert
ID: 13895719
"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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13895759
>> 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
 
LVL 1

Author Comment

by:vanauden
ID: 13895767
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
 
LVL 34

Accepted Solution

by:
arbert earned 1000 total points
ID: 13895842
"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
 
LVL 1

Author Comment

by:vanauden
ID: 13896409
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question