Dynamics CRM 2011 : Update Rollup 10 で提供された簡易検索のパフォーマンス向上に関する詳細

みなさん、こんにちは。

以前、以下の記事で紹介した Update Rollup 10 で提供された簡易検索の
パフォーマンス向上ですが、先日、北米 PFE ブログにて詳細な考察が公開
されましたので、こちらでも紹介します。

Dynamics CRM 2011 Update Rollup 10 簡易検索レコードのパフォーマンス向上

元記事: CRM 2011 Performance: Take advantage of better query performance for your Quickfinds!

=============================================================
Update Rollup 10 でデータの取得方法が大きく改善されました。その手法は
様々ですが、その 1 つに簡易検索クエリの変更があります。簡易検索では
isQuickFindFields プロパティを新たに追加し、プロパティによって生成される
クエリが変更されます。今回は、こちらの詳細な情報を提供します。

まず比較として、以前までのクエリについて説明します。以下に実際のクエリと
クエリプランを示しますが、クエリプランが非常に複雑であることが見て取れます。

exec sp_executesqlN'select
top 251 "new_labor0".New_WorkCodeId as "new_workcodeid"
, "new_labor0".New_Minutes as "new_minutes"
, "new_labor0".New_AccountId as "new_accountid"
, "new_labor0".New_comments as "new_comments"
, "new_labor0".New_LaborDate as "new_labordate"
, "new_labor0".CreatedOn as "createdon"
, "new_labor0".new_projectid as "new_projectid"
, "new_labor0".crmfargo_LaborCaseId as "crmfargo_laborcaseid"
, "new_labor0".New_laborId as "new_laborid"
, "new_labor0".New_WorkCodeIdName as "new_workcodeidname"
, "new_labor0".New_AccountIdName as "new_accountidname"
, "new_labor0".New_AccountIdYomiName as "new_accountidyominame"
, "new_labor0".new_projectidName as "new_projectidname"
, "new_labor0".crmfargo_LaborCaseIdName as "crmfargo_laborcaseidname"
from New_labor as "new_labor0" (NOLOCK) 
where
(((("new_labor0".New_WorkCodeIdName like @New_WorkCodeIdName0 or "new_labor0".OwnerIdName like @OwnerIdName0 or "new_labor0".crmfargo_LaborCaseIdName like @crmfargo_LaborCaseIdName0 or "new_labor0".New_comments like @New_comments0)))) order by "new_labor0".New_LaborDate desc, "new_labor0".New_laborId asc',N'@New_WorkCodeIdName0 nvarchar(9),@OwnerIdName0 nvarchar(9),@crmfargo_LaborCaseIdName0 nvarchar(9),@New_comments0 nvarchar(9)',@New_WorkCodeIdName0=N'Contract%',@OwnerIdName0=N'Contract%',
@crmfargo_LaborCaseIdName0=N'Contract%',@New_comments0=N'Contract%'

図1 : 新機能適用前の一般的な簡易検索の実行プラン:

上記のクエリプランでは右下から左上行くにつれで、データセットが大きくなり、
最後に並べ替えとフィルターが実行されています。データの持ち方や検索の
対象にもよりますが、効率的ではない場合が多いです。  

これに対して、最適化された簡易検索のクエリは以下のように UNION SELECT を
用いることで、それぞれの検索をシンプルにしています。※ top 10001 の箇所に
ついては、簡易検索の最適化と同時に提供された、検索結果上限を 1 万件に
する機能によるものです。

