May 6, 2014 at 6:56 pm
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
May 6, 2014 at 10:31 pm
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.
May 7, 2014 at 2:16 am
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?
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
May 7, 2014 at 4:41 am
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
May 7, 2014 at 11:46 am
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