none
SQL Serverで大量のデータ(CSV)を取り込みマスタ(台帳)に反映させるスムーズなアーキテクチャについて RRS feed

  • 質問

  • 上位システムから商品マスタ(主に売価変更)が、CSVで送られてきてスムーズに取り込みたいと考えています。
    量は、現在は、500万件ほど送られてきて、最終的には、1500万件の取り込みが予定されています。

    現在は、500万件だとバルクインで30分、商品マスタ反映(変換~INSERT OR UPDATE)に60分ほどかかり、計90分となり困っています。

    なんとか、今の3倍の量の1500万件の取り込みを60分以内に終わらせたいのですが、なにか良い選択、方法、アーキテクチャはあるのでしょうか?

    ご助言を頂ければ幸いです。

    サーバのスペックは、
        仮想ですが、メモリもCPUも好きなだけつめますが、そもそものDISK IOやMEMORY IOがネックでうまくスムーズに処理が出来ていないように思えます。
        4ドライブに分けて配置しているデータベース300GBを、圧縮バックアップすると30GBになり、5分程度で出力可能です。NVMeレベルの性能がでてはいます。

    こちらで考えたこと
         ・Always Onを使ってデータベースを分散して、データの取り込みを分散して行い、同期をかけて統合する。
      ⇒同期処理のレスポンスが怪しくむしろ遅くなるのではと考えています。
          ・データベースを地域や事業単位に構築して、それぞれでマスタ管理して、それぞれでマスタを取り込む。
      ⇒ユーザ要件を満たせていないので採用が厳しい状況です。
          ・SQL Server Integration Services (SSIS) (Scale Out)を使用して上位連携をスケールアウトして取り込む。
              ⇒こういった連携サービス(ETL)は使ったことがないけど大量データの性能は大丈夫なの?
          ・その他って案がありますか?

    補足
        SP処理の中では、インデックスはもちろん、マージ文の使用やトランザクション辺りの処理サイズ(適正処理リミット値)などを意識しております。
    2020年6月17日 2:17

すべての返信

  • bulk insert 処理処理時間を短縮する方法としては、該当データベースが完全復旧モデルの場合、最小ログ記録でパフォーマンスが向上するかを確認すると良いかもしれません。

    一括インポートで最小ログ記録を行うための前提条件
    https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver15

    該当のクエリの何処で時間を要しているかをまずは確認されたほうが良いかと思いますが、Writlog 待ちで時間を要している場合には、トランザクションの遅延持続性により、ログの書き込みを一時的に非同期書き込みにすることで、クエリパフォーマンスを向上できる可能性はあります。

    トランザクションの持続性の制御
    https://docs.microsoft.com/ja-jp/sql/relational-databases/logs/control-transaction-durability?view=sql-server-ver15#delayed-transaction-durability
    + トランザクションの遅延持続性

    • 編集済み NOBTA 2020年6月18日 1:55
    2020年6月17日 15:45
  • 1TBくらいメモリ積んで、In Memory Tableにすればよいのでは。

    jzkey

    2020年6月17日 22:21
  • ありがとうございます。

    復旧モデルは単純(シンプル)を使用しております。

    私は、ミニバッチやフルバッチを実行する時は、一括(バルク)が望ましいと考えていますが、トランザクションログの運用をしない案件となっています。

    2020年6月18日 1:14
  • ありがとうございます。

    メモリ最適化テーブルも使ってみました。(データベースの設定を行い、それようのファイルグループを作成して、WITH(memory_optimized=on)としてテーブルを作成。

    ワークを使用するその後の処理で性能が落ち、全体で効果が得られませんでした。。。主キー以外のキーも索引できないし。

    高速ストレージ(シーケンシャルでGB、IOPS数十万)が入っているとそこまで短縮ができないのかもしれません。あとは、使い方の問題があるかはわかりませんでしたが、レスポンスのログを入れて処理を分解して性能比較していくとメモリ最適化テーブルを使用した処理(更新系)で極端に遅くなる部分があるようです。

    2020年6月18日 1:24
  • トランザクションの遅延持続性は、復旧モデルが単純な場合においても効果がある設定になります。ただ、設定有効時に SQL Server プロセスが仮に異常終了してしまった場合、データを損失する可能性があるため、パフォーマンス改善とのトレードオフとなりますが。

    トランザクションの持続性の制御
    https://docs.microsoft.com/ja-jp/sql/relational-databases/logs/control-transaction-durability?view=sql-server-ver15#delayed-transaction-durability
    + トランザクションの遅延持続性

    2020年6月18日 1:57
  • ありがとうございます。これは効果がありそうですね。試してみます。

    2020年6月22日 0:47
  • kiyo7447さん、こんにちは。フォーラムオペレーターのクモです。 
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    ご質問いただいた件ですが、その後いかがでしょうか。
    NOBTAさんから寄せられた投稿はお役に立ちましたか。

    参考になった投稿には [回答としてマーク] をお願い致します。

    設定いただくことで、
    他のユーザーもお役に立つ回答を見つけやすくなります。

    お手数ですが、ご協力の程どうかよろしくお願いいたします。

    MSDN/ TechNet Community Support Kumo ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2020年6月24日 2:11
    モデレータ
  • 回答が遅くなりすみません。実施してみて「トランザクションの持続性の制御」を試して効果はありました。

    update 約70万件×2回 12:30⇒12:16            2%up  
    insert
    約70万件 ×2回 9:29⇒9:10                 3%up 

    設定はデータベース単位に実施しています。alter database ...で設定。
    効果があれば、対象のストアドだけ対応と考えていましたが、僅かな改善だったので、採用を見送りました。

    2020年6月26日 7:51
  • kiyo7447さん、こんにちは。フォーラムオペレーターのクモです。
    ご返信いただきありがとうございます。

    以下の投稿が参考になるかもしれません。
    Handling BULK Data insert from CSV to SQL Server

    Import Data from 48 GB csv File to SQL Server

    どうぞよろしくお願いいたします。 

    MSDN/ TechNet Community Support Kumo ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2020年6月30日 8:19
    モデレータ