EF查詢百萬級數據的性能測試--多表連接複雜查詢

相關文章：EF查詢百萬級數據的性能測試--單表查詢一、起因上次做的是EF百萬級數據的單表查詢，總結了一下，在200w以下的數據量的情況(Sql Server 2012)，EF是可以使用，但是由於查詢條件過於簡單，且是單表查詢，EF只是負責生成Sql語句，對於一些簡單的查詢，生成Sql語句的時間可 ...

相關文章：EF查詢百萬級數據的性能測試--單表查詢

一、起因

　　　上次做的是EF百萬級數據的單表查詢，總結了一下，在200w以下的數據量的情況(Sql Server 2012)，EF是可以使用，但是由於查詢條件過於簡單，且是單表查詢，EF只是負責生成Sql語句，對於一些簡單的查詢，生成Sql語句的時間可以基本忽略，所以不僅沒有發揮出EF的優勢，而且這樣的性能瓶頸基本可以說是和資料庫完全有關的，這個鍋資料庫得背(資料庫：怪我了)。鑒於實際項目中多是多表的連接查詢，還有其他複雜的查詢，一向本著求真務實的思想的博主就趁此機會再次測試了一下EF的複雜的連接查詢什麼的。說實話，在測試之前我也不知道結果，只是為了自己以後用起來有個參考依據，也比總是聽別人說EF性能很差，嚇得都不敢用了要好。EF的性能到底有多差，或者說可以勝任什麼樣的場景，不吹不黑，我們就一起來看看，也好在以後的實際項目選型的時候參考一下。

二、關於很多ORM框架的對比測試

　　博主最近也看了不少關於ORM框架的測試，大多數都是增刪改幾千，幾萬條的數據，這樣確實可以看出來性能的比較，但是實際項目中真的很少有這樣的情況，一次增刪改幾千幾萬條數據的，我們做項目服務的都是用戶，按用戶的一次請求為一次資料庫上下文的操作，同一個上下文在這樣的一次請求中基本不可能同時提交這麼多的數據操作，有人說那要是成千上萬的用戶同時呢，那就要考慮併發了，就不是本文所要討論的問題了。所以這些測試能表明結果，但是不能表明實際問題。另外在大多數對於EF的測試中，很多人忽略了EF對於實體的跟蹤，比如：

　　　　這些屬性雖然我不全知道是什麼的東西，但是既然可以設置Enabled，就說明是對性能有影響的，而且數據量越多，相信影響也越大，但其他多數ORM應該都沒有這些功能或者設置（我不知道，哈哈），所以對於增刪改的操作，我覺得當前情況下是完全夠用的，所以不再探究增刪改的性能（如果實在有朋友覺得必要，博主再找機會）。EF的初衷，也可以說是很多ORM應該具備的出發點，就是從以前的非常不OO的數據操作方式，變成現在的OO的方式，就是為瞭解放開發人員寫Sql查詢操作資料庫的方式，就是要用面向對象的思想來操作資料庫，結果倒好，有些人又要回到以前寫Sql語句，又要去回到解放前，這就好比面向過程編程效率很高速度很快，但是為什麼要提出面向對象編程，因為面向過程寫起來累啊！不好維護啊！不好擴展啊！不方便啊，還有分層架構，不都是為了這嗎，這些東西我們應該是發揮它的優勢，知道他在什麼情況下用，什麼情況下不用，而不是一直死死的抓住他的缺點說不行。當然，有很多情況下是不追求生產效率，只追求性能的，那就不說了。

　　　　說了這麼多，我也不是想證明什麼，我只是想知道，我該什麼情況下用EF，怎麼用EF來發揮出他的優勢，怎麼能用好EF，應用到實際生產環境中。一句話，為什麼我的眼裡常含淚水，因為我對EF愛的深沉。（斜眼笑）

三、準備工作

　　那肯定是先建表結構和數據了，廢話不多說，上圖先。

　　 1.關係圖

　　這是資料庫的關係圖，只有User和Role是多對多關係，其他的是一對多，另外都加了導航屬性，博主事先用的是Code First，已經添加了導航屬性，為的是可以在後來的測試中使用導航屬性（EF會自動根據導航屬性生成連接查詢，可以由此來做測試），這裡借用了Database First來從資料庫生成了模型圖，為的是大家能夠清楚的看表之間的關係。

　　簡單說明一下：

　　　　　　一個User對應多個Order；

　　　　　　一個Order對應多個OrderDetail，對應一個City；

　　　　　　一個OrderDetail相當於一個產品，對應一個產品類型Category。

　　　　　　其中由於多對多的關係比較少見，且可以轉化為兩個一對多的關係（Sql Server就是這麼乾的），所以這次暫時不做多對多的測試，應該和一對多差不多。

　　 2.表數據

