sql-server - ADO.NET 与 SSMS 中的 SQL Server 性能对比

Question

在开始之前，我在这里阅读了几篇关于过去的性能问题的文章，人们在 ADO 和 SSMS 中执行 SQL 语句/过程。我花了一天的大部分时间试图自己解决这个问题...重新索引，使用 sp_recompile，将 Option(Recompile) 添加到我的程序中。没有任何效果，所以我向社区寻求帮助。

我有一个存储过程，我的一个 Web 应用程序执行它来运行报告。该过程主要由动态 SQL 组成，以允许返回不同的报告结果...有点像我网站上的动态报告功能。不管怎样，一些报告可以运行(使用相同的过程)并且结果几乎立即返回。但是，可以使用其他选项，并且该过程可能需要几分钟才能运行。但是，在 SSMS 中使用相同选项手动运行该过程会立即产生结果。这听起来像是某种类型的计划缓存问题，但在重新编译该过程并添加 WITH(RECOMPILE) 后，它在 ADO.NET 中的运行速度仍然非常慢。

所以我开始查看 SQL 配置文件，也许 ADO 正在使用的“SET”命令之一导致了问题。但是，在使用完全相同的 SET 命令后，使用 SSMS 仍然几乎立即返回。

我尝试使用 DBCC freeproccache 和 DBCC freesystemcache 清除任何存储的计划，但这也没有帮助。

我尝试的另一件事是获取过程中生成的动态 SQL 并直接在 SqlCommand 语句中运行它。这里没有参数，只有简单的 SQL。同样，它在 SSMS 中立即运行，但在 ADO.NET 中则需要很长时间。

有没有办法(运行 ADO.NET)来查看生成的计划？我可以在 SSMS 中执行此操作，但这对我没有帮助，因为它在 SSMS 中运行良好。

如果有任何帮助，这里是原始 SQL 语句...

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

这是昨晚让我失眠的问题之一，所以我开始重新审视所有事情。最后让我震惊的是，在 SSMS 中，我将默认行计数设置为 1000。但我遇到的一个错误是，当我在 SQL Profiler 中运行跟踪时，其他 SET 中没有出现这种情况。将 ROWCOUNT 设置回 0 让我能够在 SSMS 中重现这一点，从而让我能够查看执行计划并修复导致查询运行缓慢的问题。

归根结底，这不是 ADO 问题 - 这是我在 SSMS 中放入的一个设置，用于限制我通常希望返回的行数。由于我的查询的一部分构建了一个较大结果集的临时表，因此该临时表仅填充了 1000 行。