SQL Server Performance

Puzzling Performance Difference

Discussion in 'ALL SQL SERVER QUESTIONS' started by starkst, Nov 7, 2014.

  1. starkst New Member

    When I run a simple query (in SSMS) against a complex view, it takes about 44 seconds (for one particular query, for others, over 3 minutes).

    select * from partcostview where partnum = '123456';
    (44 seconds for one particular partNum, minutes for others).

    select (somefields but not Cost) from partcostview where partnum = '123456';
    (immediate).
    The view calls other views (4 deep) one of which is a CTE which scans records hierarchically to determine a cost. So the performance is perhaps not surprising. If I remove the Cost field from the query, it returns immediately. Sql-server does a good job recognizing all that.

    That's not the puzzling part.
    If I run the query from a C# program, including cost, it return immediately.
    That's the puzzling part.
    Cost is returned, immediately.

    Here's the C# script:

    const String sqlFormat = "SELECT * FROM PartCostView WHERE PartNum = '{0}'";
    var sql = String.Format(sqlFormat, partNum);
    return svr.GetDataSql(sql, AssignData, null, evt);

    This runs in < 1 second (regardless of PartNum) always and returns the cost as well.
    There's nothing mysterious in that call, it's just using ADO with some column parsing.

    If I substitute a C# call to a stored procedure that implements that same query on the database, the performance back to 40+ seconds.

    What am I missing? How is that view optimized so well from C# but not from SMSS and how can I get a stored procedure to perform like that? I'm kind of curious to find out what I don't know.
  2. moh_hassan20 New Member

    It is hard to know the root cause, except there are some measures to be analyzed.
    Please, run the profiler to capture the series of Transact-SQL statements and monitor the duration of every step of the query ,add show plan xml event.
    Save the result to a file and inspect it carefully to know the root cause.
    Take into account that the first run of the query result in caching of execution plan and the data, and next run may take less time
    Sometimes, we have the opposite, performance is slow in application but fast in SSMS, have a look to the url:
    http://www.sommarskog.se/query-plan-mysteries.html

Share This Page