Data Base Performance Enhancement By cfqueryparam

Data Base Performance Enhancement By cfqueryparam

In ColdFusion, generally we use cfqueryparam for security causes. But cfqueryparam also helps in database query performance enhancement. Let see how this works.

I wrote the following code in ColdFusion then run the code.

<cfset request.param = "test" />

<cfquery name="TestQUery" datasource="LOCAL_DB">

    SELECT * FROM RETS_tblField WHERE vcField_Long_Name = '#request.param#'

</cfquery>

Again I changed the request.param = “test1” and run the code. When I checked the database cache I got the following out put.

There is two cache entries for the two times execution of the same query. If our application will run this query 1lakh times then it will create 1lakh entry in the cache.

Which will create the following issue.

• There will more cache entries for a single query.
• Frequency of the auto cache deletion of the database will increase.
• It will use more RAM size as all the cache entries will be load into RAM for searching all the queries.
• The same query will compile as many times it runs.

If we write our query in following way.

<cfset request.param = "test" />

<cfquery name="TestQUery" datasource="LOCAL_DB">

    SELECT * FROM RETS_tblField WHERE vcField_Long_Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#request.param#" />

</cfquery>

Also I changed the request.param="test1" and run it twice. Then I got following out put.

Here it creates only one cache entry for the same query also compiles only once. It doesn't depend on no of times you runs the query.

The most important thing is how we will check the cache entries of database.

For MS-SQL:

To Get Cache Entries

SELECT

cp.objtype,st.text

FROM

sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE

st.text NOT LIKE '%sys.dm_exec_cached_plans%'

AND

st.text LIKE '% RETS_tblField %'

NOTE: Here RETS_tblField is the table name . So the above query will list out cache entries which contains RETS_tblField as a part of text.

To Clear Cache Entries

DBCC FREEPROCCACHE

Similarly you can find queries to check database cache entries for other database.