Why is this fast query slow in Visual Studio

  • The query below executes in SSMS in 2 ms. When I run the exact same query in Visual Studio it takes 9651 ms. That is a big difference. I would like to understand why and find a way to improve the performance of the VS operation.

    Those times come from SQL profiler. SQL Profiler reports the queries as ( I have added returns to make the results easier to read):

    SSMS:

    Declare @BeginDt as varchar(20) = '20140101'

    Declare @EndDt as varchar(20) = '20140430'

    Declare @LotNo as varchar(20) = ' 000000113855'

    Set statistics time on

    Select Sum(Ln.Qty_to_SHip * Ln.Unit_Price *((100-Ln.Discount_Pct)/100)) as Ext

    From OEHdrHst_SQL Hdr

    Inner Join OELinHst_SQL Ln

    On Hdr.Ord_Type = Ln.Ord_type and Hdr.Ord_No = Ln.Ord_No And Hdr.Inv_No = Ln.Inv_No

    Inner Join IMLstrx_SQL T

    On T.Item_No = Ln.Item_No and T.Ctl_No = Ln.Inv_No and T.LIne_No = Ln.Line_No

    Where Hdr.Orig_Ord_Type = 'O' and Hdr.Inv_Dt >= @BeginDt and Hdr.Inv_Dt <= @EndDt and T.Ser_Lot_No = @LotNo

    set statistics Time off

    2 ms

    From Visual Studio

    exec sp_executesql N

    'Select Sum(Ln.Qty_to_SHip * Ln.Unit_Price *((100-Ln.Discount_Pct)/100)) as Ext

    From OEHdrHst_SQL Hdr

    Inner Join OELinHst_SQL Ln

    On Hdr.Ord_Type = Ln.Ord_type and Hdr.Ord_No = Ln.Ord_No And Hdr.Inv_No = Ln.Inv_No

    Inner Join IMLstrx_SQL T On T.Item_No = Ln.Item_No and T.Ctl_No = Ln.Inv_No and T.LIne_No = Ln.Line_No

    Where Hdr.Orig_Ord_Type = ''O'' and Hdr.Inv_Dt >= @BeginDt and Hdr.Inv_Dt <= @EndDt and T.Ser_Lot_No = @LotNo '

    ,N

    '@LotNo nvarchar(15),@BeginDt bigint,@EndDt bigint',@LotNo=N' 000000101058',@BeginDt=20140101,@EndDt=20140430

    Duration 9651

    I have attached my connection and command code from the Visual Studio project.

    I have never encountered this sort of difference. Could there be some sort of stored Execution Plan that this query method is calling?

    This query is called several thousand times when my VS code is executed.

    Any ideas on how to speed this thing up?

    Thanks,

    pat

  • What happens if You change variable declaration to

    Declare @BeginDt as bigint = 20140101

    Declare @EndDt as bigint = 20140430

    Declare @LotNo as nvarchar(15) = N' 000000113855'

    and execute query after the change. Especially notice varchar -> nvarchar change.

    If query is slow after this change please attach execution plans for queries with both variable sets.

    This looks like case where implicit conversion from nvarchar to varchar causes table/index scan instead of seek.

  • Begin by eliminating the obvious differences - your parameters are different datatypes between the two environments. The datatype of your parameters should match the datatype of the table columns wherever possible to avoid implicit conversions, which can make index seeks impossible.

    I'd try first changing the datatypes of your parameters in the SSMS script so that they match the datatype in the VS script, and comparing run times.

    Next, I'd modify the parameters so they match the datatype of the column they are compared to.

    Next, I'd put the SQL into a stored procedure. There are numerous good reasons for this including maintainability and plan reuse. Can you think of any good reasons not to do it?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Check the ANSI connection settings for each environment. If they're different it can lead to different execution plans and therefore radically different performance.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You have correctly identified the declaration of the parameters, specifically the data type, as the problem. My VS statement, “.addwithValue(“@LotNo”,”000000113855”) which is implicitly converted to @LotNo=N' 000000101058' is the source of the slow down. The conversion of the two dates does not seem to affect performance.

    I found two methods to correct the problem. The first is to just put the string into the query programmatically, “Where Ser_lot_no = ‘ 000000113855’ …”.

    The second method is to declare the dbType of the parameter. This a bit tricky in this case. The database (from a third party vendor. Not one I created) defines Ser_lot_No as a Char(15). I encountered a problem here in that the SQLParameter dbTypes do not include the char data type. The article at http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx suggests that the Char type does exist but it was not available to me in the intelisense and manually typing it in resulted in a error.

    After some trial an error I found AnsiStringFixedLength to work in this instance.

    My original code:

    .addwithvalue(“@LotNo”, “ 000000113855”)

    My revised code

    Dim p as new system.data.SQLParameter

    p.Name = “@LotNo”

    P.SQLDbType = SQLDbType.AnsiStringFixedLength

    P.Value = “ 000000113855”

    MacCmd.Parameters.Add(p)

    The new code is much longer and I will decide whether or not to use it on a case by case basis. (I have been using the old method for several years. Writing thousands of parameterized queries against a Char data type. This is the first instance of a detectable problem. )

    Thanks everyone for your assistance.

    PS not sure how to mark multiple post as partial solutions. Sorry.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply