比较多层应用中的性能Linq To实体和outofBand SQL更新 [英] Comparing Performance Linq To entities and outofBand SQL updates in multilayered apps
问题描述
您好，
首先SQL代码
字符串sqlUpdate =" UPDATE [订单明细] SET Quantity = Quantity +1;" ;;
string sqlReset =" UPDATE [Order详细信息] SET Quantity = Quantity -1;" ;;
using（SqlConnection sqlConn = new SqlConnection（connString））
sqlCmd.CommandType = CommandType sqlCmd。 .Text;
sqlCmd.CommandText = sqlUpdate;
sqlConn.Open（）;
int recsUpdated = sqlCmd.ExecuteNonQuery（）;
timer.Stop（）;
sqlCmd.CommandText = sqlReset ;结果int recsReset = sqlCmd.ExecuteNonQuery（）;
sqlConn.Close（）;
if（recsUpdated！= recsReset）
MessageBox.Show（" Error update or resetting Order_Details.Quantity。"）; < br>}
第二个EF代码
使用（ctxNwind = new NorthwindEntities（））
{
foreach（在ctxNwind.Order_Details中的Order_Detail d）
d。数量+ = 1;
ctxNwind.SaveChanges（false）;
foreach（在ctxNwind.Order_Details中的Order_Detail d）
d.Quantity - = 1;
ctxNwind.SaveChanges（false）;
var origQuan =（来自d在ctxNwind.Order_Details中选择d .Quantity）.FirstOrDefault（）;
if（origQuan！= 12）
MessageBox.Show（"更新或重置Order_Details.Quantity。"错误;）}
第二EF比普通SQL慢100倍。 （基于 wrox ADO.NET 3.5与LINQ和实体框架第515页的示例）
现在在分布式设计中我必须遵循Daniel Simmons在博客上的建议.msdn.com / dsimmons / archive / 2008/02/17 / context-lifetimes-dispose-or-reuse-.aspx
- 为每个Web服务调用创建一个新的Context（无状态）
- 添加更改分批到商店。
1）这意味着我将第一次调用WCF Web服务，该服务获取客户端所需的所有数据。
2）必须收集更改，直到通过调用WCF Web服务方法并在某些实体上使用CUD操作列表来回写所有更改的预定义时间点
嗯点1意味着用户必须等待很长时间才能它的数据出现。
如果客户端在收集大量收集的更改之前崩溃，则第2点容易崩溃。
不知何故我觉得EF不是适当的（因为每次服务调用需要很长时间才能创建Objectcontext）。可以使用EntityBag方法，但数据传输对象不是持久性无知。
有人评论吗？我错了吗？
谢谢
David
当工作在"OO"时world，你的类代表数据库中的一个实体，所以你最终编写代码来更新一组实体，然后让你的ORM层在数据库中应用这些更改 - 有ORM可以进行批量更新，那里是那些将每个变化转换为命令的人，EF是最新的。顺便说一句，批量更新也比单个更新语句慢。
在设计业务逻辑层时，您需要考虑跨越许多实体的此类操作，并了解执行此类操作的优缺点。 BL中的操作，然后将此命令发送到DB。如果你退后一步并考虑缺点，你最终会做出合乎逻辑的事情 - 将BL代码分解为你可以和你应该做什么.net代码以及你应该在数据库代码中做什么。一次更新大量实体显然（大多数情况下）您将使用存储过程在数据库中执行某些操作 - 这可能会改变业务逻辑的工作方式，并且可能会稍微改变您的架构，但在考虑如何"整齐"你的体系结构再次表现如何，有时你需要改变设计方式以获得性能提升。
总而言之 - 如果你在数据库中声明存储过程，你可以使用EF声明一个将映射到它的方法，请在此处阅读：
http：// msdn.microsoft.com/en-us/library/bb399203.aspx 搜索结果，伊
hi there,
First the SQL Code
string sqlUpdate = "UPDATE [Order Details] SET Quantity = Quantity +1;";
string sqlReset = "UPDATE [Order Details] SET Quantity = Quantity -1;";
using (SqlConnection sqlConn = new SqlConnection(connString))
{
SqlCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = sqlUpdate;
sqlConn.Open();
int recsUpdated = sqlCmd.ExecuteNonQuery();
timer.Stop();
sqlCmd.CommandText = sqlReset;
int recsReset = sqlCmd.ExecuteNonQuery();
sqlConn.Close();
if (recsUpdated != recsReset)
MessageBox.Show("Error updating or resetting Order_Details.Quantity.");
}
second the EF code
using (ctxNwind = new NorthwindEntities())
{
foreach (Order_Detail d in ctxNwind.Order_Details)
d.Quantity += 1;
ctxNwind.SaveChanges(false);
foreach (Order_Detail d in ctxNwind.Order_Details)
d.Quantity -= 1;
ctxNwind.SaveChanges(false);
var origQuan = (from d in ctxNwind.Order_Details select d.Quantity).FirstOrDefault();
if (origQuan != 12)
MessageBox.Show("Error updating or resetting Order_Details.Quantity.");
}
second EF is 100 times slower than plain SQL. (example based on wrox ADO.NET 3.5 with LINQ and the Entity Framework page 515)
Now in distributed designs I have to follow then the recommendations of Daniel Simmons at
blogs.msdn.com/dsimmons/archive/2008/02/17/context-lifetimes-dispose-or-reuse-.aspx
-Create a new Context for each web service call(being stateless)
-Add changes to the store in batches.
1)this would mean I would have a first call to the WCF Web service which fetches all the data required in the Client.
2)the changes have to be collected until a predefined Time point at which all the changes are written back by calling a WCF web service method with a list of CUD operations on some entities
Hmm point 1 would mean that the user would have to wait a long time before its data appears.
point 2 is prone to crashes if the client crashes before the mass of collected changes are applied.
somehow I have the feeling that EF is not appropriate(since time to create the Objectcontext fore each service call takes to much time) for distributed designs with 3 Layers. One could use EntityBag approaches but the Data transfer objects are then not persistence ignorant.
Anyone some comment? am I wrong somewhere?
thanks
David
Hi,
When working in a "OO" world, your class represents an entity in the database, so you end up writing a code to update a set of entities, and then ask your ORM layer to apply this changes in the database - there are ORM's that can do batch updates, and there are those who translate each change to a command, EF is the latest. BTW, batch update is also slower than a single update statment.
When designing a business logic layer, you need to take into account such actions that span over many entities and understand the pros and cons of doing this kind of action in your BL, and then sending this command to the DB. If you take a step back and consider the cons, you end up doing the logical thing - splitting the BL code between what you can and should do in you .net code and what you should do in your DB code. Updating lots of entities at once is clearly (most of the times) something you'll do in the DB, using a stored procedure - this can change the way your business logic works, and perhaps change your architecture a bit, but when considering how "neat" your architecture is agains how well it performs, sometimes you will need to change the way you design to earn a performance gain.
And to sums this up - if you declare a stored procedure in the DB, you can use EF to declare a method that will be mapped to it, read about it here:
http://msdn.microsoft.com/en-us/library/bb399203.aspx
Ido.
这篇关于比较多层应用中的性能Linq To实体和outofBand SQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
