Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113

    Unanswered: Discrepancy in query execution.

    I have a .NET application communicating with a SQL Server 2008 database.

    There is a particular stored procedure that when executed by the application takes excessively longer to execute than when run through SQL Server Management Studio with identical parameters.

    It doesn't matter in which order I execute them (SSMS first, application second, or vice versa, or at the same time) so I can rule out caching as being the problem - but some sample stats for executing the stored procedure are as follows:

    CPU time:
    SSMS: Consistently around 200-400ms but has gone as high as 2,500ms on a couple of occasions.
    .NET: Consistenly between 10,000 and 20,000ms but has gone as high as 80,000ms

    Reads:
    SSMS: Consistently between 37,000 and 40,000.
    .NET: Consistently between 950,000 and 1,000,000

    Writes:
    SSMS: 16-18 every time without exception
    .NET: 3-5 every time without exception

    Looking at the SQL Server Profiler trace on the two calls, they are almost identical in every way. The only discrepancies between when I execute it through SSMS and .NET is that the latter has data in the BinaryData column adnd the ObjectName column, where those are blank for my SSMS call to the stored procedure.

    I'm using the same user account in both instances and I can't think of anything that might account for such a large discrepancy in the CPU time and the number of reads between the two other than perhaps the way the SQL Driver in .NET functions when using an IDataReader

    Any thoughts?

  2. #2
    Join Date
    May 2010
    Posts
    2

    i have few ideas

    well it is sounds very waired

    but :
    1. you could add the with(onlock) to you query
    that should speed up your queries

    2. did you tried the LINQ option ?

    and one more question

    is the query execution what is taking so long ?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing a great deal about your SQL Server configuration, your network topography, your client configuration, etc, etc, and so forth we need to do a whole lot of guessing. This appears to be a systemic problem.

    When you run the stored procedure from SSMS installed on the client machine, does it run fast/slow/in-between? After running on the client via SSMS, does your application run fast/slow/in-between?

    How does your application connect to the SQL Server? Does it use ODBC, OLEDB, or something else?

    What are the ping times and tracert values (mostly the count of the levels and total elapsed time) between the machine?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    I'm running via SSMS on my own computer, as well as running the application from my own computer so the network is the same between the two. The DB is hosted on a server on the LAN with -1ms ping and 1 hop on the trace.

    It doesn't matter in which order I run it, SSMS then application, or vice versa. The application is always much longer execution time than SSMS - I can run it 10 times on SSMS and 10 times on the application alternating between each and I get the same results every time - application is always at least 10 times longer to run the same stored proc with the same params - so that rules out caching as far as I'm concerned.

    We use the System.Data.SqlClient namespace in .NET to manage all our connection objects (App written in C#)

    Of particular interest to me is the massive difference in the number of reads between the two methods of execution - the application is definitely doing something different though from running SQL Server profiler I can't see what it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •