Ok so you can't attach files.
<cfsilent>
<cfparam name="attributes.value" />
<!--- type of bind variable value must equate to --->
<cfparam name="attributes.cfsqltype" default="CF_SQL_CHAR" />
<!--- length of string in value attribute --->
<cfparam name="attributes.maxlength"
default="#Len(attributes.value)#" />
<!--- Number of decimal places in parameter. Applies to
CF_SQL_NUMERIC and CF_SQL_DECIMAL --->
<cfparam name="attributes.scale" default="0" type="numeric"
min="0" max="20" />
<!--- whether to ignore the value passed in and send
null --->
<cfparam name="attributes.null" default="no" />
<!--- whether the value is a list or single item --->
<cfparam name="attributes.list" default="no" />
<!--- what seperator to use if using list --->
<cfparam name="attributes.separator" default="," />
<!---
Before Changing anything in this custom tag run it against the
unit tests
* For these types, a data value can be converted to a numeric
value': CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT,
CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4,
CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT
* For these types, a data value can be converted to a date supported
by the target data source': CF_SQL_DATE, CF_SQL_TIME, CF_SQL_TIMESTAMP
* For all other types, if the maxLength attribute is used, a data
value cannot exceed the maximum length specified.
param type mssql equivilent
cf_sql_bigint bigint
cf_sql_bit bit
cf_sql_char char, nchar
cf_sql_date smalldatetime
cf_sql_decimal numeric, decimal
cf_sql_double double, float
cf_sql_float double, float
cf_sql_idstamp timestamp
cf_sql_integer int
cf_sql_longvarchar text
cf_sql_money money
cf_sql_money4 smallmoney
cf_sql_numeric numeric, decimal
cf_sql_real real
cf_sql_refcursor cursor
cf_sql_smallint smallint
cf_sql_time
cf_sql_timestamp datetime
cf_sql_tinyint tinyint
cf_sql_varchar varchar, nvarchar,uniqueidentifier
--->
<!--- stupid hack so you can use throw in cfscript --->
<cffunction name="throw">
<cfargument name="detail" type="string" required="false" />
<cfargument name="message" type="string" required="false" />
<cfargument name="errorcode" type="string" required="false" />
<cfargument name="type" type="string" required="false" />
<cfthrow detail="#arguments.detail#"
message="#arguments.message#" errorcode="#arguments.errorcode#"
type="#arguments.type#" />
</cffunction>
<!--- this custom tag is being written in cfscript in order to
speed processing time, since it is called very often on almost every
template --->
<cfscript>
// clear out any text in between start and end tags
thisTag.GeneratedContent = "";
// only execute this tag one time
if( thisTag.executionMode eq "end" OR NOT thisTag.HasEndTag) {
// variable to hold output string
Local.strQueryParameter = "";
// check that user does not want to return null
value
if(NOT attributes.null) {
// check maximum length
if(Len(attributes.value) GT
attributes.maxlength) {
throw( message="The cause of this
output exception was that':
coldfusion.tagext.sql.CF_QueryParamTag$InvalidDataException': Invalid
data '#attributes.value#' value exceeds MAXLENGTH setting
#attributes.maxlength#.",
type="APPLICATION",
detail="",
errorcode="0");
}
// what type of bind variable are we
passing
switch(attributes.cfsqltype) {
// integer types
case 'CF_SQL_INTEGER':
case 'CF_SQL_SMALLINT':
case 'CF_SQL_TINYINT':
case 'CF_SQL_BIGINT':
// if we are in a list
check each item in the list
for(Local.Iterator =
1;Local.Iterator LTE
ListLen(attributes.value,attributes.separator);Local.Iterator =
Local.Iterator + 1){
Local.CurItem =
ListGetAt(attributes.value,Local.Iterator,attributes.separator);
// check that
numeric types can be converted to numbers
if(NOT
IsNumeric(Local.CurItem)) {
throw(
message="Invalid data #attributes.value# for CFSQLTYPE
#attributes.cfsqltype#",
type="Database ",
detail="",
errorcode="0"
);
}
// floor
decimals to nearest integer
Local.strQueryParameter
=
ListAppend(Local.strQueryParameter,javacast('int',val(Local.CurItem)));
} // end loop over list of items
break;
// floating point types
case 'CF_SQL_REAL':
case 'CF_SQL_FLOAT':
case 'CF_SQL_DOUBLE':
case 'CF_SQL_MONEY':
case 'CF_SQL_MONEY4':
// if we are in a list
check each item in the list
for(Local.Iterator =
1;Local.Iterator LTE
ListLen(attributes.value,attributes.separator);Local.Iterator =
Local.Iterator + 1){
Local.CurItem =
ListGetAt(attributes.value,Local.Iterator,attributes.separator);
// check that
numeric types can be converted to numbers
if(NOT
IsNumeric(Local.CurItem)) {
throw(
message="Invalid data #attributes.value# for CFSQLTYPE
#attributes.cfsqltype#" ,
type="Database ",
detail="",
errorcode="0"
);
}
// return a
numeric value
Local.strQueryParameter
=
ListAppend(Local.strQueryParameter,javacast('double',val(Local.CurItem))
);
} // end loop over list of items
break;
// decimal types (affected by
scale)
case 'CF_SQL_DECIMAL':
case 'CF_SQL_NUMERIC':
// create mask that
matches the passed scale
Local.DecMask = "";
if(attributes.scale) {
Local.DecMask = ".";
for(Local.Iterator =
0;Local.Iterator LT val(attributes.scale);Local.Iterator =
Local.Iterator + 1) {
Local.DecMask =
Local.DecMask & "9";
}
}
// if we are in a list
check each item in the list
for(Local.Iterator =
1;Local.Iterator LTE
ListLen(attributes.value,attributes.separator);Local.Iterator =
Local.Iterator + 1){
Local.CurItem =
ListGetAt(attributes.value,Local.Iterator,attributes.separator);
// check that
numeric types can be converted to numbers
if(NOT
IsNumeric(Local.CurItem)) {
throw(
message="Invalid data #attributes.value# for CFSQLTYPE
#attributes.cfsqltype#" ,
type="Database ",
detail="",
errorcode="0"
);
}
// return a
numeric value
Local.strQueryParameter
=
ListAppend(Local.strQueryParameter,NumberFormat(val(Local.CurItem),"9999
99999999" & Local.DecMask));
} // end loop over list of items
break;
// date types
case 'CF_SQL_TIMESTAMP':
case 'CF_SQL_DATE':
case 'CF_SQL_TIME':
// if we are in a list
check each item in the list
for(Local.Iterator =
1;Local.Iterator LTE
ListLen(attributes.value,attributes.separator);Local.Iterator =
Local.Iterator + 1){
Local.CurItem =
ListGetAt(attributes.value,Local.Iterator,attributes.separator);
// check that
values passed are actually dates
if(NOT
IsNumericDate(Local.CurItem)) {
throw(
message="The cause of this output exception was that':
coldfusion.runtime.Cast$DateStringConversionException': The value
""#attributes.value#"" could not be converted to a date.",
type="APPLICATION",
detail="",
errorcode="0"
);
}
// return a date
value
Local.strQueryParameter
= ListAppend(Local.strQueryParameter,"'" & Local.CurItem & "'");
//Local.strQueryParameter = d.Date(Local.CurItem);
} // end loop over list of items
break;
//make booleans true or false
case 'cf_sql_bit':
// can not make lists of
booleans
if(NOT
IsBoolean(attributes.value)) {
throw( message="cannot
convert the value ""#attributes.value#"" to a boolean",
type="Expression
",
detail="",
errorcode="0"
);
} else {
if(attributes.value) {
Local.strQueryParameter = 1;
} else {
Local.strQueryParameter = 0;
}
}
break;
// text values
case 'CF_SQL_VARCHAR':
case 'CF_SQL_CHAR':
if(Find("'",attributes.value)) {
// if they are
passing a single quote in the string handle it through the built in
function
Local.USECFQUERYPARAM =
true;
/*
escapedString =
Replace(attributes.value,"'","''","ALL");
if(attributes.list) {
Local.strQueryParameter =
javacast("string",listqualify(escapedString,"'",","));
} else {
Local.strQueryParameter = javacast("string","'" & escapedString & "'");
}*/
} else {
if(attributes.list) {
// add
single quotes around each list item
Local.strQueryParameter =
javacast("string",listqualify(attributes.value,"'",","));
} else {
Local.strQueryParameter = javacast("string","'" & attributes.value &
"'");
}
}
break;
// for long var chars it is
necessary to update via the cfqueryparam tag
case 'CF_SQL_LONGVARCHAR':
Local.USECFQUERYPARAM = true;
break;
default:
throw( message="unknown
cf_sql_type",
type="APPLICATION",
detail="",
errorcode="0"
);
break;
} //switch sql type
} // are we returning null
} // are we done processing tag
</cfscript>
<!--- adding any space between the cfsilent and the cfif tag
will cause errors --->
</cfsilent><cfif IsDefined("Local.strQueryParameter")><cfif NOT
IsDefined("Local.USECFQUERYPARAM")><cfoutput>#Local.strQueryParameter#</
cfoutput><cfelse><cfqueryparam cfsqltype="#attributes.cfsqltype#"
value="#attributes.value#" list="#attributes.list#"
separator="#attributes.separator#" null="#attributes.null#"
maxlength="#attributes.maxlength#" /></cfif></cfif>
-----Original Message-----
From: Porter, Benjamin L.
Sent: Friday, December 29, 2006 7:58 AM
To: CF-Talk
Subject: RE: cfqueryparam DECREASES performance?
CFQueryParam can indeed decrease performance. If you look at what
happens when you use CFQueryParam versus just passing in variables it
makes sense. CFQueryParam transforms any passed variables into bind
variables. In MSSQL it is the same as declaring a variable, setting it
and then using that in the query. IE
SELECT
Id,
Description
FROM Product
WHERE ProductID = 2
VS (with query param )
DECLARE @P1 int
SET @P1 = 2
SELECT
Id,
Description
FROM Product
WHERE ProductID = @P1
CF then uses a stored procedure spprepexec to try to force the dbms to
precompile the sql statement into an execution plan for faster execution
on subsequent calls. This is where you lose performance often. If your
database does not have enough memory to store all of the prepared
statements you will be essentially running the query 2x every time you
call it. Once to generate the execution plan once to actually run it.
The same thing happens if the query is not called often enough. IE in
the case of reports run infrequently. The database will clear the
execution plan for your query since it is not used often to make room
for other queries. I would recommend writing a custom tag to replace
cfqueryparam when used with infrequent long running reports. A query
that takes 100 ms to run, is no big deal if it has to be recompiled once
and a while. A query that runs for 3 minutes can be a major problem if
it takes 2x as long 50% of the time. I wish Adobe would rewrite this
functionality to take an optional parameter to specify whether to use sp
prep exec.
I've attached a custom tag I wrote to replace cfqueryparam in these
cases. User beware it is not fully tested, and not as secure as
cfqueryparam.
-----Original Message-----
From: Greg Luce [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 28, 2006 1:01 PM
To: CF-Talk
Subject: cfqueryparam DECREASES performance?
OK, I must have something wrong here. I've only heard good things about
cfqueryparam on this list for both security and performance. A client
sent
me an ugly report that times out for them. I spent an hour going through
it
and applying cfqueryparams to each variable in the many queries with
appropriate datatypes. I threw a cfsetting tag in to increase the
request
timeout and the report runs in roughly about 512687 ms, restarted MSSQL
server and CFMX7, then with the cfqueryparams the same query that was
running in 5282ms in the old code, now takes 15094ms.
Any ideas?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265294
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
