Dapperを用いたマッピングとデータベースのリレーショナルについて
投稿者 (削除されました) ()
投稿日時
2023/2/27 16:23:38
(削除されました)
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2023/2/27 16:26:54
> テーブルやクラス、プロパティの関係性は
> A:B(α:β) = 1:n
> B:C(β:γ) = 1:n
> という状態です
こういう階層イメージでしょうか?
> 果たしてこのやり方がパフォーマンス的にベストなのか疑問に思いながらコーディングしていました
一括挿入なら、Dapper Plus で Bulk Insert するという手がありますね。
https://dapper-plus.net/pricing
> この方法について皆さんはどう思われますか？
XML 化がボトルネックになりそうな気もするので、
SelectMany を用いて、各テーブル向けにフラット化して渡すようにするとか…。
(ストアド実行ではなくなってしまいますが)
> A:B(α:β) = 1:n
> B:C(β:γ) = 1:n
> という状態です
こういう階層イメージでしょうか?
Class α
Public Property Identity As Integer 'αの主キー
Public Property MemberOfAlpha As String 'αのメンバー
Public Property Children As List(Of β)
End Class
Class β
Public Property Identity As Integer 'βの主キー
Public Property MemberOfBeta As String 'βのメンバー
Public Property Children As List(Of γ)
End Class
Class γ
Public Property Identity As Integer 'γの主キー
Public Property MemberOfGamma As String 'γのメンバー
End Class> 果たしてこのやり方がパフォーマンス的にベストなのか疑問に思いながらコーディングしていました
一括挿入なら、Dapper Plus で Bulk Insert するという手がありますね。
https://dapper-plus.net/pricing
> この方法について皆さんはどう思われますか？
XML 化がボトルネックになりそうな気もするので、
SelectMany を用いて、各テーブル向けにフラット化して渡すようにするとか…。
(ストアド実行ではなくなってしまいますが)
connection.Execute(queryInsertAlpha, listOfAlpha)
connection.Execute(queryInsertBeta, listOfAlpha.SelectMany(Function(a) a.Children.Select(
Function(b) New With {
Key .aID = a.Identity,
Key .bID = b.Identity,
b.MemberOfBeta
}))
)
connection.Execute(queryInsertGamma, listOfAlpha.SelectMany(Function(a) a.Children.SelectMany(Function(b) b.Children.Select(
Function(g) New With {
Key .aID = a.Identity,
Key .bID = b.Identity,
Key .gID = g.Identity,
g.MemberOfGamma
})))
)
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2023/2/27 18:17:36
INSERT を繰り返し呼ぶのは遅いので、高速化ならやっぱり BULK INSERT ですね。
Dapper Plus 以外だと、 Dapper.Bulk というものもあるようです。
速度比較の記事が見当たらないので、有効性は不明。
https://github.com/KostovMartin/Dapper.Bulk
参考：
https://stackoverflow.com/questions/28856802/best-way-to-do-bulk-inserts-using-dapper-net
Dapper Plus 以外だと、 Dapper.Bulk というものもあるようです。
速度比較の記事が見当たらないので、有効性は不明。
https://github.com/KostovMartin/Dapper.Bulk
参考：
https://stackoverflow.com/questions/28856802/best-way-to-do-bulk-inserts-using-dapper-net
投稿者 SSD (社会人)
投稿日時
2023/2/27 18:27:36
> こういう階層イメージでしょうか?
おおよそそんな感じです。
ただIdentityの値はあらかじめインスタンスで保持しているわけではなく、
テーブルにレコードをInsertした際にテーブル内のIdentityに設定している列に自動的に入ります。
提示いただいたSelectManyを用いる場合はそれぞれのクエリの実行の間に、
Insertした後に確定した各レコードのIndentityの値を取得するクエリの実行が必要になります。
αはコレクションではない単一のインスタンスで、
Insert文の実行をした後にSelect文の実行をしてIdentityの値を取得しています。
ただβとγはコレクションのコレクションというような関係で、
αと同じようなことをするとデータベースへの接続を何度も繰り返すことになり非効率なのかなと思い、
ストアドプロシージャの中でβのレコードを1行Insertし、@@IdentityでそのβのレコードのIdentityを取得した後にγ(XMLからテーブルに変換)のレコードを複数行Insertしている感じです。
> 一括挿入なら、Dapper Plus で Bulk Insert するという手がありますね。
Executeとの処理速度の比較表を見るととてつもなく速いですね
https://dapper-plus.net/bulk-insert
ただお値段が結構しますね..
Bulk Insertの場合であっても1:nの関係で、親のIdentityの値を子のレコードに入れる場合、
・親のレコードをInsert
・確定した親レコードのIdentityを取得
・親のIdentityの値を加えて子のレコードをInsert
を繰り返すことになりInsertとIdentityの取得で接続と切断が繰り返されてしまい、パフォーマンスが悪いのかなと思ってあまり考慮していませんでした
Identityを用いてこのようにリレーショナルにすること自体がそもそもおかしいんでしょうか？
おおよそそんな感じです。
ただIdentityの値はあらかじめインスタンスで保持しているわけではなく、
テーブルにレコードをInsertした際にテーブル内のIdentityに設定している列に自動的に入ります。
提示いただいたSelectManyを用いる場合はそれぞれのクエリの実行の間に、
Insertした後に確定した各レコードのIndentityの値を取得するクエリの実行が必要になります。
αはコレクションではない単一のインスタンスで、
Insert文の実行をした後にSelect文の実行をしてIdentityの値を取得しています。
ただβとγはコレクションのコレクションというような関係で、
αと同じようなことをするとデータベースへの接続を何度も繰り返すことになり非効率なのかなと思い、
ストアドプロシージャの中でβのレコードを1行Insertし、@@IdentityでそのβのレコードのIdentityを取得した後にγ(XMLからテーブルに変換)のレコードを複数行Insertしている感じです。
> 一括挿入なら、Dapper Plus で Bulk Insert するという手がありますね。
Executeとの処理速度の比較表を見るととてつもなく速いですね
https://dapper-plus.net/bulk-insert
ただお値段が結構しますね..
Bulk Insertの場合であっても1:nの関係で、親のIdentityの値を子のレコードに入れる場合、
・親のレコードをInsert
・確定した親レコードのIdentityを取得
・親のIdentityの値を加えて子のレコードをInsert
を繰り返すことになりInsertとIdentityの取得で接続と切断が繰り返されてしまい、パフォーマンスが悪いのかなと思ってあまり考慮していませんでした
Identityを用いてこのようにリレーショナルにすること自体がそもそもおかしいんでしょうか？
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2023/2/28 09:48:08
> ただお値段が結構しますね..
BULK INSERT 自体はデータベース側の機能なので、
Dapper によるサポートが不要なら自分で呼び出す選択肢もあります。
あるいは後から紹介した、無料の Dapper.Bulk というものもありますが、Dapper.Plus と違って
>> 速度比較の記事が見当たらない
ので、こちらはその有効性が判断しにくいところ。
> テーブルにレコードをInsertした際にテーブル内のIdentityに設定している列に自動的に入ります。
Indentityによる自動採番だとして、α,β,γの主キー構造が単一主キーなのか、
それとも複合主キーなのか読み解けませんでした。
（βとγの繋がりが、データーベース的にどういうテーブル構成になっているのか?）
> αと同じようなことをするとデータベースへの接続を何度も繰り返すことになり非効率なのかなと思い、
IDENT_CURRENT / SCOPE_IDENTITY / @@IDENTITY 等を繰り返すような
カーソル処理・ループ処理は、SQL とは相性が悪いので、基本的には
一括処理できる設計で検討した方が良いとは思いますね。
データ量などいろいろな要件に左右されるので、一概には言えない所もありますが。
> Insert文の実行をした後にSelect文の実行をしてIdentityの値を取得しています。
複数テーブルあるいは複数レコードにまたがる自動採番の場合、
たとえば「予約番号」を使うという手法があります。
やり方は幾つかありますが、たとえば採番が 1～9999999999 という範囲で生成されるのなら、
挿入予定のデータ側には、それと被らない -1, -2, -3, -4, …… といった連番で割り当て、
それを一括登録した後、トランザクション処理の中で、負数を実際の採番値に
置き換えてからコミットするようにします。
（この手法を採る場合は、IDENTITY よりも SEQUENCE の方が扱いやすいかもしれません）
もし、VB 側が List や Array で管理されているのなら、
そこに連番を振るためのコストは、ほぼ無視できるでしょう。
しかし、後からキーを置換するというこの方法では、インデックスに対するロックが
広くかかりすぎてしまうという欠点もあります。
それを避ける場合、予約番号をコミット時に後から決めるのではなく、その逆に
先に必要な数だけ、データ登録準備段階で採番値の一覧をサーバー側に発行させてしまい、
それをローカル側で割り当ててから BULK INSERT とするというやり方もあります。
こちらの手法であれば、ロックも最小限で済みますし、tempdb への負荷も少なくて済みます。
その代わり、データベース側から採番値を事前に取得する手間が追加されるため、
実際には 3 層アーキテクチャ型のアプリケーション設計にしておき、ビジネスロジック層で
この変換処理を行うような実装とします。
BULK INSERT 自体はデータベース側の機能なので、
Dapper によるサポートが不要なら自分で呼び出す選択肢もあります。
あるいは後から紹介した、無料の Dapper.Bulk というものもありますが、Dapper.Plus と違って
>> 速度比較の記事が見当たらない
ので、こちらはその有効性が判断しにくいところ。
> テーブルにレコードをInsertした際にテーブル内のIdentityに設定している列に自動的に入ります。
Indentityによる自動採番だとして、α,β,γの主キー構造が単一主キーなのか、
それとも複合主キーなのか読み解けませんでした。
（βとγの繋がりが、データーベース的にどういうテーブル構成になっているのか?）
> αと同じようなことをするとデータベースへの接続を何度も繰り返すことになり非効率なのかなと思い、
IDENT_CURRENT / SCOPE_IDENTITY / @@IDENTITY 等を繰り返すような
カーソル処理・ループ処理は、SQL とは相性が悪いので、基本的には
一括処理できる設計で検討した方が良いとは思いますね。
データ量などいろいろな要件に左右されるので、一概には言えない所もありますが。
> Insert文の実行をした後にSelect文の実行をしてIdentityの値を取得しています。
複数テーブルあるいは複数レコードにまたがる自動採番の場合、
たとえば「予約番号」を使うという手法があります。
やり方は幾つかありますが、たとえば採番が 1～9999999999 という範囲で生成されるのなら、
挿入予定のデータ側には、それと被らない -1, -2, -3, -4, …… といった連番で割り当て、
それを一括登録した後、トランザクション処理の中で、負数を実際の採番値に
置き換えてからコミットするようにします。
（この手法を採る場合は、IDENTITY よりも SEQUENCE の方が扱いやすいかもしれません）
もし、VB 側が List や Array で管理されているのなら、
そこに連番を振るためのコストは、ほぼ無視できるでしょう。
しかし、後からキーを置換するというこの方法では、インデックスに対するロックが
広くかかりすぎてしまうという欠点もあります。
それを避ける場合、予約番号をコミット時に後から決めるのではなく、その逆に
先に必要な数だけ、データ登録準備段階で採番値の一覧をサーバー側に発行させてしまい、
それをローカル側で割り当ててから BULK INSERT とするというやり方もあります。
こちらの手法であれば、ロックも最小限で済みますし、tempdb への負荷も少なくて済みます。
その代わり、データベース側から採番値を事前に取得する手間が追加されるため、
実際には 3 層アーキテクチャ型のアプリケーション設計にしておき、ビジネスロジック層で
この変換処理を行うような実装とします。
投稿者 (削除されました) ()
投稿日時
2023/2/28 09:48:09
(削除されました)
投稿者 SSD (社会人)
投稿日時
2023/3/1 09:49:39
魔界の仮面弁士 様
> α,β,γの主キー構造が単一主キーなのか、それとも複合主キーなのか
単一主キーです
言及していたクラスは
という感じでVBの中ではαは単一のインスタンス、βはコレクションの要素としています
テーブルAにα1,α2を記録(Identity=α1)
テーブルBにβ1,α1,β2を記録(Identity=β1)
テーブルCにγ1,β1,β3の要素を記録(Identity=γ1)
A,B,Cでの主キーはそれぞれα1,β1,γ1です
γ1はVB側では使用していません
(β3が当初のγに該当します)
意味合いとしてはαが見出し、βが明細、β3が各明細の備考という感じです
> 先に必要な数だけ、データ登録準備段階で採番値の一覧をサーバー側に発行させてしまい
Identityの予約で調べてもそれらしきものが出てこなかったので方法が分からないのですが、
これはあらかじめ空のレコードをコレクションの数だけInsertしてそのIdentityの値を取得するということでしょうか？
Bulk Insertを実際に使用したことがないのですが、
調べたところテキストファイルなどから一括でテーブルにInsertする機能というところまではわかりました。
今回の場合で言うとインスタンスのプロパティー値からテキストファイルを作成し、
それをBulk Insertするというイメージでしょうか？
> α,β,γの主キー構造が単一主キーなのか、それとも複合主キーなのか
単一主キーです
言及していたクラスは
Class α
Public Property α1 As Integer
Public Property α2 As String
End Class
Class β
Public Property β1 As Integer
Public Property β2 As String
Public Property β3 As List(Of String)
End Classという感じでVBの中ではαは単一のインスタンス、βはコレクションの要素としています
テーブルAにα1,α2を記録(Identity=α1)
テーブルBにβ1,α1,β2を記録(Identity=β1)
テーブルCにγ1,β1,β3の要素を記録(Identity=γ1)
A,B,Cでの主キーはそれぞれα1,β1,γ1です
γ1はVB側では使用していません
(β3が当初のγに該当します)
意味合いとしてはαが見出し、βが明細、β3が各明細の備考という感じです
> 先に必要な数だけ、データ登録準備段階で採番値の一覧をサーバー側に発行させてしまい
Identityの予約で調べてもそれらしきものが出てこなかったので方法が分からないのですが、
これはあらかじめ空のレコードをコレクションの数だけInsertしてそのIdentityの値を取得するということでしょうか？
Bulk Insertを実際に使用したことがないのですが、
調べたところテキストファイルなどから一括でテーブルにInsertする機能というところまではわかりました。
今回の場合で言うとインスタンスのプロパティー値からテキストファイルを作成し、
それをBulk Insertするというイメージでしょうか？
投稿者 魔界の仮面弁士 (社会人)
投稿日時
2023/3/1 10:47:21
番号を予約する手法を採る場合は、IDENTITY よりも SEQUENCE の方が向いています。
複数の番号を予約するための sp_sequence_get_range も使えますしね。
https://learn.microsoft.com/ja-jp/sql/t-sql/statements/create-sequence-transact-sql?WT.mc_id=DT-MVP-8907&view=sql-server-ver16
https://learn.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-sequence-get-range-transact-sql?WT.mc_id=DT-MVP-8907&view=sql-server-ver16
あるいは UNIQUEIDENTIFIER型 にしておいて、NEWID() 等で生成する手法もあります。
IDENTITY を使う場合、sp_sequence_get_range に相当する機能が無いため、
案1) 1 行ずつ挿入して、 IDENT_CURRENT / SCOPE_IDENTITY / @@IDENTITY を都度取得
案2) ワークテーブルに複数行挿入し、トリガーでどうにかする
などの手間がかかってしまうので、自分は敬遠しています。
ID 自体に意味のない、アクセスログの記録なんかには便利なのですが、
外部キーとして使うのには向いていないと思います。
🔹 @@IDENTITY
現在のセッション内で、
どのテーブルで生成されたかに関わらず、
最後にインサートされた IDENTITY の値を返す。
🔹 SCOPE_IDENTITY()
現在のセッション内かつ現在のスコープ内で、
どのテーブルで生成されたかに関わらず、
最後にインサートされた IDENTITY の値を返す。
🔹 IDENT_CURRENT('table_name')
セッションやスコープに関わらず、
指定したテーブルに対して
最後に生成された IDENTITY の値を返す。
複数の番号を予約するための sp_sequence_get_range も使えますしね。
https://learn.microsoft.com/ja-jp/sql/t-sql/statements/create-sequence-transact-sql?WT.mc_id=DT-MVP-8907&view=sql-server-ver16
https://learn.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-sequence-get-range-transact-sql?WT.mc_id=DT-MVP-8907&view=sql-server-ver16
あるいは UNIQUEIDENTIFIER型 にしておいて、NEWID() 等で生成する手法もあります。
IDENTITY を使う場合、sp_sequence_get_range に相当する機能が無いため、
案1) 1 行ずつ挿入して、 IDENT_CURRENT / SCOPE_IDENTITY / @@IDENTITY を都度取得
案2) ワークテーブルに複数行挿入し、トリガーでどうにかする
などの手間がかかってしまうので、自分は敬遠しています。
ID 自体に意味のない、アクセスログの記録なんかには便利なのですが、
外部キーとして使うのには向いていないと思います。
🔹 @@IDENTITY
現在のセッション内で、
どのテーブルで生成されたかに関わらず、
最後にインサートされた IDENTITY の値を返す。
🔹 SCOPE_IDENTITY()
現在のセッション内かつ現在のスコープ内で、
どのテーブルで生成されたかに関わらず、
最後にインサートされた IDENTITY の値を返す。
🔹 IDENT_CURRENT('table_name')
セッションやスコープに関わらず、
指定したテーブルに対して
最後に生成された IDENTITY の値を返す。
投稿者 SSD (社会人)
投稿日時
2023/3/9 08:52:07
魔界の仮面弁士 様
Identityは向いていないんですね。
ご回答ありがとうございました。
Identityは向いていないんですね。
ご回答ありがとうございました。
SQL Serverには
親テーブルA
子テーブルB
孫テーブルC
というテーブルがあり、それぞれにIdentityとなる値があります
また、AのIdentityの値をBのレコードに、
BのIdentityの値をCのレコードに記録してリレーショナルにしています
それぞれ
A:= クラスαのプロパティ値を記録するためのテーブル
B:= クラスβのプロパティ値を記録するためのテーブル
C:= クラスβのプロパティγ(コレクション)の値を記録するためのテーブル
となっておりテーブルやクラス、プロパティの関係性は
A:B(α:β) = 1:n
B:C(β:γ) = 1:n
という状態です
ABCにレコードを記録する場合以下のようにしています
(1) αのプロパティー値をDapperのパラメータに渡す
(2) Aに(1)の値でレコードを1行Insert
(3) (1)でInsertしたレコードのIdentityの値を取得
(4) (3)の値とクラスβのコレクションの各要素からパラメータ値を抽出してDapperのパラメータに渡す
(5) (4)の際にγはコレクション(List(Of String)))からXML(String型)に変換してパラメータに渡す
(6) Bに(4)の値で1行Insert(γの値以外)
(7) (6)でInsertしたレコードのIndentity値を取得
(8) γのXMLをテーブルに変換し(7)の値と合わせてCに複数行Insert
(9) (6)～(7)をDapperが繰り返す
上記はストアドプロシージャの実行で済ませています
(5)について、SQL Serverではコレクションや配列を受け取ってくれる変数がないのでXMLに変換しています
(9)について、(4)でパラメータをコレクションにしてDapperに渡すとコレクションの数だけDapperがInsertしてくれます
期待している通りの挙動はしてくれるのですが、
1:nの関係が親テーブルから孫テーブルまであり、
それぞれのテーブルのIdentityの値をもとにリレーショナルにしている場合に、
果たしてこのやり方がパフォーマンス的にベストなのか疑問に思いながらコーディングしていました
この方法について皆さんはどう思われますか？
また、どのような方法がベストだと思われますか？
ご意見いただければ幸いです