[thelist] CF: Query runs slower in ColdFusion than in Oracle

[Scott Brady]

(I'd have tried cf-talk for this problem, but that list seems to be down today)

I have a fairly complex query that runs drastically slower in CF than it does within Oracle (well, Toad), and I can't figure out why.  (I'll include the query at the end of this e-mail).  It seems to me that if a query takes 2 seconds for Oracle to process, it shouldn't take much longer (if any longer) when cfquery is run.  It certainly shouldn't take 30 seconds to run.

I've created a page (with debugging turned on) that runs just the query and doesn't do anything else, so I know that it's the cfquery that's taking 30 seconds to process.

Note: the main line in question is "AND mcd.org_seq_no = 170572"  If I change that conditional to look at some other table [so it's really a whole other condition], the cfquery takes the same amount of time as it does within Toad.  So, while it seems to be the SQL affecting it, it seems like the Toad query should then also take 30 seconds with that condition.

I've verified this difference on 3 different CF servers (2 developer boxes and 1 QA box) and with 2 different instances of the database, so it shouldn't be attributable to something in my setup.

I'd appreciate any suggestions.  This is Oracle 8 on CFMX for Windows 2000.  Because it's a corporate environment, making changes to the actual servers' settings is not much of an option. (I can test such changes on my local server, of course).

Here's the query (which is complex, and it'd be too hard to explain the organization, since I barely understand the database as it is):
				SELECT
					abnq.order_seq_no || '_' || ltt.antrim_test_id groupColumn,
					abnq.abn_queue_seq_no,
					abnq.order_seq_no,
					abnq.patient_seq_no,
					abnq.org_seq_no,
					cln.description clinic,
					abnq.abn_reason,
					abnr.abn_description reason,
					pd.patient_id,
					to_char(o.collection_date,'mm/dd/yyyy') collDate,
					UPPER(md.last_name) || ', ' || UPPER(md.first_name) physician,
					UPPER(eb.last_name) || ', ' || UPPER(eb.first_name) enteredBy,
					NVL(o.lab_accession_no,'Pending') lab_accession_no,
					o.order_type_cd,
					ltt.abbrev test_abbrev,
					ltt.description || ' (' || ltt.abbrev || ')' test,
					ltt.antrim_test_id,
					NVL(pg.description,' ') test_cat,
					probd.description justification,
					prob.icd9_code,
					to_char(abnq.setup_date,'mm/dd/yyyy') abn_generated,
					NVL(o.return_to_clinic_ind,'N') return_to_clinic_ind,
		 		 	oqs1.who_made_chg_part_seq_no coder_seq_no,
				 	to_char(oqs1.setup_date,'mm/dd/yyyy') coded_date,
					UPPER(mcd.last_name) || ', ' || UPPER(mcd.first_name) coder_name,
					mcd.org_seq_no coder_seq_no
				FROM
					abn_queue abnq,
					abn_reason abnr,
					patient_data pd,
					orders o,
					organizations cln,
					org_demographics md,
					org_demographics eb,
					lab_test_types ltt,
					problem_groups pg,
					lab_problem_standards lps,
					problem_descrip probd,
					problems prob,
					lab_orders lo,
					lab_order_icd9 loi,
					order_queue_states oqs1,
					org_demographics mcd
				WHERE  
					pd.patient_seq_no = abnq.patient_seq_no
					AND mcd.org_seq_no = 87945
					AND o.order_seq_no = abnq.order_seq_no
					AND cln.org_seq_no = abnq.org_seq_no
					AND abnr.abn_reason = abnq.abn_reason
					AND md.org_seq_no = o.ordering_md_partic_seq_no
					AND sysdate BETWEEN md.begin_date AND md.end_date
					AND eb.org_seq_no = o.entered_by_partic_seq_no
					AND sysdate BETWEEN eb.begin_date AND eb.end_date
					AND ltt.analyte_no = abnq.analyte_no
					AND ltt.analyte_no = lps.analyte_no(+)
					AND (lps.dialysis_modality_type = pd.dialysis_modality_type OR lps.dialysis_modality_type IS NULL)
					AND pg.problem_group_cd(+) = lps.problem_group_cd
					AND lo.lab_req_seq_no  = abnq.lab_req_seq_no 
					AND lo.lab_req_seq_no = loi.lab_req_seq_no
					AND loi.problem_seq_no = prob.problem_seq_no
					AND prob.problem_seq_no = probd.problem_seq_no
					AND oqs1.order_seq_no = abnq.order_seq_no
					AND oqs1.who_made_chg_part_seq_no = mcd.org_seq_no
					AND sysdate between mcd.begin_date and mcd.end_date
					AND oqs1.order_status_cd || '' = 'RTL'
					AND oqs1.setup_date = 
					(
						SELECT 
							MAX(setup_date)
						FROM
							order_queue_states oqs2
						WHERE
							oqs2.order_seq_no = oqs1.order_seq_no
							AND oqs2.order_status_cd||'' = 'RTL'
					)

----------------------------
Scott Brady
http://www.scottbrady.net/