外部リンクデータで範囲絞り、インデックスの有無、速度について

普通のオンラインタスク

データはSQL Server

メインソースに　別データを　外部リンクしています。

外部リンクデータの項目で範囲絞りを行うとします。

この時、外部リンクデータに
絞込み対象項目でインデックスが用意されているのと
いないのとでは速度に大きな違いは出るものでしょうか？

-----------
外部リンクしたデータで範囲絞りするよりも、
メインソースの中にデータを保持して、適切なインデックス
を用意する方が速度は向上するものでしょうか？

全社共通商品コードに対して、拠点別に仕入先コードを
設定出来るようにする場合、商品コード＋拠点コードの
データを別途用意するべきか、商品マスタの中に
拠点分だけ仕入先コードを登録する枠を用意すべきか悩みます。


メインソースの代わりに、SQLコマンドを指定するようにすれば
どのデータを主体に表示するかも切り替えられるように
出来るのでしょうね。（タスクは１個のままで）

nkmtさん
こんにちは

インデックスについては、私もよく分かりませんが、速度に大きな違いは出るかについては、
データ量によります。基準は？と聞かれるとよく分かりません。
データベース内に、インデックス用のエリアが設けられると思いますので、ソートや抽出は早くても、挿入や更新が遅くなったり、データベースの容量が大きくなったりと、デメリットもあると思われます。

私の場合は、プライマリ以外はキーは作成しません。
必要な場合はMAGICで仮キーにします。
レコードメインを仮キーで設定しても、速度に支障はありません。
想定されるレコード件数のダミーデータを作成し、テストして遅いと感じた時、パフォーマンスチューニングを行われると思います。その時の最後の手段でいいと思います。

ちなみに、後付けでインデックスを追加して、劇的に高速になったこともあります。

Btrieveはインデックスありきでしたし、外部リンクでの範囲指定は、ご法度でしたね。癖でSqlServerでも使いませんが、そんなに速度に影響はなかったと思います。


１人で開発している個人的な意見ですので、参考になるかどうか…。

nkmtさん　こんにちは

fjksudouさんんも書かれているかと思いますが
SQLserverの場合　データ件数、index等を考慮して
オプティマイザー が催促で抽出できる実行計画を立てて
実行してくれます。

基本、私は外部リンクしたデータで範囲絞りします。
なぜなら　正規化を重視しているからです。

速度が遅くなった時、チューニングする手段を取ってます。
どちらが正解というのはないかと思います。
でわ〜でわ〜

fjksudou様、pu様、レスありがとうございます。
感謝します。

Puさんの正規化を重視する・・・・という部分をお教えください。

Btrieve時代の名残で、売上伝票鑑の項目
「売上日、得意先コード、営業所コード、営業担当コード」などを
売上明細データにも　重複して　保持する事がよく有ります。

これは正規化に反する、データの容量が無駄に大きくなりSQLの利点を殺しているといった事なのでしょうか？

正規化を突き詰めていない私です。

売上伝票データ　と
売上明細データ　で項目の重複をしないようにしたとした場合


売上明細の商品コード別に売上金額を集計し、
外部リンクで売上伝票を読み、絞込みは売上伝票データの売上日でインデックスがあれば
売上伝票の売上日での絞込みといった事もSQLは得意であるという事なのでしょうか。

正規化の例では、売上伝票と売上明細の事で書きましたが
今回の悩み事は、10万件の商品マスタがあり、
営業所毎に主仕入先が異なる以下の件です。

商品マスタは、商品コードでインデックスを用意。

商品別営業所別　主仕入先情報は
　商品コード＋営業所コードで主インデックスを用意しつつ
　主仕入先コードで　重複可　のインデックスも用意。

10万件の商品マスタを一覧表示するマスタメンテナンスPG
で商品別営業所別　主仕入先情報を外部リンクし、
主仕入先コードで　範囲絞り　を行う。

全商品　全営業所　に　主仕入先が登録されていれば
10万件×営業所数分だけのレコードが有りますが、実際には
一部の商品、一部の営業所にだけ　主仕入先が登録されている
といった具合です。

主仕入先で絞りたいのであれば、
商品マスタをメインソースにせずに
商品別営業所別　主仕入先情報をメインソースにした
タスクを別途用意した方がベターなのですかね？

仕入先によっては、10万件の商品のうち50商品しか登録がない
といった事も有るかもしれませんので、見付けにいく量が
圧倒的に違うと思いまして。

Magicの普通の作りでは全然作りの異なるデータで
メインソースの切替は不可能ですが、SQLコマンドにすれば
それも可能なので、速度面も活かしつつ、タスクも分けずに
一つで済み、かつ速度面も問題ない利点も有るのでしょうね。



指定した仕入先で商品登録がない時などに時間がかかるような
気がするので、検索へ行く前の仕入先コードを指定した時点で
商品別営業所別　主仕入先情報を照会リンクして
登録がない場合には、商品表示タスクへ
突入しないようにしたいと思います。

正規化を突き詰めていき、外部リンクや
場合によってはSQLコマンドを多用すれば速度面も
問題ないのかもしれないなという事なのかな、と想像しつつ
この件、継続して実験していきたいと思います。

Btrieveでデータベースを設計するとき、正規化にならないため、いつも違和感がありました。
わざわざ明細テーブルに仕入先コードを設け、同じ内容を書き込んでいた記憶があります。
ISAM系なので、第二正規化までと考えていました。

SQLになってからは完全に正規化しています。
同じ項目を設ける必要が無いため、バグの要因が一つ消えます。

ちなみにSQL分の書き方によって、同じ結果でも速度が異なります。

また、列数が多い場合、速度を遅く感じます。
他にテーブルを作って結合した方が早い場合がありますので、私の場合は用途別にした１対１のテーブルもあります。

fjksudou様、レスありがとうございます。
SQLになってから完全に正規化していらっしゃるのですね。
情報ありがとうございます。
今後新しく作る分はそのようにしてみたいと思います。

売上明細データに、あまり打ち変えないけど
商品名も保持することが有ります。
60バイトとか100バイト消費するんですよね。

売上伝票鑑データにも　いちげん様ように　手入力お客様名の枠や
鑑備考などを持ったり・・・売上明細にも明細備考を持つなど。

あまり入力されないそういった情報も別データ化した方が
速度面で有利なのでしょうね。

伝票入力PGなどでエントリーの際に、各種本物データから
エントリー用メモリWFに、寄せ集め、
保存時にそれぞれのデータへ書き戻すといった対応も
検討したいと思います。

nkmtさん　こんにちは

私の返信内容もfjksudouと全く同じです。
Btrieve時代は明細にも抽出用の項目を持っていました。

RDBにしてからjoinを多用しますので
正規化に意識を集中して設計できるので
気持ちがスッキリします。

但しDWHのスタースキーマーやキーバリュー等はまた
考え方は異なりますが．．．

でわ〜でわ〜

puさんレスありがとうございます。

RDBにしてからjoinを多用とありますが
Magic上は普通にメインソースと外部リンク／結合リンク
を使った記述、あるいはSQLコマンドを使っての処理など
どちらにもメリットがあるので使い分けておられますか？

売上伝票と売上明細で正規化したデータを作ってみて
色々と実験してみたいと思います。

nkmtさん　こんにちは

メンテナンス性はを考慮したら
基本は外部リンク／結合リンク
(INNERE JOIN/OUTER JOIN)です

よっぽど速度を元寝られる時に
埋め込みSQLです

その上がストアドでしょうね。

でわ〜でわ〜

売上明細から売上鑑を外部リンクして
外部リンクした　売上鑑の売上日を　普通に範囲で絞る方法、
DB SQL:に書く方法両方試せました。

メインソースの項目は、普通に範囲指定を行い、
外部リンクした売上鑑の日付はDB SQLで指定するなど
混在での範囲指定も有りなのでしょうね。

売上明細の件数があまり多くなかったり
高速SSDのPCだと速度比較のテストにもならないですね。

数百万明細のテストデータを作り、ハードDISKのPCでの
実験が必要だと感じました。

SQLコマンドについて

基本的にはMAGICのリンクを使用しますが、
SQLのCASE文が優秀で高速なので、
その時に「JOIN」や「OUTER JOIN」を使います。
他にも複数の「LIKE」等の、複雑なWHERE条件の場合にSQLを使用します。

また、計算や集計した結果を使用する場合もSQLですね。
SQLのエディタで、集計結果がすぐ出ます。
後はMAGICでAPGして、ちょちょちょっと手間を加えるだけです。


SQLを使うことで、開発工数がとても減って、愕然とした記憶があります。

fjksudou様 レスありがとうございます。

SELECT SUM（金額）といった単純なSQLならわかりますが、JOINや複雑な条件になると今の私にはSQL文を書けないので、ACCESSでクエリー作ってSQLコマンドを理解するなどしたいと思います。

ACCESSで書かれるSQL文とSQL Serverでは異なる部分も有るのかもしれませんね。

それとSQL Server 2008 R2 で動いていたSQLコマンドがSQL Server 2014 や 2016 では動かないとかそういった心配は有るのですかね？

SQLServer、Oracle、Access等、若干記述方法が異なります。
バージョンの違いについては少しありますが、今はそんなに意識しなくても大丈夫だと思います。

私はSELECT文くらいしかSQL文は書けません。
基本的な内容は書籍にありますし、ぐぐれば直ぐに出てきます。

CASE、DISTINCT、UNIONなどMAGICでは一発回答で出てこないものがありますし、FROM句や外部リンクテーブルをSELECT文にするとか…。

ちなみにAccessのクエリーはお勧めしません。記述が結構違います。
勉強するなら、MAGICで作成した既存の帳票プログラムを、SQLで記述してみることをお勧めします。余計な子タスクなどは無くなります。

