sp_executesql runs in milliseconds in SSMS but takes 3 seconds from ado.net

Question

This is my dynamic query used on search form which runs in milliseconds in SSMS roughly between 300 to 400 ms:

exec sp_executesql N'set arithabort off;
set transaction isolation level read uncommitted;

With cte as 
  (Select ROW_NUMBER() OVER 
      (Order By  Case When d.OldInstrumentID IS NULL 
          THEN d.LastStatusChangedDateTime Else d.RecordingDateTime End 
       desc) peta_rn,   
      d.DocumentID
   From Documents d
   Inner Join Users u on d.UserID = u.UserID 
   Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
   Inner Join ITypes it on it.ITypeID = d.ITypeID 
   Where 1=1  
       And (CreatedByAccountID = @0 Or DocumentStatusID = @1 Or DocumentStatusID = @2 )  
       And (d.JurisdictionID = @3 Or DocumentStatusID = @4 Or DocumentStatusID = @5)   
       AND (  d.DocumentStatusID = 9  ) 
   ) 
Select d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime, 
    d.CreatedByAccountID, d.JurisdictionID, 
    Case When d.OldInstrumentID IS NULL THEN d.LastStatusChangedDateTime 
        Else d.RecordingDateTime End as LastStatusChangedDateTime, 
    dbo.FnCanChangeDocumentStatus(d.DocumentStatusID,d.DocumentID) as CanChangeStatus, 
    d.IDate, d.InstrumentID, d.DocumentStatusID,ig.Abbreviation as IGroupAbbreviation, 
    u.Username, j.JDAbbreviation, inf.DocumentName,
    it.Abbreviation as ITypeAbbreviation, d.DocumentDate, 
    ds.Abbreviation as DocumentStatusAbbreviation,
    Upper(dbo.GetFlatDocumentName(d.DocumentID)) as FlatDocumentName 
From Documents d 
Left Join IGroupes ig On d.IGroupID = ig.IGroupID 
Left Join ITypes it On d.ITypeID = it.ITypeID 
Left Join Users u On u.UserID = d.UserID 
Left Join DocumentStatuses ds On d.DocumentStatusID = ds.DocumentStatusID 
Left Join InstrumentFiles inf On d.DocumentID = inf.DocumentID 
Left Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID 
Inner Join cte on cte.DocumentID = d.DocumentID 
Where 1=1 
    And peta_rn>=@6 AND peta_rn<=@7 
Order by peta_rn',
N'@0 int,@1 int,@2 int,@3 int,@4 int,@5 int,@6 bigint,@7 bigint',
@0=44,@1=5,@2=9,@3=1,@4=5,@5=9,@6=94200,@7=94250

This sql is formed in C# code and the where clauses are added dynamically based on the value the user has searched in search form. It takes roughly 3 seconds to move from one page to 2nd. I already have necessary indexes on most of the columns where I search.

Any idea why would my Ado.Net code be slow?

Update: Not sure if execution plans would help but here they are:

enter image description here

Are the times that you are providing comming from the SQL profiler ? See it can be that it takes the query to run 300-400ms anytime but there is a.net code that runs longer that is why I'm asking.. — Mortalus, Mar 01 '13 at 07:19
What is the database type of your Id? Are they VARCHAR/CHAR instead of NVARCHAR/NCHAR? — Aron, Mar 01 '13 at 07:22
Might be a parameter sniffing issue: https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/ — David Brabant, Mar 01 '13 at 08:05

score 1 · Answer 1 · answered Mar 01 '13 at 09:21

It is possible that SQL server has created inappropriate query plan for ADO.NET connections. We have seen similar issues with ADO, usual solution is to clear any query plans and run slow query again - this may create better plan. To clear query plans most general solution is to update statistics for involved tables. Like next for you:

update statistics documents with fullscan

Do same for other tables involved and then run your slow query from ADO.NET (do not run SSMS before).

Note that such timing inconsistencies may hint of bad query or database design - at least for us that is usually so :)

Aron · Answer 2 · 2013-03-01T09:17:13.917

0

.net by default uses UTF strings, which equates to NVARCHAR as opposed to VARCHAR.

When you are doing a WHERE ID = @foo in dot net, you are likely to be implicitly doing

WHERE CONVERT(ID, NVARCHAR) = @foo

The result is that this where clause can't be indexed, and must be table scanned. The solution is to actually pass each parameter into the SqlCommand as a DbParameter with the DbType set to VARCHAR (in the case of string).

A similar situation could of course occur with Int types if the .net parameter is "wider" than the SQL column equivalent.

PS The easiest way to "prove" this issue is to run your query in SSMS with the following above

DECLARE @p0 INT = 123
DECLARE @p1 NVARCHAR = "foobar" //etc etc

and compare with

DECLARE @p0 INT = 123
DECLARE @p1 VARCHAR = "foobar" //etc etc

edited Mar 01 '13 at 09:17

answered Mar 01 '13 at 08:27

Aron

15,464
3
31
64

The query that I posted above is from Ado.Net and recorded from profiler. I don't see any *Convert* in that query. – Jack Mar 01 '13 at 08:35
The convert is done in SQL server, hence the IMPLICITLY. – Aron Mar 01 '13 at 08:43
1

Try the query in SSMS but instead of putting the parameters in a VARCHAR try NVARCHAR. The same effect should show up. – Aron Mar 01 '13 at 08:44
Can you give me some links that can prove this? Just so it makes it easy for me to believe. – Jack Mar 01 '13 at 08:54
http://msdn.microsoft.com/en-us/library/system.data.common.dbparameter.dbtype.aspx "One of the DbType values. The default is String." Which is the same as NVARCHAR, AnsiString = VARCHAR. – Aron Mar 01 '13 at 09:06
http://stackoverflow.com/questions/1699382/linq-to-sql-nvarchar-problem similar problem that stems from LinqToSQL – Aron Mar 01 '13 at 09:07

score 0 · Answer 3 · answered Mar 01 '13 at 09:05

If you run a query repeatedly in SSMS, the database may re-use a previously created execution plan, and the required data may already be cached in memory.

There are a couple of things I notice in your query:

the CTE joins Users, IGroupes and ITypes, but the joined records are not used in the SELECT
the CTE performs an ORDER BY on a calculated expression (notice the 85% cost in (unindexed) Sort)

probably replacing the CASE expression with a computed persisted column which can be indexed speeds up execution.

note that the ORDER BY is executed on data resulting from joining 4 tables
the WHERE condition of the CTE states AND d.DocumentStatusID = 9, but AND's other DocumentStatusIDs
paging is performed on the result of 8 JOINed tables.

most likely creating an intermediate CTE which filters the first CTE based on peta_rn improves performance

sp_executesql runs in milliseconds in SSMS but takes 3 seconds from ado.net

3 Answers3