ラベル ESS の投稿を表示しています。 すべての投稿を表示
ラベル ESS の投稿を表示しています。 すべての投稿を表示

2017-06-02

大容量のSQLテーブルもOK! ― FileMaker 16 を帳票作成ツールとして使う

 FileMaker Server 16 (以下、FMS16)ではPDF出力をサポートするようになりました。FileMaker Pro はもともと帳票/レポートを簡単に作成できるツールして利用されてきましたが、サーバサイドでPDFが作成できるようになり、面倒なWeb帳票の設計・実装が実に簡単、且つ短時間のうちに行えるようになりました。 データベースは FileMaker でも SQL/XMLデータベースでもOKです。 本稿では MySQL の1500万件のテーブルからデータを抽出し、遅延無くPDFを出力する方法の概要を記します。

開発・運用環境

FileMaker Pro (Advanced) 16 ― 帳票作成ツール*1
FileMaker Server 16/IIS/PHP ― サーバ*2

 *1 帳票を作り、簡単なスクリプトを書くだけであればFileMaker Pro 16(¥4万前後)で事足りますが、複雑なスクリプトを書くには開発用(デバッガ付)の FileMaker Pro Advanced (¥7万前後)が必要です。
 *2 FileMaker Server 16 はFM社直売で¥99,000 (税抜)です。

 FileMaker Server 16 (の カスタムWeb公開 =CWP)の理論上の最大接続数は 2,000、検証接続数は500 ですので(詳細はこちら)、¥20万弱で本格的な帳票開発・運用環境が構築できる可能性があります。

本稿の目的と概要図

Webブラウザ上でから 入出庫システム(バックエンドはMySQL)にアクセスし、入庫伝票をPDF形式で作成・表示することを目的とします。 帳票を構成するテーブルに1000万件単位レコードが存在する場合にも、ユーザにストレスを与えない程度の実行速度で、入庫伝票PDFを表示することも目指します。以下が実装手法の概要図となります。



実装手法

入出庫明細テーブルには 約1500万のレコードがあります。 FileMaker で数十万件を超すテーブルからデータを抽出してPDF帳票を遅延無く作成するには、上図の FMS ⇔ MySQL 間の設計がキモとなります。今回は一般的な FileMaker ESS(External SQL Source 機能)を使う手法 と、原始的な ODBC データ取込を使う2つの手法を実装・テストしました。

実装手法1 ―  FileMaker ESS を使用  データ件数が多くなると低速化する

  1. 本手法では ESS を使用し、MySQLのテーブルを FileMaker のファイル内に直接配置します。下図の「_入庫」や「_取引先_入庫」などがそれに相当し、これらはシャドウテーブルとも呼ばれます。
    ESSは FileMaker 内から直接 MySQL のテーブルを読み書きでき便利なのですが、数十万行を超す大きなテーブルを扱う時には速度が低下するなどの問題があります。ESSの問題点については、古い記事になりますがこちらを参考にしてください。

  1. 入庫伝票レイアウトの開発は FileMaker Pro (Advanced) のGUIを利用し、テーブル内のフィールドや罫線、テキストなどのオブジェクトを配置しながら作成すると、慣れた人なら 短時間で完成すると思います。  
FileMaker Pro により作成した入庫伝票イメージ

  1. 次に FileMaker のスクリプトメーカーで、PHP から渡ってきた入庫No による検索、入庫伝票レイアウトの選択、PDF保存などを行うスクリプト(「実装手法概要図」の「特定のスクリプト」)を作成します。
  2. 最後に PHPファイルからそのスクリプトを実行するように指定します。

実装手法2 ― ODBCデータ取込 を使用 データ件数にかかわらず高速

  1. 本手法では独立した FileMaker ファイルを一つ用意し、その中に FileMaker のテーブル(vw入庫伝票、下図参照)を用意します。

    このテーブルに MySQL から必要最低限のデータを取り込み、そのデータを FileMaker レイアウトを使用してPDF形式で出力します。

    注:ESS は使用せず、したがって MySQL のシャドウテーブルも配置しません。
  2. MySQL側に入庫伝票に必要なテーブル(本例では、入出庫明細、取引先、入庫、商品の4テーブル)を結合した入庫伝票用ビューを作成します。

    ビューの作成は必須ではありませんが、ビューが無いとFileMaker内に4つのテーブルを用意し、4回SELECTクエリを発行して用意したテーブルに対応するMySQLのテーブルデータを取り込む必要があります。ビューを使用すれば、テーブル、クエリー、取込とも1つ/1回で済むので、実行速度的にも有利です。特別な事情がなければ、ビューを使用しましょう。

  3. FileMaker を使用して入庫伝票レイアウトの作成します。テーブル構成が異なるので実装手法1とはその作成方法は異なりますが、FM開発者にとっては容易な作業と思います。
  4. 次にPHPから渡ってきた 入庫No を使用し、 ビューに対して SELCTクエリーを発行し、結果を vw入庫伝票テーブルに取り込み、入庫伝票レイアウトを選択して、PDFを保存するスクリプトを作成します。
  5. PHPからこの FileMaker スクリプトを実行して、PDFを作成・表示する部分は実行手法1と同様です。

テスト結果

上記2つの手法による実装をブラウザからテストしてみました。HTTPリクエストからPDFが表示されるまでの時間は以下の通りです(テストは各5回ずつ実行し、その平均値を記載しています)。

実装手法1: 77秒
実装手法2: 1.2秒 

 その差は圧倒的でした。シャドウテーブルを配置するESSは、SQLクエリーの発行を FileMaker がすべてやってくれるので便利な反面、膨大なクエリーを発行するため速度低下を招きます。 その点、 ファイル内にESSシャドウテーブルを配置せず、SQL SELECT文を自身で指定し、その結果を FileMaker テーブルに取り込む ODBCデータ取込は大きなSQLテーブルに対しては圧倒的に有利です。

思ったこと

実装手法2であれば、数千万件のデータを持つSQLデータベースの帳票作成環境として、その使用に耐えそうです。 実装手法1は10万~20万件程度のテーブルであれば利用可能と思われます。

本稿では SQLデータベースを取り上げましたが、旧FileMaker Server の Webシステムを温存しつつ、下図のように FMS16 を併行運用し、FMS 16 は PDF出力用サーバとして使用する方法も考えられます。

PDF出力をしたいが、 FM16へのアップグレードはできない場合、検討の価値はあるかと思います。





(土屋)