[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

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?
0
rondre
Asked:
rondre
  • 3
  • 3
1 Solution
 
sybeCommented:
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).

0
 
rondreAuthor Commented:
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?
0
 
sybeCommented:
> 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.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rondreAuthor Commented:
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.
0
 
sybeCommented:
That's a lot of stuff for a single page.

Isn't it easier to transfer the logic to a stored procedure, because I think it's is easier to have the transaction functionality there. And you need to only once pass the data to the stored procedure. Also a stored procedure in the database works faster then pulling data into an ASP page and then sending them back to the database.

I have never coded a transaction in an ASP page, but a google search turns up pages like this:
http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/iisbook/c07_transactional_asp.mspx

http://www.asp101.com/articles/chris/asptransactions/default.asp
0
 
rondreAuthor Commented:
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now