.NET 到远程 SQL Server 在一台计算机上速度较慢，在另一台计算机上则不然
我正在新设置的 Windows 7 桌面上开发 .NET 4.0 应用程序。在该应用程序中，对远程数据库的查询非常慢。相同的应用程序在我到目前为止一直使用的笔记本电脑上运行得非常快。我现在使用的台式机比笔记本电脑快很多。我正在使用 VPN 连接来连接到远程数据库服务器（与笔记本电脑上相同）。 IIS 和 Cassini 中的速度都很慢。
我应该补充一点，该应用程序正在使用实体框架进行数据访问。
有谁知道问题可能是什么？
更新1： 好吧，这真的变得很奇怪。我在笔记本电脑和台式机上查看了 SQL Server Profiler 中的查询。他们都是一样的。但在我的桌面上，查询的执行时间越来越长。因此，我在桌面上启动了 Management Studio，并执行了从 SQL Server Profiler 中获取的查询。它运行得非常快（不到一秒）。但当我再次执行时，花了1秒。然后，2 秒，然后 4 秒（看到模式了吗？）。我一生都无法弄清楚是什么导致了这种情况。这是查询：
select top (2) [Extent1].[RequestID] AS [RequestID],
[Extent1].[RequestDate] AS [RequestDate],
[Extent1].[RequestTitle] AS [RequestTitle],
[Extent1].[RequestTypeID1] AS [RequestTypeID1],
[Extent1].[RequestTypeID2] AS [RequestTypeID2],
[Extent1].[SubjectMatterID1] AS [SubjectMatterID1],
[Extent1].[SubjectMatterID2] AS [SubjectMatterID2],
[Extent1].[ProviderID] AS [ProviderID],
[Extent1].[Description] AS [Description],
[Extent1].[Notes] AS [Notes],
[Extent1].[RequestedBy] AS [RequestedBy],
[Extent1].[OrganisationID] AS [OrganisationID],
[Extent1].[ProgramID] AS [ProgramID],
[Extent1].[StatusID] AS [StatusID],
[Extent1].[MethodID] AS [MethodID],
[Extent1].[StaffID] AS [StaffID],
[Extent1].[TopicID] AS [TopicID],
[Extent1].[RequestCreatedBy] AS [RequestCreatedBy],
[Extent1].[RequestCreatedOn] AS [RequestCreatedOn],
[Extent1].[RequestModifiedBy] AS [RequestModifiedBy],
[Extent1].[RequestModifiedOn] AS [RequestModifiedOn],
[Extent1].[RequestedByExtraID] AS [RequestedByExtraID]
FROM [dbo].[Request] AS [Extent1]
但是等等！事情变得更奇怪了！无论我执行的频率如何，以下查询总是花费相同的时间（不到一秒）：
select top (2) * From request
我在这方面浪费了大量的时间。如果有人有任何指点，你将永远出现在我的酷人书中。
更新2 如果我在管理工作室中“包括实际执行计划”，则查询总是非常快，无论我执行的频率如何。
I'm developing a .NET 4.0 application on a newly setup Windows 7 desktop. Queries to the remote database are extremely slow in that application. The same application is running pretty fast on my laptop which I've used up until now. The desktop I'm using now is a lot faster than the laptop. I'm using a VPN connection to connect to the remote db server (same as on the laptop). It's slow in both IIS and Cassini.
I should add that the application is using Entity Framework for data access.
Does anyone have an idea what the problem could be?
UPDATE 1:
OK, this is really getting weird. I looked at the queries in SQL Server Profiler on laptop and desktop. They're both the same. But on my desktop, the queries take longer and longer to execute. So I fired up Management Studio on the desktop and executed a query that I grabbed from SQL Server Profiler. It ran really fast (under a second). But when I executed it again it took 1 second. Then, 2 seconds, then 4 (see the pattern?). I can't for the life of me figure out what would be causing this. Here's the query:
select top (2) [Extent1].[RequestID] AS [RequestID],
[Extent1].[RequestDate] AS [RequestDate],
[Extent1].[RequestTitle] AS [RequestTitle],
[Extent1].[RequestTypeID1] AS [RequestTypeID1],
[Extent1].[RequestTypeID2] AS [RequestTypeID2],
[Extent1].[SubjectMatterID1] AS [SubjectMatterID1],
[Extent1].[SubjectMatterID2] AS [SubjectMatterID2],
[Extent1].[ProviderID] AS [ProviderID],
[Extent1].[Description] AS [Description],
[Extent1].[Notes] AS [Notes],
[Extent1].[RequestedBy] AS [RequestedBy],
[Extent1].[OrganisationID] AS [OrganisationID],
[Extent1].[ProgramID] AS [ProgramID],
[Extent1].[StatusID] AS [StatusID],
[Extent1].[MethodID] AS [MethodID],
[Extent1].[StaffID] AS [StaffID],
[Extent1].[TopicID] AS [TopicID],
[Extent1].[RequestCreatedBy] AS [RequestCreatedBy],
[Extent1].[RequestCreatedOn] AS [RequestCreatedOn],
[Extent1].[RequestModifiedBy] AS [RequestModifiedBy],
[Extent1].[RequestModifiedOn] AS [RequestModifiedOn],
[Extent1].[RequestedByExtraID] AS [RequestedByExtraID]
FROM [dbo].[Request] AS [Extent1]
But wait! It gets weirder! The following query always takes the same amount of time (less than a second), no matter how often I execute it:
select top (2) * From request
I have wasted an insane amount of time on this. If anyone has any pointers, you'd be in my book of cool people forever.
UPDATE 2
If I "Include Actual Execution plan" in management studio, the query is always very fast, no matter how often I execute it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
最后，经过几天的尝试各种不同的设置，甚至从头开始重新安装所有东西（包括Windows），我以某种方式解决了这个问题。这是 - 我仍然不敢相信 - 网卡设置。我禁用了“IPv4 校验和卸载”、“大发送卸载 (IPv4)”、“TCP 校验和卸载 (IPv4)”和“UDP 校验和卸载 (IPv4)”。现在又好又快...
Finally, after days of trying all kinds of different settings, and even reinstalling everything (including Windows) from scratch, I somehow figured out the problem. It was - and I still can't believe this - the network card settings. I disabled "IPv4 Checksum Offload", "Large Send Offload (IPv4)", "TCP Checksum Offload (IPv4)", and "UDP Checksum Offload (IPv4)". Now it's nice and fast...
嗯，我在我们的应用程序中经历过类似的事情。请注意 ManagementStudio 结果可能会导致您得出错误的结论。连接上有一个参数 ARITHABORT，在 MS 中默认设置为 ON，但在您的应用程序中，默认情况下它始终为 OFF。
链接：
SQL Server ARITHABORT
https://stackoverflow.com/questions/2465887/why-会设置 arithabort-on-dramatically-speed-up-a-query
Well, i've experienced something similar in our app. Beware that ManagementStudio results may led you to incorrect conclusions. There is a parameter ARITHABORT on connection which is by default set to ON in MS, but from your app it is always OFF by default.
Links:
SQL Server ARITHABORT
https://stackoverflow.com/questions/2465887/why-would-set-arithabort-on-dramatically-speed-up-a-query
在数据库服务器上运行跟踪并查看实际正在针对其运行哪些实际 SQL 语句。我有一种感觉，你的 EF 生成的 SQL 与你在 Management Studio 中生成的 SQL 不同。您可能需要优化它。 VPN 连接也可能是问题的一部分。
Run a trace on the DB server and see what actual SQL statements are actually being run against it. I have a feeling your EF generates diff SQL than what you have in management studio. You may need to optimize it. VPN connection could also be part of the problem.
同样的事情也发生在我身上，我花了几天时间试图弄清楚。从我的工作站上从实体框架和 ADO.NET 到远程 SQL Server 的查询运行速度非常慢，但在所有其他开发人员的工作站上运行速度很快。禁用大型发送卸载 V2 (IPv4) 立即解决了问题。
Same thing happed to me and I spent days trying to figure it out. Queries from Entity Framework and ADO.NET to a remote SQL Server from my workstation would run extremely slow, but fast on all the other developer's stations. Disabling the Large Send Offload V2 (IPv4) immediately solved the problem.