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

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へのアップグレードはできない場合、検討の価値はあるかと思います。





(土屋)




2016-12-06

Slim フレームワークの REST(PDO) および RESTfm を使って CRUD のテストをしてみた

 先日の記事『Slim フレームワークの REST による FileMaker データベースへのアクセス』では、REST URI 経由で FileMaker DB アクセス実験を行うために Silm フレームワークを採用しました。
 この記事ではWeb クライアントからの GET 要求を受領してから、 FileMaker データベースに PDO 経由で SELECT クエリを発行してデータを戻すという、Slim REST 側の操作方法を簡単にご紹介しました。

 今回はこの Slim フレームワークの REST 機能のパフォーマンステストに加え、RESTfm でもテストを行ってみました。
 RESTfm は、Goya 社 MIT ライセンスで公開しているオープンソースの PHP コードであり、 FileMaker Server で公開されているデータベースに対する REST API 機能を簡単に実装できます。

 とても便利!と思ったのは、REST に不慣れなプログラマが「 あるテーブルのレコードにアクセスする方法を知りたい」と思ったときに、RESTfm 環境の index.html にアクセスするだけで、その URI を知ることができることです。 
 例えば、「EasyApp15‗p」データベースにログインすれば、任意のレイアウトのそれぞれのレコードへの URI が自動表示され(下図)、商品レイアウト上のレコード ID 10951 へのレコードにアクセスするためのリンクが表示されていることが確認できます。


 また、URL の最後の部分のファイル拡張子は .html ですが、これを .json、.xml、.txt、.fmpxml などに書き換えるだけで、自動的に変換表示してくれるのもよいですね。これは、PHP でプログラムしたことがある方ならわかりますが、結果セットを簡単に見ることができ、とても楽ちんです。


 さて、本題に戻りますが、今回のパフォーマンス検証では、クライアントの Web ページから GET(取得 READ)、POST(新規作成  CREATE)、PUT(更新 UPDATE)、DELETE(削除) 要求を発行しました。

 比較用として、記事「 FileMaker API別にCRUDのテストをしてみた」で採用した API も一緒にグラフに掲載しておりますので、 併せて参考にしてください。

テスト環境

サーバ: Xeon 2.2GHz(1Core)/4GB RAM(Hyper-V 仮想マシン)
OS/Webサーバ: Windows Server 2012/IIS 8.0
FileMaker Server: Ver 15
使用言語: PHP
使用DB: FMEasy在庫 IWP/WD R1.5 (パイロット版)
使用REST API:
 Slim フレームワーク(PDO を使用)
 RESTfm 4.0.8 (FileMaker API for PHP を使用)
テスト概要:
 Slim フレームワーク REST URI 経由、および RESTfm の URI 経由でCRUD(Create/Read/Update/Delete)を行う簡単な Webアプリを作成・実行。

備考:
  1. Slim フレームワークから FileMaker データベースへの接続オブジェクトは PDO(ODBC)です。
  2.  RESTfm はその仕様上、内部的には FileMaker API for PHP で FileMaker Server に接続します。

 Slim フレームワークは Composer を使ってインストールします。
 詳しくは以下のリンクをご覧ください。
 https://www.slimframework.com/docs/start/installation.html

 RESTfm の最新版は 4.0.8 です。(2016/12/06 現在)
 以下のリンクよりダウンロードできます。
 https://github.com/GoyaPtyLtd/RESTfm/releases

 各テストはそれぞれ5回実施して開始から終了までの時間をプログラム上で測定。各5回の平均値を基にデータを作成しました。

Slim フレームワークを使用し、100件のレコードを REST URI 経由で検索・表示した際のブラウザウインドウ(FireFox)

RESTfm を使用し、100件のレコードを REST URI 経由で検索・表示した際のブラウザウインドウ(FireFox)

GET メソッドによる Readテスト 1


テスト内容:
 94万レコードを持つテーブルから該当件数が N 件になるようにレコードを検索し、その中から100レコードのみを表示

結果: XML圧勝! Slim は遅いね

該当件数がN件になるようにレコードを検索し、その中から100件を表示

          No. of found                     recs
APIs
0 15000 30000 300000 600000 900000
FM API for PHP 0 0.22 0.34 1.76 3.28 4.74
XML 0 0.19 0.25 1.68 3.26 4.71
PDO 0 0.65 0.72 2.47 4.18 5.91
Slim REST PDO 0 0.46 0.63 3.86 7.3 10.69
RESTfm 0 0.88 0.91 2.32 3.91 5.4


補足:
 対象レコード件数が 1万件前後までは他の API とさほどパフォーマンスの差は感じられませんが、2万件あたりから差は開いていき、3万件を超過すると Slim REST PDO グラフ線(エンジ色)の傾斜は急激にきつくなっています。

 これに対し、RESTfm (黄色のグラフ線)は、該当件数が90万件でも、その中から 100件取り出す程度であれば、PDO よりもパフォーマンスは良いようです。


GET メソッドによる Readテスト 2


テスト内容:
 94万レコードを持つテーブルで 該当件数が3万件になるように検索し、N 件のレコードを表示

結果: FM API for PHP と XML が有利 Slim と RESTfm はやはり遅い

該当件数が3万件なるように検索し、その中から N件を表示

         No of fetched                    recs
APIs
0 100 500 1000 2500 5000 7500 10000
FM API for PHP 0 0.34 0.81 1.36 4.32 6.99 10.51  
XML 0 0.25 0.55 1.09 2.88 6.04 10.58 16.19
PDO 0 0.72 0.74 1.28 3.45 5.18 7.46 10.24
Slim REST PDO 0 0.64 1.6 2.75 6.15 12.32 18.22 23.72
RESTfm 0 0.97 4.31 7.97 19.25      


補足

 100 件程度のデータを取り出すなら、やはり FM API for PHP および XML がパフォーマンスに優位性がみられます。
 取得件数が 5000 件を増えると PDO が優位になってきます。

 全体的にみて、Slim も RESTfm も取り出すデータ件数が増えるにしたがい、極端にパフォーマンスが低下しているのがわかります。

 2500件取得時には、Slim(注:Slimは PDO を使用) は PDO(注:Slim不使用の生PDO) の約 1.8倍の応答時間となっており、RESTfm は PDO の約 5.6倍の応答時間となっています。
 1万件取得時には、Slim は PDO の約 2.3倍の応答時間、RESTfm (注:FM API for PHP を使用)にいたっては、5000 件以降はサーバタイムアウト(30秒)が発生し、計測不能となっています。


CREATE テスト


テスト内容:
 94万レコードを持つテーブルに対して 100件のレコードを PHP のループにより作成。その後、作成した100件のレコードを検索して表示。

備考:
  1. Slim フレームワークは、内部処理をある程度自由にプログラミングできるため、クライアント側から 100回 POST 要求を発行するパターンと、1 回の POST 要求で Slim フレームワーク側で 100 レコードをループ作成する方法で計測を行いました。
  2. RESTfm は、URI のパターンは定型化されていますが、1件レコード作成用の URI と複数のレコード作成を一回のリクエストで実現するバルク処理用の URI が用意されているため、それぞれで計測を行いました。

    1件レコード作成用 RESTfm  URI パターン
    /RESTfm/{datagase}/layout/{layout}

    バルク作成用 RESTfm  URI パターン
    /RESTfm/{database}/bulk/{layout}

    それぞれ、POST メソッドにて json 形式のデータを送信。

結果: PDOの圧勝!ただ、バルク処理なら Slim PDO もいい感じ


100レコード連続作成結果

補足
 クライアント側から 1 回リクエストを発行し、REST 側で 100 回レコード作成したほうが、クライアントから 100 回リクエストを発行し、そのたびに 1 回レコードを作るよりパフォーマンスがよくなるのは当然ですけどね…

DELETE テスト


テスト内容:
 94万レコードを持つテーブルを使用。FM API for PHP と XML では100レコードが対象となるように検索を実行。その後に PHP のループによりレコードを1件ずつ削除。PDO および Slim では DELETE table WHERE ~により、100件を一度に抽出して一括削除。
 RESTfm では、100レコードが対象となるように検索を実行後、バルク処理を使ってレコードを一括削除。

備考:
バルク作成用 RESTfm  URI パターン
/RESTfm/{database}/bulk/{layout}

DELETE メソッドにて json 形式の削除対象データを送信。


結果: PDO 圧勝だが、Slim も RESTfm もバルク処理ならパフォーマンスはよい

100レコード削除結果



UPDATE テスト


テスト内容:
 94万レコードを持つテーブルを使用。FM API for PHP と XML では100レコードが対象となるように検索を実行。その後に PHP のループによりレコードを1件ずつ更新。 PDO および Slim では UPDATE table SET ~ WHERE ~により、100件を一度に抽出して一括更新。 更新後、更新対象となったレコード100件を検索して表示。
 RESTfm では、100レコードが対象となるように検索を実行後、バルク処理を使ってレコードを一括更新。

備考:
バルク更新用 RESTfm  URI パターン
/RESTfm/{database}/bulk/{layout}

注:PUT メソッドにて json 形式の更新データを送信。


結果: PDO圧勝!Slimも奮闘。ただ、RESTfm は連続更新は苦手みたい


100レコード更新結果

結論:


 Web アクセスをする場合、間に何か挟めば余分なトラフィックが発生しますので、低速になるのはあたりまえです。というわけで、これは納得の結果です。

 すべてが自己完結するような Web アプリケーションを一から組めば、パフォーマンスに優れ、かつかゆいところに手が届くものができるかもしれませんが、それなりのコストと時間がかかってしまうことがネックとなります。

 多少パフォーマンスを犠牲にしてしまうことが許容される業務環境で、Web アプリケーションをなるべく短期間で開発する場合は、Slim のような REST フレームワークや、RESTfm のような既成 REST モジュールを採用することは選択肢としてありでしょう。

 しかしながら、どれを採用するにしても、覚えることが増えることには変わりません。

 Slim フレームワークの場合は、GET/POST/PUT/DELETE などの送信メソッドに対応した便利な機能が多数用意されていますが、それらを組み合わせてそれぞれの要求に応答した処理を行うには、担当プログラマが検討してコーディングする必要があります。
 組み方によっては、効率的にデータベースアクセスができるものが仕上がるという柔軟性はあります。

 RESTfm に関しては、操作用の定型 URI があらかじめ提供されているため、開発者がやりたいことさえ把握していれば、比較的高速に Web アプリケーションを完成させられるでしょう。Slim フレームワークに比べれば、覚えることが少ないのは良いと思います。
 ただし、RESTfm は FileMaker API for PHP を経由しますので、FileMaker API for PHP のパフォーマンスに縛られます。さらに、RESTfm 内部でも処理を行いますので、その分の処理コストが上乗せされます。
 つまり、FileMaker API for PHP が不得手な処理を RESTfm 経由で連続してやらせたりすると、パフォーマンスは極端に低下してしまいます。


 Web プログラミングは、長い目で見れば開発コストよりも運用コストの方がものを言います。特に、多数のクライアントを抱える Web サイトや、短時間に何人ものユーザが集中的にアクセスするようなサイトですと、Web サーバのパフォーマンス問題は避けては通れない話です。

 それぞれの特徴やパフォーマンスをある程度比較検討したうえで、業務要件に適したツール選択を行うのが失敗しない Web 開発につながるでしょう。


参考リンク:
Slim フレームワーク (英語)
Slim フレームワーク ユーザガイド (英語)
RESTfm (Goya 社)(英語)
RESTfm オンラインマニュアル (英語)
RESTfmがオープンソースに (Not only FileMaker)


(亀)