exec sp_executesqlN'WITH __QuickFind__ as (select top 10001 [New_laborId] from (
SELECT "new_labor0".[New_laborId] as [New_laborId] from [New_laborExtensionBase] as "new_labor0" where ("new_labor0".New_comments like @New_comments0) UNION
SELECT "new_labor0".[New_laborId] as [New_laborId] from [New_laborBase] as "new_labor0" where "new_labor0".OwnerId in (select [OwnerId] from [OwnerBase] as "new_labor0" where "new_labor0".Name like @OwnerIdName0) UNION
SELECT "new_labor0".[New_laborId] as [New_laborId] from [New_laborExtensionBase] as "new_labor0" where "new_labor0".New_WorkCodeId in (select [New_workcodeId] from [New_workcodeExtensionBase] as "new_labor0" where "new_labor0".New_name like @New_WorkCodeIdName0) UNION
SELECT "new_labor0".[New_laborId] as [New_laborId] from [New_laborExtensionBase] as "new_labor0" where "new_labor0".crmfargo_LaborCaseId in (select [crmfargo_laborcaseId] from [crmfargo_laborcaseExtensionBase] as "new_labor0" where "new_labor0".crmfargo_name like @crmfargo_LaborCaseIdName0)) as [__QuickFindInternal__])select
top 251 "new_labor0".New_comments as "new_comments"
, "new_labor0".New_Minutes as "new_minutes"
, "new_labor0".New_LaborDate as "new_labordate"
, "new_labor0".New_AccountId as "new_accountid"
, "new_labor0".OwnerId as "ownerid"
, "new_labor0".crmfargo_LaborCaseId as "crmfargo_laborcaseid"
, "new_labor0".New_laborId as "new_laborid"
, "new_labor0".New_AccountIdName as "new_accountidname"
, "new_labor0".New_AccountIdYomiName as "new_accountidyominame"
, "new_labor0".OwnerIdName as "owneridname"
, "new_labor0".OwnerIdYomiName as "owneridyominame"
, "new_labor0".OwnerIdType as "owneridtype"
, "new_labor0".crmfargo_LaborCaseIdName as "crmfargo_laborcaseidname"
, case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as
[__QuickFindLimitValue__]
from New_labor as "new_labor0" (NOLOCK) 
where
[new_labor0].[New_laborId] in (select [New_laborId] from [__QuickFind__]) and (((("new_labor0".statecode = @statecode0)))) order by
"new_labor0".New_LaborDate desc
, "new_labor0".New_laborId asc',N'@statecode0 int,@New_WorkCodeIdName0 nvarchar(9),@OwnerIdName0 nvarchar(9),@crmfargo_LaborCaseIdName0 nvarchar(9),@New_comments0 nvarchar(9)',@statecode0=0,@New_WorkCodeIdName0=N'Contract%',@OwnerIdName0=N'Contract%',
@crmfargo_LaborCaseIdName0=N'Contract%',@New_comments0=N'Contract%'

図2: 新機能適用後の一般的な簡易検索の実行プラン:

上記のように UNION SELECT を利用してクエリを分割することで、SQL Server が
効果的にインデックスを使用するようになります。そのためデータセットに含む
列の数がどの段階でも比較的少なくなり、効率的にクエリが実行できます。また
フィルタも各段階で実施されています。

※必ずしもすべての状況でパフォーマンスが向上することに直結はしませんが、
多くの環境では恩恵を受けれるはずです。

簡易検索のオプションは既定で有効になりますが、より詳細を知りたい場合には
Optimization White Paper (英語) をご確認ください。

以下に本機能に関する FAQ を紹介します。

Q: どうすれば新機能が有効になりますか。
A: Update Rollup 10 をインストールすることで既定で有効になります。以前
EnableRetrieveMultipleOptimization オプションを設定したことがある場合は値を
0 にしてください

Q: EnableRetrieveMultipleOptimization とは何ですか。
A: 詳細は Optimizing CRM white paper にありますが、Microsoft Dynamics CRM が
データを取得する挙動を変更するためのオプションです。設置型の場合はレジストリ
を変更するか、OrgDbOrgSettings ツールを利用して設定が行えます。オンライン環境
の場合も OrgDbOrgSettings を利用できます。

Q: OrgDbOrgSettings とは何ですか。  
A: ツールの詳細はこちらの記事をご覧ください。

Q:   SDK を利用した開発で使用するクエリでも、最適化機能は利用できますか。
A: はい。カスタムクエリについても上記機能は利用可能です。
詳細はこちらの記事をご覧ください。

=============================================================

原文はより詳細な情報がありますので、興味がある方は是非原文もご覧ください。

- Dynamics CRM サポート 中村 憲一郎