Databases
StackOverflow2013
Postsdbo
POSQL Query slow in .NET application but instantaneous in SQL Server Management Studio
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Query slow in .NET application but instantaneous in SQL Server Management Studio
    primarykey
    data
    text
    <p>Here is the SQL</p> <pre><code>SELECT tal.TrustAccountValue FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = 70402 AND ta.TrustAccountID = 117249 AND tal.trustaccountlogid = ( SELECT MAX (tal.trustaccountlogid) FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = 70402 AND ta.TrustAccountID = 117249 AND tal.TrustAccountLogDate &lt; '3/1/2010 12:00:00 AM' ) </code></pre> <p>Basicaly there is a Users table a TrustAccount table and a TrustAccountLog table.<br> Users: Contains users and their details<br> TrustAccount: A User can have multiple TrustAccounts.<br> TrustAccountLog: Contains an audit of all TrustAccount "movements". A<br> TrustAccount is associated with multiple TrustAccountLog entries. Now this query executes in milliseconds inside SQL Server Management Studio, but for some strange reason it takes forever in my C# app and even timesout (120s) sometimes.</p> <p>Here is the code in a nutshell. It gets called multiple times in a loop and the statement gets prepared.</p> <pre><code>cmd.CommandTimeout = Configuration.DBTimeout; cmd.CommandText = "SELECT tal.TrustAccountValue FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID1 AND ta.TrustAccountID = @TrustAccountID1 AND tal.trustaccountlogid = (SELECT MAX (tal.trustaccountlogid) FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID2 AND ta.TrustAccountID = @TrustAccountID2 AND tal.TrustAccountLogDate &lt; @TrustAccountLogDate2 ))"; cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId; cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId; cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId; cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId; cmd.Parameters.Add("@TrustAccountLogDate2", SqlDbType.DateTime).Value =TrustAccountLogDate; // And then... reader = cmd.ExecuteReader(); if (reader.Read()) { double value = (double)reader.GetValue(0); if (System.Double.IsNaN(value)) return 0; else return value; } else return 0; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAmr Elgarhy
    1. USn4rzul
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Query slow in .NET application but instantaneous in SQL Server Management Studio
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Query slow in .NET application but instantaneous in SQL Server Management Studio
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Query slow in .NET application but instantaneous in SQL Server Management Studio
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you give an indication of how long is 'instantaneous' in Management Studio and the time it is taking from your App?
      singulars
      1. POSQL Query slow in .NET application but instantaneous in SQL Server Management Studio
      1. USJulius A
    2. COin Management Studio the counter at the bottom reads 00:00:00 so sub 1 second at least. In my app it can takes as long as 45 seconds if it actually completes, but sometimes the command timeout is reached which is 120 seconds.
      singulars
      1. POSQL Query slow in .NET application but instantaneous in SQL Server Management Studio
      1. USn4rzul
 

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