ADO .NET与SQL Server Management Studio中 - ADO执行得很差 [英] ADO .NET vs. SQL Server Management Studio - ADO performs worse
问题描述
我跑在ADO.NET C＃和SQL Server Management Studio中相同的命令。即通过C＃中运行的SQL执行显著恶化 - 内存使用情况更糟糕的是（使用所有可用内存），并从而导致执行时间增加数据库。管理工作室并不完美（它也导致SQL Server使用的内存），但它不是通过ADO.NET的那样糟糕。
I'm running the same commands in ADO.NET C# and Sql Server Management studio. The SQL that runs via C# performs significantly worse - memory usage is worse (using up all available memory) and thus causing the database executing time to increase. The management studio isn't perfect (it too causes sql server to use up memory) but it's not as bad as via ADO.NET.
我运行：Windows 7中时，SQL Server 2008 R2，10.50.1600。 C＃.NET 3.5。 SQL Server管理套件2008 R2。所有的程序和数据库是我的本地开发计算机上。
I am running: Windows 7, Sql Server 2008 R2, 10.50.1600. C# .NET 3.5. Sql Server management Studio 2008 R2. All programs and databases are on my local dev machine.
我运行的是SQL是40 CREATE VIEW年代和40创建2数据库的唯一索引。我需要这样做的飞行，我们正在运行2数据库之间的数据库比较（对于不相关，我们需要比较的观点，而不是表的原因）。 。而且，由于性能是一个问题，我们不能离开身边所有的时间视图和索引
The SQL I am running is 40 create view's and 40 create unique indexes on 2 database's. I need to do this on the fly as we are running a database compare between 2 databases (for reasons that aren't relevant we need to compare views and not tables). And since performance is an issue we cannot leave the views and indexes around all the time.
SQL是这样的：
create view [dbo].[view_datacompare_2011106] with schemabinding as (
SELECT t.[ID], t.[Column1], t.[Column2], t.[Column3], FROM dbo.Table t WHERE t.[ID] in ('1','2','3','4') )
go
create unique clustered index [index_datacompare_2011106] on [dbo].[view_datacompare_2011106] (ID)
go
...
唯一的区别是，C＃代码不叫去。每个创建CMD在using语句包裹起来，并呼吁通过的ExecuteNonQuery（）例如：
The only difference is that the C# code does not call Go. Each create cmd is wrapped up in a using statement and called via ExecuteNonQuery() e.g.
using (SqlCommand cmd = new SqlCommand(sql, this.connectionActualDb))
{
cmd.CommandTimeout = Int32.Parse(SqlResources.TimeoutSeconds);
cmd.ExecuteNonQuery();
}
P.S。 SET ARITHABORT必须要创建或更改计算列或索引视图索引时ON。
P.S. SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views.
推荐答案
使用的等待和队列方法调查性能瓶颈。你会找到问题的根源，然后我们可以相应的建议。最有可能你的C＃应用程序运行到并发由于锁，极有可能由应用程序本身。通常是一个责备由于参数嗅探计划的变化，如缓慢的应用，快速的SSMS ，但DDL语句，这是不太可能的。
Use Waits and Queues methodology to investigate the performance bottleneck. You'll find the root cause and then we can advice accordingly. Most likely your C# application runs into concurrency due to locks, very likely held by the application itself. Typically one blames plan changes due to parameter sniffing, as in Slow in the Application, Fast in SSMS, but with DDL statements this is unlikely.
这篇关于ADO .NET与SQL Server Management Studio中 - ADO执行得很差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
