Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It all depends on how you are executing your queries really;</p> <p>Imagine the following query:</p> <pre><code>SqlConnection myConnection = new SqlConnection("connection_string"); SqlCommand myCommand = new SqlCommand(); myCommand.Connection = myConnection; myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandTimeout = some_long_time; myCommand.CommandText = "database_killing_procedure_lol"; myConnection.Open() //Connection's now open myCommand.ExecuteNonQuery(); </code></pre> <p>Two things will happen; one is this method will queue until the command.ExecuteNonQuery() finishes. The second is that we will also tie up a connection from the connection pool for the duration of the method.</p> <p>What happens if we timeout? Well, an exception is thrown - a SqlException with a Number property = -2. However, remember, in the code above, there is no exception management so all that will happen is the objects will go out of scope and we'll need to wait for them to be disposed. In particular, our connection wont be reusable until this happens.</p> <p>It's one of the reasons why the following pattern is preferred:</p> <pre><code>using(SqlConnection myConnection = new SqlConnection("connection_string")) { using(SqlCommand myCommand = new SqlCommand()) { SqlCommand myCommand = new SqlCommand(); myCommand.Connection = myConnection; myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandTimeout = some_long_time; myCommand.CommandText = "database_killing_procedure_lol"; myConnection.Open() //Connection's now open myCommand.ExecuteNonQuery(); } } </code></pre> <p>It means that, as soon as the query is finished, either naturally (it runs to completion) or through an exception (timeout or otherwise), the resources are <em>given back immediately</em>.</p> <p>In your specific issue, having a large number of queries that take a long time to execute is bad for many reasons. In a web application, you potentially have many users contending for a limited number of resources; memory, database connections, cpu time and so on. Therefore, tying up any of these with expensive operations will reduce the responsiveness and performance of your web application, or limit the number of users you can serve simultaneously. Futher, if the database operation is expensive, you can tie up your database, too, further limiting perofrmance.</p> <p>It's always worth attempting to bring the execution time for database queries down for this reason alone. If you can't, then you will have to be careful about how many of these types of queries you can run simultaneously.</p> <p><strong>EDIT:</strong></p> <p>So you are actually interested in what's happening on the SQL Server side... the answer is... it depends! The CommandTimeout is actually a client event - what you are saying is that if the query takes longer than n seconds, then I don't want to wait any more. SQL Server gets told that this is the case, but it still has to deal with what it's currently doing, so it can actually take some time before SQL Server finishes the query. It will attempt to prioritise this, but that's about it.</p> <p>This is especially true with transactions; if you are running a query wrapped in a transaction, and you roll that back as part of your exception management, then you have to wait until the rollback is complete. </p> <p>It's also very common to see people panic and start issuing <a href="http://msdn.microsoft.com/en-us/library/ms173730.aspx">KILL</a> commands against the SQL Process id that the query is running under. This is often a mistake if the command is running a transaction, but is often okay for long running selects.</p> <p>SQL Server has to manage it's state such that it remains consistent. The fact that the client is no longer listening means you have wasted work but SQL Server still has to clean up after itself. </p> <p>So yes, the ASP.Net side of things will be all fine as it doesn't care, but SQL Server still has to finish the work it began, or reach a point where it can abandon that work safely, or rollback any changes in any transactions that were opened. </p> <p>This obviously could have a performance impact on the database server depending on the query!</p> <p>Even a long running SELECT or UPDATE or INSERT outside of a transaction has to finish. SQL Server will try and abandon it as soon as it can, but only if it's safe to do so. Obviously, for UPDATES and INSERT's especially, it has to reach a point where the database is still consistent. For SELECT's it will attempt to end as soon as it is able to.</p>
    singulars
    1. This table or related slice is empty.
    1. PO.net SqlCommandTimeOut and the connection pool
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USdash
    1. USdash
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO.net SqlCommandTimeOut and the connection pool
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI'm sorry that I'm not clear about my question, I'm asking about the connection pool but not better practicing with 'using' keyword, please check my updated question.
      singulars
      1. PO
      1. USSimon Wang
    2. COIt's the same thing. If you don't release you connections as soon as you are finished with them, then they wont be available for reuse in the connection pool until they are disposed. The example highlights how you can unintentionally keep hold of a connection for longer than you expect. The last part talks about performance issues you might have from executing many long running queries.
      singulars
      1. PO
      1. USdash
    3. COHey I just update my question, please check, I'm really asking a different question which is I do closed the connection and it returns to connection pool, would it still running on sql server side. I'm not asking how .Net handle connection pool. thanks
      singulars
      1. PO
      1. USSimon Wang
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload