私は、Coldfusion10がSQLServer 2008 R2にクエリを実行することで、パラメーター化のパフォーマンスに関する深刻な問題を一貫して再現することができました。他の人が何を得るのか知りたいと思います。コードは以下のとおりです。
テストは何をしますか?100行のテーブルを作成します。データ列は、1つを除いてすべて空白です。次に、Coldfusionクエリを10回実行し、半分はcfqueryparamを使用し、半分は単純な文字列を使用します。それぞれの応答時間のリストを返します。これを実行すると、最初の呼び出しとは別に、パラメーター化されたクエリの実行速度がはるかに遅くなります(約10〜100倍)。
SQL Serverで何が起こっているのですか?SQLサーバーに違いは見られません。どちらの場合も、プランキャッシュは実質的に同一のプランを示し(一方は明らかにパラメーター化されています)、プロファイラーは両方に対して高速な応答を示します。ただし、Coldfusionはパラメータ化されたクエリに苦労しています。
何が問題を修正しますか?不思議なことに、varcharをnvarcharに変更すると、問題は解決します。または、空白以外を先頭に移動すると、両方の応答が遅くなります(図を参照)。すべてのレコードを空白または非空白にすると、問題は発生しません。それはミックスでなければなりません。CF9で問題を再現できませんが、CF11を試していません。
<cfset datasource="yourdatasource" />
<cfquery name="createdata" datasource="#datasource#">
--EMPTY PREVIOUS TESTS
IF OBJECT_ID('aaatest', 'U') IS NOT NULL
BEGIN
TRUNCATE TABLE aaatest;
DROP TABLE aaatest;
END
--CREATE TABLE TO CONTAIN DATA
CREATE TABLE [dbo].[aaatest](
[id] [int] NOT NULL,
[somedata] [varchar](max) NULL,
[somekey] [int] NOT NULL
) ON [PRIMARY];
--INSERT 100 ROWS WITH 99 BLANK AND 1 NON-BLANK
WITH datatable AS (
SELECT 1 id
UNION all
SELECT id + 1
FROM datatable
WHERE id + 1 <= 100
)
INSERT INTO aaatest(id,somekey,somedata)
SELECT id,1,case when id=99 then 'A' else '' end
FROM datatable;
</cfquery>
<cfset results=[] />
<cfloop from="1" to="10" index="n">
<!--- use parameters for every other test --->
<cfset useParameters = (n mod 2 is 0) />
<cfquery name="myquery" datasource="#datasource#" result="result">
SELECT somedata
FROM aaatest
WHERE somekey=
<cfif useParameters>
<cfqueryparam value="1" CFSQLType="CF_SQL_INTEGER" />
<cfelse>
1
</cfif>
</cfquery>
<!--- store results with parameter test marked with a P --->
<cfset arrayAppend(results,(useParameters?'P':'')&result.executiontime) />
</cfloop>
<cfdump var="#results#" />
回答-コメントの@Raspinで確認されているように、設定NOCOUNT ON
すると問題が修正されます。
元の提案:
これが手がかりかもしれません。あなたはINDEXを扱っていませんが、私の考えでは、SQLはデータ変換を行う必要があります。行数が少ないと問題になるとは思いませんが、この問題が発生するとは思いません。
ハッシュを含むインデックス付き列でのcfqueryparam検索を使用した低速クエリ
cfqueryparamがvarcharsをUnicodeとして送信するかどうかに関係なく、ColdFusion管理者に設定がある可能性があります。その設定が列の設定と一致しない場合(この場合、その設定が有効になっている場合)、MSSQLはそのインデックスを使用しません。
私が試してみることをお勧めするもう1つのことSELECT
は、IF
ステートメント全体をステートメントでラップすることです。私の考えでは、SQLがクエリプランを再利用できるとは思わないような形で出くわしたのかもしれません。つまり、パフォーマンスの低下は実際には再コンパイルです。
<cfloop from="1" to="10" index="n">
<cfset useParameters = (n mod 2 is 0) />
<cfif useParameters>
<cfquery name="myquery" datasource="#datasource#" result="result">
SELECT somedata
FROM aaatest
WHERE somekey= <cfqueryparam value="1" CFSQLType="CF_SQL_INTEGER" />
</cfquery>
<cfelse>
<cfquery name="myquery" datasource="#datasource#" result="result">
SELECT somedata
FROM aaatest
WHERE somekey= 1
</cfquery>
</cfif>
<cfset arrayAppend(results,(useParameters?'P':'')&result.executiontime) />
</cfloop>
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加