对于SQL Server插入，更新，选择最快的方法 [英] Fastest method for SQL Server inserts, updates, selects
问题描述
我用的SP，这是不是一个SP VS code隐藏建立你的SQL命令的问题。我正在寻找一个高通量的方法，后端应用程序，处理很多琐碎的事务。我使用SqlDataReader的大部分收益，因为前锋只能在大多数情况下我。
I use SPs and this isn't an SP vs code-behind "Build your SQL command" question. I'm looking for a high-throughput method for a backend app that handles many small transactions. I use SQLDataReader for most of the returns since forward only works in most cases for me.
我已经看到了它做了很多的方法，并用其中大多数是自己。
I've seen it done many ways, and used most of them myself.
-
定义并接受存储过程的参数，参数本身并建立使用cmd.Parameters.Add（有或没有指定的DB值类型和/或长度）方法
Methods that define and accept the stored procedure parameters as parameters themselves and build using cmd.Parameters.Add (with or without specifying the DB value type and/or length)
组装你的SP PARAMS和它们的值到一个数组或哈希表，然后传递给一个解析收集更多的抽象方法，然后运行cmd.Parameters.Add
Assembling your SP params and their values into an array or hashtable, then passing to a more abstract method that parses the collection and then runs cmd.Parameters.Add
中的类重present表，初始化时需要的类，设置了重新present表中的字段的公共属性，并呼吁像保存，载入等方法
Classes that represent tables, initializing the class upon need, setting the public properties that represent the table fields, and calling methods like Save, Load, etc
我敢肯定有其他人我见过，但想不到的时刻也是如此。我向所有人开放的建议。
I'm sure there are others I've seen but can't think of at the moment as well. I'm open to all suggestions.
推荐答案
这答案主要集中在选择VS升级/创建/删除操作。我认为这是罕见的一次，以更新超过一个或几个记录，所以我也觉得'选择'是哪里的瓶颈往往发生。这就是说，你需要知道你的应用程序（配置文件）。关注您的优化时间最好的地方是几乎总是在查询本身在数据库级别，而不是客户端code。客户端code一切只是管道：这是不是你的应用程序的主要力量。然而，由于管道往往被重新用于许多不同的应用程序，我是同情的渴望得到它接近最佳越好，因此，我确实有很多可以说就如何构建code
This answer focuses mainly on 'select' vs update/create/delete operations. I think it's rarer to update more than one or a few records at a time, and so I also think 'select' is where the bottlenecks tend to occur. That said, you need to know your application (profile). The best place to focus your optimization time is almost always at the database level in the queries themselves, rather than the client code. The client code is all just the plumbing: it's not the main force of your app. However, as plumbing tends to be re-used in many different apps, I do sympathize with the desire to get it as close to optimal as possible, and therefore I do have plenty to say on how to build that code.
我有一个通用的方法，在我的数据层选择查询/程序，看起来是这样的：
I have a generic method for select queries/procedures in my data layer that looks something like this:
private static IEnumerable<IDataRecord> Retrieve(string sql, Action<SqlParameterCollection> addParameters)
{
//ConnectionString is a private static property in the data layer
// You can implement it to read from a config file or elsewhere
using (var cn = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand(sql, cn))
{
addParameters(cmd.Parameters);
cn.Open();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
yield return rdr;
rdr.Close();
}
}
}
这让我编写使用匿名方法来添加参数，公共数据层的方法。显示的code ++工程与.net 2.0 +，但可用来编写更短的.Net 3.5：
And that lets me write public data layer methods that use anonymous methods to add the parameters. The code shown works with .Net 2.0+, but can be written even shorter using .Net 3.5:
public IEnumerable<IDataRecord> GetFooChildrenByParentID(int ParentID)
{
//I could easily use a stored procedure name instead of a full sql query
return Retrieve(
@"SELECT c.*
FROM [ParentTable] p
INNER JOIN [ChildTable] c ON c.ParentID = f.ID
WHERE f.ID= @ParentID", delegate(SqlParameterCollection p)
{
p.Add("@ParentID", SqlDbType.Int).Value = ParentID;
}
);
}
我要站就在这里，所以我可以再次向您指出了code的正上方，使用参数创建匿名方法。的
这是非常干净code，因为它把查询定义和创建参数在同一个地方，同时还让您抽象掉样板数据库连接/调用code到某处更重表示可用。我不认为这种技术属于任何在你的问题的要点，而这恰好是pretty的织补得很迅速。我想这大概涵盖了你的问题的重点。的
我要继续，不过，来解释这一切是如何结合在一起的。其余的是相当简单的，但它也很容易抛出此列表或相似，并且把事情搞错了，最终损害的表现。所以在移动，业务层，然后使用一个工厂来查询结果转换为对象（C＃3.0或更高版本）：
I want to continue, though, to explain how this all fits together. The rest is fairly straightforward, but it's also easy to throw this to a list or similar and get things wrong, ultimately hurting performance. So moving on, the business layer then uses a factory to translate query results to objects (c# 3.0 or later):
public class Foo
{
//various normal properties and methods go here
public static Foo FooFactory(IDataRecord record)
{
return new Foo
{
Property1 = record[0],
Property2 = record[1]
//...
};
}
}
而不是让这些生活在自己的班级，你也可以它们一起放入专门用于存放工厂方法类的静态组。
Rather than having these live in their class, you could also group them all together into a static class specifically intended to hold the factory methods.
我需要做出一个改变原来的检索方法。该方法收益率同一个对象一遍又一遍，而这并不总是那么好。我们想要做的不同，使工作有什么给力的当前记录psented对象重新$ P $的副本，这样，当我们正在处理干净的数据读写器变异为下一个记录。我一直等到显示工厂方法，所以我们可以使用，在最后的code之后。新的检索方法是这样的：
I need to make one change to the original retrieve method. That method "yields" the same object over and over, and this doesn't always work that well. What we want to do differently to make it work is to force a copy of the object represented by the current record, so that when the reader mutates for the next record we're working with clean data. I waited until after showing the factory method so we can use that in the final code. The new Retrieve method looks like this:
private static IEnumerable<T> Retrieve(Func<IDataRecord, T> factory,
string sql, Action<SqlParameterCollection> addParameters)
{
//ConnectionString is a private static property in the data layer
// You can implement it to read from a config file or elsewhere
using (var cn = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand(sql, cn))
{
addParameters(cmd.Parameters);
cn.Open();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
yield return factory(rdr);
rdr.Close();
}
}
}
而现在我们会说这就是新的检索（）方法是这样的：
And now we would call that new Retrieve() method like this:
public IEnumerable<Foo> GetFooChildrenByParentID(int ParentID)
{
//I could easily use a stored procedure name instead of a full sql query
return Retrieve(Foo.FooFactory,
@"SELECT c.*
FROM [ParentTable] p
INNER JOIN [ChildTable] c ON c.ParentID = f.ID
WHERE f.ID= @ParentID", delegate(SqlParameterCollection p)
{
p.Add("@ParentID", SqlDbType.Int).Value = ParentID;
}
);
}
显然，这最后一种方法可以扩大到包括所需的任何额外的业务逻辑。这也证明这个code是特别快，因为它需要的IEnumerable的延迟计算功能。缺点是，它往往会造成很多短命的对象，这会损害事务的性能，你问。为了解决这个问题我有时候会打破良好的n层并直接传递IDataRecord对象到presentation层和避免不必要的对象创建为简单地绑定到网格控件马上记录。
Obviously this last method can be expanded to include any additional business logic needed. It also turns out this code is exceptionally fast, because it takes advantage of the lazy evaluation features of IEnumerable. The downside is that it tends to create a lot of short-lived objects, and that can hurt the transactional performance you asked about. To get around this I sometimes break good n-tier and pass the IDataRecord objects directly to the presentation tier and avoid unnecessary object creation for records that are simply bound to a grid control right away.
更新/创建code是相似的，区别在于你通常只改变一个记录一次，而不是很多。
Update/Create code is similar, with the difference that you're usually only changing one record at a time rather than many.
或者，我可以为您节省阅读这篇长职务，只是告诉你，使用实体框架;）
Or, I could save you reading this long post and just tell you to use Entity Framework ;)
这篇关于对于SQL Server插入，更新，选择最快的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
