如何清洁&优化WCF OData服务生成的代码？ [英] How to clean & optimise code generated by WCF OData service?

问题描述

我们看到的问题是，我们看到的问题是生成的代码是非常复杂的，并且不必要地复杂，并且在对服务器执行时导致巨大的延迟。

例如：

  SELECT 
 [Extent1]。[Id] AS [Id]，
 N'ODATAModel.LatestFiguresView'AS [C1]，
 N 'EntityType] AS [EntityType]，
 [Extent1]。[Name] AS [C2]，
 [Extent1] [Name]，
 [Extent1]。[RegistrationNumber] AS [RegistrationNumber]，
 [Extent1]。[OfficeNumber] AS [OfficeNumber]，
 ...载入其他列... 
，
 N''AS [C12] 
 FROM（SELECT 
 [LatestFiguresView]。[Id] AS [Id]，
 [LatestFiguresView]。[EntityType] AS [EntityType]，
 [LatestFiguresView]。[Name] AS [Name]，
 [LatestFiguresVie w]。[RegistrationNumber] AS [RegistrationNumber]，
 [LatestFiguresView]。[OfficeNumber] AS [OfficeNumber]，
 ...加载这些相同的列... 
 
 FROM [dbo]。[LatestFiguresView] AS [LatestFiguresView]）AS [Extent1] 
 WHERE（N'Registered'= [Extent1]。[RegistrationStatus]）
 AND（（1 = [Extent1]保留]）
 OR（1 =（CASE WHEN（[Extent1]。[Name] LIKE N'％White％'）
 THEN cast（1 as bit）
 WHEN（NOT Extent1]。[Name] LIKE N'％White％'））
 THEN转换（0作为位）END））
 OR（1 =（CASE WHEN（[Extent1]）[ ％Business％'）
 THEN转换（1作为位）
 WHEN（NOT（[Extent1]）[部门] LIKE'％Business％'））
 THEN cast（0 as bit） END））
 OR（（1 =（CASE WHEN ... etc，etc ... END））））
  

如您所见，有不必要的嵌套选择，WHERE子句包含对值的冗余检查的负载（例如CASE WHEN MyColumn LIKE'％Value％'THEN 1 WHEN NOT WHI NOT MYColumn LIKE'％Value％'THEN 0

当然有一个

解决方案

当您使用实体框架从数据库中选择一个较大的对象图时，您收到 - 正如您所看到的 - 通过将一些查询表合并在一起形成的大量数据。那么在你的应用程序中，魔法会发生在你的应用程序中，因为这个超大的结果集被剥离并被重新分解成了实体。

你映射的每个列都将从数据库。这只是自然的 - 你不能选择一半的实体。如果您选择了 LatestFiguresView ，那么该视图中的每一列都将在您的选择语句中 - 如果您在订单上检索产品，则产品&

是的，实体框架总是执行 SELECT [列] FROM（SELECT [dbo]。[Column] AS [Column]）。我不能告诉你为什么，但是我会猜测，它可以帮助代码安全围绕一些边缘情况，直接从表中选择会导致问题，或者允许查询生成器更有效率。你是对的，这些是不必要的，但我不认为他们花费任何东西。我可以在同一时间运行 SELECT * FROM MyTable 和 SELECT *（SELECT * FROM MyTable）t p>

最后， WHERE 和 CASE 语句由您的查询 - 没有看到它，或完整的 WHERE 子句，很难评论（注意：我真的不想看到你的查询，我确定它不漂亮）。如果你有如果x或y或（a& b）然后事情会变得凌乱。

通常情况下，它们会在SQL中勉强快速地担心 CASE 语句。我再次假设这是一个代码安全的决定，或者它允许一个更有效（更通用的）查询生成器。

我建议您在查询分析器中运行此操作以对其进行基准测试并识别任何问题，然后编写自己的SQL并执行相同操作。有很多情况下，编写存储Procs比实体框架编译您的查询更有效，但如果从具有多个条件的View直接选择将是其中一种情况，我会感到惊讶。

I have an OData web service that is mostly OOTB in terms of how it accesses and returns data from the database.

The problem we're seeing is that the code being generated is incredibly - and unnecessarily - complex and is causing huge delays when executing against the server.

For example:

SELECT 
[Extent1].[Id] AS [Id], 
N'ODATAModel.LatestFiguresView' AS [C1], 
N'EntityType,Name,RegistrationNumber,OfficeNumber,DateRegistered,...,Id' AS [C2], 
[Extent1].[EntityType] AS [EntityType], 
[Extent1].[Name] AS [Name], 
[Extent1].[RegistrationNumber] AS [RegistrationNumber], 
[Extent1].[OfficeNumber] AS [OfficeNumber], 
... Loads of other columns ...
,
N'' AS [C12]
FROM (SELECT 
      [LatestFiguresView].[Id] AS [Id], 
      [LatestFiguresView].[EntityType] AS [EntityType], 
      [LatestFiguresView].[Name] AS [Name], 
      [LatestFiguresView].[RegistrationNumber] AS [RegistrationNumber], 
      [LatestFiguresView].[OfficeNumber] AS [OfficeNumber], 
... Loads of those same columns ...

FROM [dbo].[LatestFiguresView] AS [LatestFiguresView]) AS [Extent1]
WHERE (N'Registered' = [Extent1].[RegistrationStatus]) 
    AND ((1 = [Extent1].[Reservation]) 
    OR (1 = (CASE WHEN ([Extent1].[Name] LIKE N'%White%') 
    THEN cast(1 as bit) 
    WHEN ( NOT ([Extent1].[Name] LIKE N'%White%')) 
    THEN cast(0 as bit) END)) 
    OR (1 = (CASE WHEN ([Extent1].[Sectors] LIKE '%Business%') 
    THEN cast(1 as bit) 
    WHEN ( NOT ([Extent1].[Sectors] LIKE '%Business%')) 
    THEN cast(0 as bit) END)) 
    OR ((1 = (CASE WHEN ... etc, etc ... END))))

As you can see, there are unnecessary nested selects and the WHERE clause contains loads of redundant checks on values (e.g. "CASE WHEN MyColumn LIKE '%Value%' THEN 1 WHEN NOT MyColumn LIKE '%Value%' THEN 0")

Surely there's a way to tidy this mess up before it hits the database?

解决方案

When you select a large object graph from your database using Entity Framework you receive - as you're seeing - a huge swathe of data formed by joining a number of queried tables together. Then the 'magic' happens back in your application as this oversized result set is stripped and shredded back into entities.

Every column that you've mapped will be selected from the database. This is only natural - you can't select half of an entity. If you're selecting the LatestFiguresView then every column in that View will be in your select statement - and if you're retrieving Products on an Order then every mapped column on Product & Order will be in the result set.

Yes, Entity Framework always does SELECT [Column] FROM (SELECT [dbo].[Column] AS [Column]). I couldn't tell you why, but I'll guess that it either helps with code safety around some edge case where selecting directly from a table causes problems, or it allows the query generator to be more efficient. You're right that these are 'unnecessary' but I don't think they cost anything. I can run SELECT * FROM MyTable and SELECT * (SELECT * FROM MyTable) t in the same time.

Finally, the WHERE and CASE statements are determined by your query - without seeing it, or the full WHERE clause, it's hard to comment (note: I don't really want to see your query, I'm sure it's not pretty). If you have if x or y or (a & b) then things will get messy.

I don't normally worry about CASE statements as they are blindingly fast in SQL. I'd again assume that this is either a code-safety decision, or it allows for a more efficient (more generic?) query-generator.

I'd suggest you run this in Query Analyser to benchmark it and identify any problems, and then write your own SQL and do the same. There are plenty of cases where it's more efficient to write Stored Procs than to have Entity Framework compile your queries, but I'd be surprised if selecting directly from a View with a number of conditions would be one of those cases.

这篇关于如何清洁&优化WCF OData服务生成的代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！