Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why is this fast query slow in Visual Studio Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 6:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:19 AM
Points: 120, Visits: 343
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


  Post Attachments 
SlowQuery.txt (5 views, 1.46 KB)
Post #1568256
Posted Tuesday, May 6, 2014 10:31 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 97, Visits: 2,609
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.
Post #1568270
Posted Wednesday, May 7, 2014 2:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1568323
Posted Wednesday, May 7, 2014 4:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 14,196, Visits: 28,520
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1568367
Posted Wednesday, May 7, 2014 11:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:19 AM
Points: 120, Visits: 343
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.
Post #1568626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse