database connection in ASP performance question

[Mucman]

Let's say you have a page that will go through a loop and will be updating 2000 records in a SQL7 database... which pseduo code block is the better way to
accomplish this?

Create Database object

open connection to database

do 2000 querys

close connection to database

destroy database object

or

Create Database object

start loop
open connection to database
query
close connection to database
next

destroy database object

 

[Codewiz]

In my opinion the first is going to be the fastest because you aren't constantly opening and closing the database object.

 

[MSantiago]

The first one without a doubt. Assuming all the queries are being performed on the same database, there's no need to keep opening and closing it with each query. 🙂

edit: I can't spell

 

[Mucman]

Thanks... I was just wondering if there is something bad about having the database connection open for a long time... What if the calculations in the loop are time consuming?

 

[Oogle]

What if the calculations in the loop are time consuming?

Try to avoid putting calculations or other work into the loop. Only put queries. If you really have to loop that much, then there's most likely a design flaw in your database schema.

I was just wondering if there is something bad about having the database connection open for a long time

Yeah, it's bad. Too many open/existing connections can prevent new clients from connecting to the database.

 

[Mucman]

This is a run once a week type of script, so performance isn't critical in this application... I think I could do it in 1 SQL query, but I am not sure if it is an improvement. How would a query be if
there are 200 OR's in the WHERE clause?

ie

SELECT username
FROM tblUsernames
WHERE (status = 'A') AND (username = 'fdsfs' OR username = 'fsdfsd' OR username = 'nla' .... repeat many times)

it's probably not bad schema, it's probably that I just suck at TSQL 😛

 

[Argo]

It's always better to put everything in 1 query than 2000 smaller once, since you never know what kinda internal optimizations DB engine does.

 

[Argo]

Instead of doing 2000 queries do 200 with 10 ORs each... The reason for that is that for each query it reads a record into memory and then compares it against each username in the WHERE clause. Therefore, for the first case you'll have 2000 I/O operations, while for the second only 200. As far as DBs go, I/O is the only thing that matters, comparison operations and etc don't take any significant amount of time.