　　這裡城市表是現在項目中用的一個，因為之前就三個欄位Id，Name，ParentId，然後要找其他數據就要遞歸查詢，很浪費時間，後來想了想既然都是死數據，就一下給寫進去，之後再用就不用查了。

　　附上City表的Sql文件，有需要的同學可以帶走：dbo.City.Table.zip

　　在某東首頁複製的商品類型數據。。

　　3.數據量

　　用戶表，訂單表，訂單明細表都是100w的數據，其他兩個表按實際情況來，類型表沒有再細分，就這樣吧。

四、開始測試

　　1.關於Sql語句生成的時間

　　　由於大多數人都說EF的性能瓶頸在生成Sql的時間和質量上，引用一位朋友的回答如下：

　　　上邊這條評論的第二條說的應該就是質量的問題，關於EF生成Sql語句有什麼規則，或者怎樣才能生成高質量的Sql，這個內容也是一個很值得研究的問題，我們隨後有時間研究。今天我們就只針對生成Sql語句的時間上加以探究。

　　　在網上搜索了一些資料，關於怎麼測試EF生成Sql的時間，博主沒有見到過相關的測試，但是怎樣獲取到生成的Sql語句還是有辦法的，所以，博主想了想，既然能獲取到sql語句，那麼這個獲取的過程就可以作為生成Sql的時間，由於沒有相關的資料說明，所以暫且用這樣的方法來測，博主使用的兩種比較笨的方法測試生成的時間，也希望園友們如果有更好的方法可以告訴博主。

　　　　1.ToString()方法

　　　　由於在IQueryable介面中重寫了ToString()方法，所以博主試了一下，果真能獲取到Sql語句，所以就用ToString()方法的執行時間當做生成Sql語句的時間。先來個簡單的：

　　　　可以看出已經生成了Sql（註意：這裡並沒有去資料庫查詢，只是生成了Sql）涉及到了最簡的兩個表的鏈接，那我們接下來看生成所用的時間。

　　　可以看出來，生成Sql的時間非常短，完全可以忽略不計，可能博友覺得Sql過於簡單，沒關係，我們再來幾個複雜的

　　　　　複雜語句一，涉及到了四個表的鏈接：

　　　　　　依舊很少時間，只是略比上一個Sql的時間長一點，畢竟複雜了一點。

　　　　　複雜語句二，直接截圖了，這裡為了生成Sql語句的複雜，隨便寫了一些Linq，可能不是我們日常想要的結果，只是為了複雜而已：

　　　　　　時間明顯變長，但是依舊不到1ms，附上生成的Sql語句，夠複雜了吧。　　　　

 1 SELECT 
 2     [Project7].[C1] AS [C1], 
 3     [Project7].[Work] AS [Work], 
 4     [Project7].[C2] AS [C2]
 5     FROM ( SELECT 
 6         [Project6].[Work] AS [Work], 
 7         1 AS [C1], 
 8         [Project6].[C1] AS [C2]
 9         FROM ( SELECT 
10             [Project3].[Work] AS [Work], 
11             (SELECT 
12                 MAX([Project5].[Amount]) AS [A1]
13                 FROM ( SELECT 
14                     [Extent11].[Id] AS [Id], 
15                     [Extent11].[Amount] AS [Amount], 
16                     [Filter4].[UserId] AS [UserId]
17                     FROM   (SELECT [Project4].[UserId] AS [UserId], [Project4].[FullName] AS [FullName], [Project4].[UserName] AS [UserName1], [Extent10].[Work] AS [Work]
18                         FROM   (SELECT 
19                             [Extent6].[UserId] AS [UserId], 
20                             [Extent7].[FullName] AS [FullName], 
21                             [Extent8].[UserName] AS [UserName], 
22                             (SELECT 
23                                 SUM([Extent9].[TotalPrice]) AS [A1]
24                                 FROM [dbo].[OrderDetail] AS [Extent9]
25                                 WHERE [Extent6].[Id] = [Extent9].[OrderId]) AS [C1]
26                             FROM   [dbo].[Order] AS [Extent6]
27                             INNER JOIN [dbo].[City] AS [Extent7] ON [Extent6].[CityId] = [Extent7].[Id]
28                             INNER JOIN [dbo].[User] AS [Extent8] ON [Extent6].[UserId] = [Extent8].[Id] ) AS [Project4]
29                         LEFT OUTER JOIN [dbo].[User] AS [Extent10] ON [Project4].[UserId] = [Extent10].[Id]
30                         WHERE [Project4].[C1] > cast(500 as decimal(18)) ) AS [Filter4]
31                     LEFT OUTER JOIN [dbo].[User] AS [Extent11] ON [Filter4].[UserId] = [Extent11].[Id]
32                     WHERE ([Filter4].[FullName] LIKE @p__linq__0 ESCAPE N'~') AND (([Filter4].[UserName1] = @p__linq__1) OR (([Filter4].[UserName1] IS NULL) AND (@p__linq__1 IS NULL))) AND (([Project3].[Work] = [Filter4].[Work]) OR (([Project3].[Work] IS NULL) AND ([Filter4].[Work] IS NULL)))
33                 )  AS [Project5]) AS [C1]
34             FROM ( SELECT 
35                 [Distinct1].[Work] AS [Work]
36                 FROM ( SELECT DISTINCT 
37                     [Extent5].[Work] AS [Work]
38                     FROM   (SELECT 
39                         [Extent1].[UserId] AS [UserId], 
40                         [Extent2].[FullName] AS [FullName], 
41                         [Extent3].[UserName] AS [UserName], 
42                         (SELECT 
43                             SUM([Extent4].[TotalPrice]) AS [A1]
44                             FROM [dbo].[OrderDetail] AS [Extent4]
45                             WHERE [Extent1].[Id] = [Extent4].[OrderId]) AS [C1]
46                         FROM   [dbo].[Order] AS [Extent1]
47                         INNER JOIN [dbo].[City] AS [Extent2] ON [Extent1].[CityId] = [Extent2].[Id]
48                         INNER JOIN [dbo].[User] AS [Extent3] ON [Extent1].[UserId] = [Extent3].[Id] ) AS [Project1]
49                     LEFT OUTER JOIN [dbo].[User] AS [Extent5] ON [Project1].[UserId] = [Extent5].[Id]
50                     WHERE ([Project1].[C1] > cast(500 as decimal(18))) AND ([Project1].[FullName] LIKE @p__linq__0 ESCAPE N'~') AND (([Project1].[UserName] = @p__linq__1) OR (([Project1].[UserName] IS NULL) AND (@p__linq__1 IS NULL)))
51                 )  AS [Distinct1]
52             )  AS [Project3]
53         )  AS [Project6]
54     )  AS [Project7]
55     ORDER BY [Project7].[Work] ASC

