On Thu, 10 Mar 2005 10:57:09 -0500, Douglas Knudsen <[EMAIL PROTECTED]> wrote: > 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 >
Hmm, I don't have access to the v$sql table (I'm not the DBA) in our DBs, so I can't run that query. But from looking at it, there's nothing in there that would indicate it's doing any sort of "TOP"-like record limiting. I just know from running our tests here that used to use maxrows that the debug output would show the total records being returned in the Records=X section of the SQL Queries section, but only the maxrows number was output. 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:198234 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