場合にもよりますが、新規案件もSQLで一歩を踏み出しましょう。

不明な場合はフォーラムで！

fjkusudou様、アドバイスをありがとうございます。
SQLをもっと学びたいと思います。
既存帳票をSQLで簡素化する練習がいいですね。

また話を戻すようですみませんがもう一つ質問です。

売上明細が300万件〜500万件有るという想定です。

売上明細データから外部リンクした売上鑑のインデックスにもなっている売上日で範囲絞りするのと

売上明細データ自体にも売上日を保持し、その売上日でインデックスが用意されているのでは

作りにもよりけりですが、一般的には
抽出速度に関して言えば後者の方が速いですかね？


売上明細データにも売上日、得意先コード、営業担当コードなどの鑑項目を保持し
・売上明細ID
・売上日＋売上明細ID
・得意先コード＋売上日
・営業担当コード＋売上日
・商品コード＋売上日
といった具合にインデックスを用意しがちです。

商品コードを１件指定して、売上日で範囲指定する場合、
これが１番速いのは間違いないのでしょうね。


売上明細データから外部リンクした売上鑑のインデックスにもなっている売上日で範囲絞りするといった分を300万件ぐらいの売上明細でテストも始めています。


上記のように売上明細データにインデックスを５本ぐらい用意した場合、MagicのプログラムのメインソースのインデックスをPervasiveの時は適切なインデックスを使うよう明示しておりましたが、SQL Serverの場合でも同様に記述するのが望ましいのですかね？
（明示するようにはしております。）


範囲絞りをデータビューで書くのがいいのか
DB SQL がいいのか
Magic SQL がいいのか悩みます。

確かに悩みますね。
いろんなやり方がありますが、私ならという意見です。


売上鑑があるので、売上明細は正規化してしまいます。
インデックスもプライマリのみで、他は仮キーにします。
抽出速度だけを見ると、売上明細に色々付加させた方が早そうですが、なるべくシンプルにします。


レコードメインは、SQLコマンドにします。
FROMは売上明細、InnerJoinに売上鑑です。
範囲絞りはWhere句です。SQLパラメータで設定します。

この状態で速度を確認し、遅い場合は鑑にインデックスを追加します。
明細には項目もキーも追加しません。
追加するとしたら最終手段です。

なお、１：ｎならSQL文で集計しますが、１：１ならメンテナンスしやすくするため、データビューを使用します。

ちなみに、300万件のレコードであれば、APGの簡単なプログラムでも速度の違いが分かるかと思います。
データビューの表示と、SQLコマンドのAPGです。
SQLコマンドのプログラムが速く、MAGICを経由するだけで遅い事が実感できます。

ManagementStudioでSQL文を実行し、レスポンスが悪い時にインデックスを考えてみましょう。


300万件でも、シンプルな構成で、そんなに遅くは感じないと思いますよ。逆に横に長い方が遅く感じます。

売上明細の300万件について

ふと思いましたが、日々の動きで300万件でしょうか？
もし、累計であれば、月次や年次の確定処理により、確定テーブルを設け、そこに移動する方法もあります。
確定テーブルは、帳票用やDWHに合わせて作成することで、集計処理も速くなります。
SQL文では、UNIONで現行のデータと結合できますよ。

fjksudouさん、昨日はレスありがとうございました。

商品別月別集計データ、
得意先別月別集計データなどは作成済です。

商品コードを指定して、過去の売上明細を探す。
商品コードを指定せずに、直接、売上明細の商品名から探す
といった事も有りますね。

過去10数年分の売上明細は残しっぱなしという事はよくやってますね。

追伸＞
SQLコマンド
売上明細300万件 売上鑑をJOIN
売上明細の商品コードを１件指定
売上鑑の売上日で範囲指定　といった実験中です。

売上鑑には売上日のインデックスも作成済み。

商品コードの指定を行わずに、JOINした売上鑑の売上日で
範囲指定しても速そうですね。

売上明細データ上には売上鑑の項目（得意先コード、売上日、担当コードなどなど）を保持しておりました。

もし正規化を進め売上明細にそれらの項目を持たないとした場合、売上明細データのインデックスは凄く少なくなりそうです。

売上明細データで、インデックスになりそうな項目としては伝票番号＋明細番号、商品コードぐらいになるのでしょうね。

売上明細から鑑を外部リンクなどしての絞込みやソートで速度が出るのか気になります。実験せねばです。

fjksudouさん いつもお世話になりありがとうございます。

> 他にテーブルを作って結合した方が早い場合がありますので、私の場合は用途別にした１対１のテーブルもあります。

これは面白いですね。
今まで例えば売上伝票と売上明細だった2本のデータを
5本ぐらいに分割！なんて事もメリット有るのかもしれないですね。

そういった場合は、全体相関図などの資料を残さないと
訳がわからなくなるでしょうね。