　　　　　複雜語句三，再來一個看看，用到了分頁。

　　　　　這次由於比較複雜，所以生成Sql也花費了一些時間，可以看出來已經到的4、5ms左右，但是生成的Sql確比上次的少。

SELECT 
    [Project3].[Id] AS [Id], 
    [Project3].[UserName] AS [UserName], 
    [Project3].[Name] AS [Name], 
    [Project3].[Amount] AS [Amount], 
    [Project3].[C1] AS [C1]
    FROM ( SELECT 
        [Project2].[Id] AS [Id], 
        [Project2].[UserName] AS [UserName], 
        [Project2].[Amount] AS [Amount], 
        [Project2].[Name] AS [Name], 
        [Project2].[C1] AS [C1]
        FROM ( SELECT 
            [Project1].[Id] AS [Id], 
            [Extent5].[UserName] AS [UserName], 
            [Extent5].[Amount] AS [Amount], 
            [Extent6].[Name] AS [Name], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[OrderDetail] AS [Extent7]
                WHERE [Project1].[Id] = [Extent7].[OrderId]) AS [C1]
            FROM    (SELECT 
                [Extent1].[Id] AS [Id], 
                [Extent1].[UserId] AS [UserId], 
                [Extent1].[CityId] AS [CityId], 
                [Extent2].[FullName] AS [FullName], 
                [Extent3].[UserName] AS [UserName], 
                (SELECT 
                    SUM([Extent4].[TotalPrice]) AS [A1]
                    FROM [dbo].[OrderDetail] AS [Extent4]
                    WHERE [Extent1].[Id] = [Extent4].[OrderId]) AS [C1]
                FROM   [dbo].[Order] AS [Extent1]
                INNER JOIN [dbo].[City] AS [Extent2] ON [Extent1].[CityId] = [Extent2].[Id]
                INNER JOIN [dbo].[User] AS [Extent3] ON [Extent1].[UserId] = [Extent3].[Id] ) AS [Project1]
            LEFT OUTER JOIN [dbo].[User] AS [Extent5] ON [Project1].[UserId] = [Extent5].[Id]
            LEFT OUTER JOIN [dbo].[City] AS [Extent6] ON [Project1].[CityId] = [Extent6].[Id]
            WHERE ([Project1].[C1] > cast(500 as decimal(18))) AND ([Project1].[FullName] LIKE @p__linq__0 ESCAPE N'~') AND (([Project1].[UserName] = @p__linq__1) OR (([Project1].[UserName] IS NULL) AND (@p__linq__1 IS NULL)))
        )  AS [Project2]
        WHERE ([Project2].[Amount] > cast(50 as decimal(18))) AND ([Project2].[Amount] < cast(500 as decimal(18)))
    )  AS [Project3]