C# + Entity FrameworkアプリケーションでTake()をかまさないToList()が極端に遅い原因を知りたい。
プログラム作成中にリスト表示箇所が極端に遅かったため、VisualStudioのデバッグ機能にて問題個所を調べたところ
ToList()で極端に遅くなっていることに気づきました。
using (var db = new AppDbContext())
{
Decimal pKey1 = XXX;
Decimal pKey2 = XXXXX;
Decimal pCode = XX;
var r1 = db.V_DATA
.Where(s => s.Key1 == pKey1)
.Where(s => s.Key2 == pKey2)
.Where(s => s.Code <= pCode)
.ToList(); // ここで7,363ミリ秒(7秒程)かかる
}
============================
/// Entityクラス
public partial class V_DATA
{
[Key]
[Column(Order = 0, TypeName = "numeric")]
public decimal Key1 { get; set; }
[Key]
[Column(Order = 1, TypeName = "numeric")]
public decimal Key2 { get; set; }
[Key]
[Column(Order = 2, TypeName = "numeric")]
public decimal Key3 { get; set; }
[Key]
[Column(Order = 3)]
public string Column4 { get; set; } // 最大
[Key]
[Column(Order =4 , TypeName = "numeric")]
public decimal Column5 { get; set; }
[Key]
[Column(Order = 5, TypeName = "numeric")]
public decimal Column6 { get; set; }
}
ToList()で取得される件数は100件。試しにToList()直前に生成されたSQLをSSMSから実行しましたが
ほぼ0秒で結果が返ってきたためクエリチューニングの問題ではないように思えました。
var r1 = db.V_DATA
.Where(s => s.Key1 == pKey1)
.Where(s => s.Key2 == pKey2)
.Where(s => s.Code <= pCode)
.Selct(s => s); // デバッグモードで停止してSql文字列を取得
以下の点についてご教示をお願い致します。
- Take()無しで速度を向上する方法を知りたい。
- ToList()のみで極端に遅くなる理由を知りたい。
なお、V_DATAはSQLServerデータベースにおいてViewとして定義されています。
var r1 = db.V_DATA
.Where(s => s.Key1 == pKey1)
.Where(s => s.Key2 == pKey2)
.Where(s => s.Code <= pCode)
.Take(100) // ToList()で取得される件数と同数の100を指定
.ToList(); // 早くなった。⇒1,112ミリ秒(1秒程)
var r1 = db.V_DATA
.Where(s => s.Key1 == pKey1)
.Where(s => s.Key2 == pKey2)
.Where(s => s.Code <= pCode)
.ToArray(); // ToArray化。同様に遅い。⇒7,576ミリ秒(7秒程)
var r1 = db.V_DATA
.Where(s => s.Key1 == pKey1)
.Where(s => s.Key2 == pKey2)
.Where(s => s.Code <= pCode)
.Any(); // Any()化。早い。⇒49ミリ秒(7秒程)
V_DATAビューは概ね以下の定義になっています。
Column4の最大桁数は11桁、その他数値項目は12桁です。
[dbo].[V_DATA](
[Key1] [numeric](6, 0) NOT NULL,
[Key2] [numeric](7, 0) NOT NULL,
[Key3] [numeric](3, 0) NOT NULL,
[Column4] [nvarchar](max) NOT NULL,
[Column5] [numeric](22, 0) NOT NULL,
[Column6] [numeric](22, 0) NOT NULL,
)
困っています。ご回答お待ちしております。
SSMSのようなDBメンテナンスツールは、パフォーマンスの低下を防ぐために、クエリを実行した際、カーソルの先頭の一部だけを取り出して表示させ、残りはスクロールさせた際にオンデマンドで取り出すことをしたりしなかったりします。
SSMSで最後の結果が表示されるまでスクロールさせた場合どの程度時間がかかりますか？
もしかしたら最終行取得もいい感じに最適化していて速いかもしれません。
この場合、全体で何行取得できていますか？
全体の抽出件数が多ければ、ToList()で相応の時間がかかるのは必然かもしれません。
@s_izumi
Questioner@s_izumi
Questionerよく分かっていなくて、質問に質問を返すようで申し訳ありません。
以下の部分ですけど、
var r1 = db.V_DATA
.Where(s => s.Key1 == pKey1)
.Where(s => s.Key2 == pKey2)
.Where(s => s.Code <= pCode)
.ToList();
(1) 以下のようにしてはまずいのでしょうか?
var r1 = db.V_DATA
.Where (s => s.Key1 == pKey1 && s.Key2 == pKey2 && s.Code <= pCode)
.ToList();
(2) 以下のようにした場合、コンソールはどのような表示になるでしょうか?
var startTime = DateTime.Now;
var r0 = db.V_DATA
.Where(s => s.Key1 == pKey1)
.Where(s => s.Key2 == pKey2)
.Where(s => s.Code <= pCode);
var endTime = DateTime.Now;
Console.WriteLine ($"Where ×3 に要した時間 {endTime - startTime}");
startTime = DateTime.Now;
var r1 = r0.ToList ();
endTime = DateTime.Now;
Console.WriteLine ($"ToList に要した時間 {endTime - startTime}");
@s_izumi
Questioner@s_izumi
Questionerやり取りを拝見して一点だけ。
SQL Serverはデータバッファのキャッシュやクエリプランのキャッシュを再利用します。
そのため1度実行したクエリを全く同じ条件で再度実行すると処理時間が短くなったりします。
従いまして処理時間を計測する場合はキャッシュをクリアして実行された方が宜しいかと思います。
DBCC DROPCLEANBUFFERS
Where()がデータベースクエリーとして処理されるようにTake()もデータベースクエリーとして処理されます。
Transact-SQLではオフセットとリミットの指定はORDER BYの一部なので、暗黙のORDER BYが指定されいる可能性があります。LAG()関数は非決定性なのでクエリーの内容によって結果が変化する可能性があります。Take()ありのログを確認すれば何かわかるかもしれません。
参考：
https://docs.microsoft.com/ja-jp/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/ja-jp/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-ver15
@s_izumi
Questioner実行クエリをログ出力することができることを知りました。
db.Database.Log = Console.Write;
こんなに簡単にコンソール出力ができることは知りませんでした。
早速ログを確認しました。クエリはオーソドックスなものになりましたが、やはり7306ミリ秒かかりました。
で接続を開きましたSELECT
[Extent1].[Key1] AS [Key1],
[Extent1].[Key2] AS [Key2],
[Extent1].[Key3] AS [Key3],
[Extent1].[Column4] AS [Column4],
[Extent1].[Column5] AS [Column5],
[Extent1].[Column6] AS [Column6]
FROM [dbo].[V_DATA] AS [Extent1]
WHERE ([Extent1].[Key1] = @p__linq__0) AND ([Extent1].[Key2] = @p__linq__1) AND ([Extent1].[Key3] <= cast(98 as decimal(18)))
-- p__linq__0: '201903' (Type = Decimal, IsNullable = false, Precision = 6)
-- p__linq__1: '160' (Type = Decimal, IsNullable = false, Precision = 3)
-- 2020/10/29 13:31:29 +09:00
で実行しています-- 7306 ミリ秒で完了しました。結果: SqlDataReader
悩ましいことに、新しい質問が生まれました。
ログに吐かれたクエリをdb.Database.SqlQuery<List<V_DATA>>(/* ここにSQL文字列 */).ToList()で直接実行してみました。
すると170ミリ秒で結果が帰ってきたのです。
2020/10/29 13:40:23 +09:00
で接続を閉じました2020/10/29 13:40:23 +09:00
で接続を開きました SELECT /* 文字数のため省略 */
-- 2020/10/29 13:40:23 +09:00
で実行しています-- 170 ミリ秒で完了しました。結果: SqlDataReader
内部での実行形式が全く違うのでしょうか？
改めてよろしくお願いします。
db.V_DATA.GetType().FullNameとdb.Database.SqlQuery<List<V_ZM_DATA>>(/* SQL */).GetType().FullNameの結果を教えてください
@s_izumi
Questioner@s_izumi
Questioner