Run-on commands
-
Okay, this may be a really stupid question, but sitting here and thinking about it, followed by a large number of Google attempts has left me unenlightened. Here goes: Let's say I have an ASP.Net web application that displays a report to users using a datagrid. The query for the report is fairly complex and involves several joins using either poorly- or non-indexed tables containing hundreds of thousands of rows each. Suffice it to say, the query is long-running, which is not the greatest thing to have in a web application, but that's not really the point of my question. Now, let's say an impatient user signs in to the application and requests the slow report described above. The server-side application logic opens a connection, generates a command for the report, and executes it against the database server. The user drums his fingers on his desk for a while, whistles for a few minutes after that, and finally says "Something must be wrong!" and stabs the 'Submit' button to generate the report again (causing a new request against the web server, causing another command to be created and executed etc.). A minute passes and the query has still not returned... The user, now completely disgusted, closes his/her browser and goes to lunch. Now the question: What happens to the two commands - in fact two *connections* - that are presumably executing at the database server? They were both created under the main process of the ASP.Net worker account, and the process has not disappeared, however there is no longer anyone listening for the command to complete and return (either the browser is closed or the user has browsed away). Do the queries complete and return (to nowhere)? (no idea) Is the database server somehow signalled that the commands have been 'orphaned'? (doubt it) Do the commands, now presumably with a broken communication pipe the the client that requested their execution, sit until timeout occurs? Is the end nigh, and should I try to find a cool, dry place to watch Armageddon? The reason I ask is that I've observed similar issues in cases where a user posts a request for data from a long-running command, then closes his/her browser thinking that opening a new browser will make things go faster, only to find out that the DB server is almost completely unresponsive. I have also observed it from the DB server side, where there are large numbers of processes, alive and taking up CPU time, but never seeming to complete. Thanks for any input.
-
Okay, this may be a really stupid question, but sitting here and thinking about it, followed by a large number of Google attempts has left me unenlightened. Here goes: Let's say I have an ASP.Net web application that displays a report to users using a datagrid. The query for the report is fairly complex and involves several joins using either poorly- or non-indexed tables containing hundreds of thousands of rows each. Suffice it to say, the query is long-running, which is not the greatest thing to have in a web application, but that's not really the point of my question. Now, let's say an impatient user signs in to the application and requests the slow report described above. The server-side application logic opens a connection, generates a command for the report, and executes it against the database server. The user drums his fingers on his desk for a while, whistles for a few minutes after that, and finally says "Something must be wrong!" and stabs the 'Submit' button to generate the report again (causing a new request against the web server, causing another command to be created and executed etc.). A minute passes and the query has still not returned... The user, now completely disgusted, closes his/her browser and goes to lunch. Now the question: What happens to the two commands - in fact two *connections* - that are presumably executing at the database server? They were both created under the main process of the ASP.Net worker account, and the process has not disappeared, however there is no longer anyone listening for the command to complete and return (either the browser is closed or the user has browsed away). Do the queries complete and return (to nowhere)? (no idea) Is the database server somehow signalled that the commands have been 'orphaned'? (doubt it) Do the commands, now presumably with a broken communication pipe the the client that requested their execution, sit until timeout occurs? Is the end nigh, and should I try to find a cool, dry place to watch Armageddon? The reason I ask is that I've observed similar issues in cases where a user posts a request for data from a long-running command, then closes his/her browser thinking that opening a new browser will make things go faster, only to find out that the DB server is almost completely unresponsive. I have also observed it from the DB server side, where there are large numbers of processes, alive and taking up CPU time, but never seeming to complete. Thanks for any input.
-
This is my grand theory (im quite sure on this as i have SQLprofiled it in test environment) Once the browser is closed the connection to Sql is lost and then sql svr aborts the process... Prove me wrong People =)
That's intuitively what I would expect, but I'm not certain it's what always happens. I should clarify somewhat by pointing out that my observations have been made with Oracle 9i, not SQL Server. The reason I'm not sure orphaned connections are always cleaned up neatly and efficiently is based on two main points: 1. I've watched from the DB side as connections were created from ASP.Net applications, during the execution of which the client browser was either closed or directed to a different (and unrelated) URL. The connections have not always dropped gracefully. 2. This is more of a gap in my knowledge than anything, but it seems as if there's a point in the execution of a SQL command from any application client where control is passed to the DB server to complete the work and return results across the pipe. In ASP.Net, where the (OS) process creating the connection is tied to the application, not the client browser, and is not terminated merely because the browser closes. In such cases, it appears as if the message pipe might be broken with regard to returning results to the end client, but the process under which the work is being performed is alive and well. Thanks for your input. If possible, could you provide some information on how you structured your tests? I'll be doing some of my own, so I'd be curious to see what you did.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’