SQL Server Performance in ADO.NET vs SSMS

Question

Before I begin, I've read several posts here regarding past performance issue's people have had executing SQL statements/procedures in ADO vs in SSMS. I've spent the better part of the day trying to solve this issue on my own...reindexing, using sp_recompile, adding Option(Recompile) to my procedure. Nothing has worked, so I'm turning to the community for assistance.

I have a stored procedure that one of my web applications executes for running reports. This procedure in particular is made up of mostly dynamic SQL to allow different report results to come back...sort of a dynamic reporting feature on my site. Anyhow, some reports can be run (using the same procedure) and results come back almost immediately. However, other options can be used and the procedure might take several minutes to run. However, manually running the procedure with those same options in SSMS gives immediate results. It sounds like some type of plan caching issue, but after recompiling the procedure and adding WITH(RECOMPILE), it's still running dreadfully slow in ADO.NET.

So I started looking at SQL profiles, perhaps one of the "SET" commands ADO is using is causing the issue. However, after using the same exact SET commands it's still returns almost immediately using SSMS.

I tried using DBCC freeproccache and, DBCC freesystemcache to clear out any stored plan, however this didn't help as well.

One other thing I tried was taking the dynamic SQL that gets generated in the procedure and running it directly in the SqlCommand statement. There are no parameters here, just plain SQL. Again it runs immediately in SSMS but takes forever in ADO.NET.

Is there a way (running ADO.NET) to see the plan that gets generated? I can do this in SSMS, however that won't help me here since it runs fine in SSMS.

If it's of any help, here is the raw SQL statement...

SELECT sf.ID [FileID], sb.ID [BillID], sb.Client_BillID, sf.BobID [ClientID], c.Name [ClientName], c.Parent_ID [ParentID], pnt.Name [ParentName], Network_ID, Facility_Name, OON, sb.TaxID, Inpatient, sf.ProcessDate, sb.Reversed, sb.State, sb.Product, sb.FormType, n.Direct 
INTO #t1 FROM SubmitterFiles sf WITH(NOLOCK) 
INNER JOIN SubmitterBills sb WITH (NOLOCK) ON sf.ID = sb.FileID 
LEFT JOIN PPORecords r WITH (NOLOCK) ON sb.RecordID = r.ID 
LEFT JOIN PPONetworks n WITH (NOLOCK) ON r.Network_ID = n.ID 
LEFT JOIN PPOProviders p WITH (NOLOCK) ON r.Provider_ID = p.ID 
INNER JOIN Clients c WITH (NOLOCK) ON sf.BobID = c.ID 
LEFT JOIN Clients pnt WITH (NOLOCK) ON c.Parent_ID = pnt.ID 
WHERE sf.ProcessDate BETWEEN 'Dec  1 2012 12:00AM' and 'Dec 31 2012 12:00AM' 
AND ISNULL(sb.Status,'') NOT IN ('E','V') 
AND (c.Parent_ID IN (1989) or c.ID  IN (1989)) 
; 

SELECT TOP 100 0 as [placeholder],NULL AS BillID, NULL AS Client_BillID, NULL AS DOS
,NULL AS Network_ID
,NULL AS Client_ID
,NULL AS Client_Name
,NULL As ProcessDate
,NULL As ProcessMonth
,NULL AS SubClientID
,NULL AS SubClientName
,Product
,TaxID
, FacilityName
, LastName
, FirstName
,State
,County
,NULL As ProcCode
,NULL As FormType
,NULL As Inpatient, NULL AS Outpatient
,COUNT(DISTINCT sb.BillID) AS [Total_Bills]
,SUM(sl.Amount) AS  [Total_Charges]
,SUM(sl.StateSavings) AS [Total_StateSavings]
,SUM(sl.PPOSavings) AS [Total_PPOSavings]
,COUNT(DISTINCT TaxID) AS [Total_Unique_TaxIds]
,0,0,0,0,0
,COUNT(DISTINCT CASE WHEN sb.OON = 1 THEN sb.BillID ELSE NULL END) AS [Out_Bills]
,SUM(CASE WHEN sb.OON = 1 THEN sl.Amount ELSE 0 END) AS [Out_Charges]
,SUM(CASE WHEN sb.OON = 1 THEN sl.StateSavings ELSE 0 END) AS [Out_StateSavings]
,COUNT(DISTINCT CASE WHEN sb.OON = 1 THEN sb.TaxID ELSE NULL END) AS [Out_Unique_TaxIds]
,0,0,0,0,0
,0,0,0,0,0
 FROM SubmitterLines sl  WITH (NOLOCK, INDEX(IX_SubmitterLines_BillID))
 INNER JOIN #t1 sb WITH(NOLOCK) ON sl.BillID = sb.BillID
 INNER JOIN SubmitterBillProviders sbp WITH(NOLOCK) ON sb.BillID = sbp.ID
 INNER JOIN SubmitterBillZipCounty sbc WITH(NOLOCK) ON sb.BillID = sbc.ID
 WHERE 1 = 1
GROUP BY Product
,TaxID
,FacilityName, LastName, FirstName
,State
,County
ORDER BY [Out_Bills] DESC

Answer 1

This was one of those problems that kept me up last night so I started going over everything again. It finally struck me that in SSMS, I have my default row count set to 1000. What kind of bugs me though is when I ran a trace in SQL Profiler, this didn't show up with the other SET's. Setting the ROWCOUNT back to 0 allowed me to reproduce this in SSMS allowing me to see the execution plan and fix the issue causing the query to run slow.

At the end of the day, it wasn't an ADO issue - it was a setting that I put in SSMS to limit the number of rows I normally like to return. Since part of my query builds a temp table of a larger result set, that temp table was only being populated with 1000 rows.