Q: Lets say you have a page that will go through a loop and will be updating 2000 records in a database SQL7 . pseduo code block which is the better way to
accomplish this?
Create object
open Database connection to 2000 querys
close database
do connection to the database Database object
or
Create object
start loop
open database
destroy connect to the database connection object database
query
close database
next
destroy
Re: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.
Re: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.
Re: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 
Re: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 timeYeah, it's bad. Too many open/existing connections can prevent new clients from connecting to the database.
Re: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?
Re: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
Re:In my opinion the first is going to be the fastest because you aren't constantly opening and closing the database object.
0 Comments.