38193


次のようなコレクションがあったとします。

[code]

    List<Item> itemlist = new List<Item>();
    itemlist.Add(new Item(){Id = 1, Name = "A"});
    itemlist.Add(new Item(){Id = 2, Name = "B"});
    itemlist.Add(new Item(){Id = 3, Name = "C"});
    
    class Item
    {
      int Id {get;set;}
      string Name{get;set;}
      int value {get;set;}
    }
    
[/code]

また、SQL Serverのテーブルには以下のレコードが10M件あるとします。

[code]

    ------------------------
    |Id | DateTime | Value |
    ------------------------
    |1 | 2017-09-26 13:03 | 9|
    ------------------------
    |1 | 2017-09-26 13:03 | 5|
    ------------------------
    |1 | 2017-09-26 13:03 | 2|
    ------------------------
    .
    .
    .
    |3 | 2017-01-01 00:01 | 11|
    ------------------------
    
[/code]

このデータベースから該当Itemの最新データを取得する効率のよい方法をご教授いただきたいです。

[code]

    foreach(var n in itemlist)
    {
     n.value = table.where(c=>c.Id = n.Id).OrderByDescending(c=>c.DateTime).Select(c=>c.value).FirstOrDefault();
    }
    
[/code]

この方法で取得すると、レコードが多い場合、OrderbyDescendingがあるせいかとても遅くなります。。

何か他によい方法がございますでしょうか。

.NET Framework4.0の環境で開発しています。  
よろしくお願いします。


CC BY-SA 3.0
2017-09-26T01:48:12.100
38189
2017-09-26T09:24:53.820
2017-09-26T09:24:53.820
4236
12388
question
c#
Collectionの中から効率よく目的のデータを取得したい

[code]

    CREATE NONCLUSTERED INDEX [index_name] ON [table_name]
    (
        [Id] ASC,
        [DateTime] DESC
    )
    INCLUDE (
        [Value]
    )
    
[/code]

DateTime列について降順でソートされたインデックスを作成してください。

加えて、itemlistの要素数だけSQLを発行することを止めて、Idで集計(`group by [Id]`)した結果を利用することも検討の余地があります。


CC BY-SA 3.0
2017-09-26T02:12:03.517
38191
2017-09-26T02:17:45.050
2017-09-26T02:17:45.050
2238
2238
38189
answer


インデックスを追加するべきですが、次善の策として一度`Max(c.DateTime)`を求めてから`Where(c => c.DateTime ==
maxDateTime)`でフィルタリングするという二段階に分割する手もあります。この場合計算量はO(n)×2ですのでソートのO(nlogn)よりは高速になります。ただし通信回数が増えますので、元のクエリーが100ms程度であれば逆に遅くなる可能性もあります。


CC BY-SA 3.0
2017-09-26T02:23:52.813
38192
2017-09-26T02:23:52.813
5750
38189
answer


htbさんの

> Idで集計(`group by [Id]`)した結果を利用する

ですがコードで書くと

[code]

    var idValue = table
        .GroupBy(c => c.Id, (Id, g) => new { Id, DateTime = g.Max(c => c.DateTime) })
        .Join(table, id => id, c => new { c.Id, c.DateTime }, (_, c) => new { c.Id, c.Value })
        .ToDictionary(c => c.Id, c => c.Value);
    
[/code]

とすることで`Id => Value`のマッピング辞書を構築できます。あとは

[code]

    foreach(var n in itemlist)
        n.value = idValue[n.Id];
    
[/code]

と辞書を引くだけです。


CC BY-SA 3.0
2017-09-26T03:19:23.720
38193
2017-09-26T03:19:23.720
4236
38189
answer


`itemlist`をループで回して複数回検索していますが、これは典型的にはアンチパターンです。接続やSQLの解析実行のオーバーヘッドもループ回数分増えることになります。

Idのリストを集めて、1回で検索するようにするようにしてください。ただ、若干SQLが面倒です。(C#で同じ物がかけるかどうかはわかりません)

[code]

    SELECT Id, Value FROM 
       (SELECT Id, DateTime, Value, 
               ROW_NUMBER() OVER (PARTITION BY Id, ORDER BY DateTime DESC) AS row_num 
        FROM Table Where Id IN (?, ?, ?, ?) WORK 
      WHERE WORK.row_num = 1
    
[/code]

典型的には自己結合が使われていたクエリですが、ウィンドウ関数が使える環境ではそちらを使う方が一般的にパフォーマンスが良いです。

また、Id、DateTimeの比率によってはインデックスで性能が改善出来るかもしれません。闇雲にインデックスを張ると更新などのオーバーヘッドになりますので、EXPLAINで実行計画を確認して効果の有無をかならず確認してください。


CC BY-SA 3.0
2017-09-26T04:17:39.973
38196
2017-09-26T04:17:39.973
5793
38189
answer