Bryan,
If you think it's Oracle, try the sql outside of Reactor and
Model-Glue. When I run the below, the elapsed time ranges from
00:00:01.10 to 00:00:00.12. I don't know if this helps as I don't have
MSSql here to compare w/.
Brian has a good point:
"I think the underlying thing that is being missed is that the
readFields query only runs one time when in production mode. So the
fact that it takes 1.6 seconds for this to run doesn't matter at all
since it only happens one time, the first time the app is loaded. So
instead of focusing on why this query takes 1.6 seconds, the real
issue is why it keeps re-executing for you when it doesn't for anyone
else."
Beth
SET TIMING ON;
SELECT
col.COLUMN_NAME as name,
CASE
WHEN primaryConstraints.column_name IS NULL
THEN 'false'
ELSE 'true'
END as primaryKey,
/* Oracle has no equivalent to autoincrement or identity */
'false' AS "IDENTITY",
CASE
WHEN col.NULLABLE = 'Y' THEN 'true'
ELSE 'false'
END as NULLABLE,
col.DATA_TYPE as dbDataType,
case
/* 26 is the length of now() in ColdFusion (i.e.
{ts '2006-06-26 13:10:14'})*/
when col.data_type = 'DATE' then 26
/* oracle return 4000 for clobs which is the
length of what is stored inline in the record. However, oracle can
store several gb. */
when col.data_type in ('CLOB','BLOB') then 20000000
/* Oracle can compress a number in a smaller
field so use precision if available */
else nvl(col.data_precision, col.data_length)
end as length,
col.data_scale as scale,
col.DATA_DEFAULT as "DEFAULT",
CASE
WHEN updateCol.updatable = 'YES' THEN 'false'
ELSE 'true'
END as readonly
FROM USER_tab_columns col,
USER_updatable_columns updateCol,
( select colCon.column_name,
colcon.table_name
from USER_cons_columns colCon,
USER_constraints tabCon
where tabCon.table_name = 'ST_STUDY'
AND colCon.CONSTRAINT_NAME = tabCon.CONSTRAINT_NAME
AND colCon.TABLE_NAME = tabCon.TABLE_NAME
AND 'P' = tabCon.CONSTRAINT_TYPE
) primaryConstraints
where col.table_name = 'ST_STUDY'
and col.COLUMN_NAME =
primaryConstraints.COLUMN_NAME (+)
AND col.TABLE_NAME = primaryConstraints.TABLE_NAME (+)
and updateCol.table_name (+) = col.table_name
and updateCol.COLUMN_NAME (+) = col.COLUMN_NAME
order by col.column_id;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[EMAIL PROTECTED]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
