asked on

Run multiple SQL Queries through 1 asp command

I'm not sure how to properly phrase what I want to do. I have multiple SQL inserts and updates that run through an asp page and because our servers and bandwidth suck, often times the page will time out before completing all of the sql statements. I've been told that there is a way to wrap all of these sql statements into a "transaction" where they will all run or all fail. Does anyone have more information about this?

sybe

If a page times out, it will still be executed. So that would not make any difference.
Multiple inserts and updates in a page can be very quick, although it depends on what is done exactly.

I suspect that the slowness is rather caused by posting lots of data, that is where bandwidth plays a role. There's no bandwidth involved in inserts and updates (unless it is done to a database outside the network of the webserver, but that seems unlikely).

rondre

ASKER

The db is in house so that shouldn't be the problem. I'm not sure what the problem is here as normally everything works great, but out of roughly 2000 times this page i wrote has been used, there have been roughly 10 times that we've found where only part of the code has executed. Is it not true that creating multiple recordsets (as is done on this page) doesn't really slow things down?

sybe

> Is it not true that creating multiple recordsets (as is done on this page) doesn't really slow things down?

Well, yes. But for updates and inserts you don't need recordsets.

connection.Execute("INSERT INTO <table> (<fieldnames>) values (<values>)"), , 128
connection.Exectute("UPDATE <table> SET <fieldname> = <value>, <fieldname2> = <value2" WHERE <id> = <value>"), , 128

Not sure what you are doing, but if you pull a whole table from the database in order to insert or update one record, things will be quite slow.

rondre

ASKER

I have about 8 insert statements and 10 update statements. All of them are using the connection.execute method, however, i have other recordsets that are using select statements to pull data into vars for use in the update and insert statements.

Since there are occasions where only part of the statements will execute, I'm assuming that from your post that this must mean that the server is actually going down where transmission is getting cut off somehow because there are definite times where only part of the code is getting executed. In that case, would putting all of the statements into say 1 function/transaction/whatever it's called - would that still cause only part to get posted or would it do an all or nothing as I would want it to, and if so how does one package them up for use w/ asp?

Thanks for all your help.

ASKER CERTIFIED SOLUTION

sybe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rondre

ASKER

Thanks Sybe, I don't have much experience with stored procedures, but in reviewing these links, I'm sure this is what I'm looking for, Thanks for all your help.