EXISTS is a bit slow... but it shouln't run forever... do you have an index on the cust_po field? That might help thing a LOT.
Russ > -----Original Message----- > From: Eric J. Hoffman [mailto:[EMAIL PROTECTED] > Sent: Monday, February 27, 2006 12:51 PM > To: CF-Talk > Subject: SQL Exists, better way to run query? > > I have a long query that checks to see what someone entered in the > previous form and only searches by that. > > > > In one area, the problem one of course, it searches by Purchase Order > number. > > > > <cfif form.po NEQ '' AND form.comp_name EQ ''> > > (cu.cust_openpo LIKE > '%#form.po#%' > > OR EXISTS (Select > cust_po from customer_orders where cust_po = '#form.po#')) > > </cfif> > > > > So it looks for a stored PO from the customer table from the main join; > but they also want to be able to check for any previous order that > contained the purchase order number, so I fumbled with an Exists query. > > > > It seems to make it run *forever*. What is a better way to find it, > make it a left outer join in the main joins or what? Some other > method? > > > > Thanks! > -------------------------------------------------------- > > > Eric J. Hoffman > Managing Partner > 2081 Industrial Blvd > StillwaterMN55082 > mail: [EMAIL PROTECTED] > www: http://www.ejhassociates.com > tel: 651.717.4105 > fax: 651.717.4115 > mob: 651.245.2717 > > -------------------------------------------------------- > > This message contains confidential information and is intended only for > [EMAIL PROTECTED] If you are not cf-talk@houseoffusion.com you > should not disseminate, distribute or copy this e-mail. Please notify > [EMAIL PROTECTED] immediately by e-mail if you have received this > e-mail by mistake and delete this e-mail from your system. E-mail > transmission cannot be guaranteed to be secure or error-free as > information could be intercepted, corrupted, lost, destroyed, arrive late > or incomplete, or contain viruses. Eric J. Hoffman therefore does not > accept liability for any errors or omissions in the contents of this > message, which arise as a result of e-mail transmission. If verification > is required please request a hard-copy version. > -------------------------------------------------------- > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233553 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
