check this SQL below out. We use it on Oracle 9i. Assuming you have
the proper privs and all, you can see all the SQL running within the
past few minutes. Add "AND username = 'FOO' " to see this for a
single user. Using this SQL run a large cfquery using maxrows. You
will see that maxrows is actually occuring in Oracle, how I don't know
I'd hope its using the inner view method you mentioned. I actually
tested by running SQL that returns 3000+ rows. Using maxrow=200, I
see no queries in Oracle with 2000+ rows, only ones with 201 rows.
I wonder if this is JDBC related, eh? a feature. Anyone using SQL
Server care to test this similarly?
select * from (
select vs.hash_value, au.username,
decodeCommandType(vs.command_type) as command,
round((vs.elapsed_time/vs.executions)/1000000,2) as avg_sec,
round(vs.ROWS_PROCESSED/vs.executions) as rows_processed_avg ,
round(vs.buffer_gets/vs.executions) as avg_buffer_gets,
vs.executions, vs.module, vs.sql_text, vs.optimizer_mode,
vs.first_load_time
from v$sql vs, all_users au
where vs.executions > 0
and au.user_id = vs.parsing_user_id
) where 1=1
/* sample optional filters */
and module = 'JDBC Connect Client' /* Cold Fusion MX Only */
order by rows_processed_avg desc
On Thu, 10 Mar 2005 10:30:35 -0500, Dave Carabetta <[EMAIL PROTECTED]> wrote:
> On Thu, 10 Mar 2005 09:50:37 -0500, Douglas Knudsen
> <[EMAIL PROTECTED]> wrote:
> > ok, maybe I'm on some bad crack today, but I recall that the maxrows
> > attribute of cfquery works AFTER the resultset is returned to CF from
> > the DB. Is this the case for all RDBMs? I'm noticing that this is
> > not the case with CFMX and Oracle. Maybe this behaviour changed?
> >
>
> Yes, it first pulls back all records the query itself specifies, and
> then does the equivalent of a "TOP" to get the number of rows on the
> ColdFusion side. We use Oracle (8i, 9i, and 10g) and all 3 versions in
> MX first pull back all records requested. Personally I would never
> suggest using maxrows for this reason. In Oracle a simple inner view
> (because there is no TOP) will handle your request much more
> efficiently.
>
> Regards,
> Dave.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:198208
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
