Actually, The where clause processes bottom up.
So, if possible, for best results place your most restricting clauses LAST and filter out the greatest amount of data. Then the next where clause up from the bottom has the least amount of records possible to compare against and will perform faster. HTH, t ********************************************************************** Tyler M. Fitch Certified Advanced ColdFusion 5 Developer ISITE Design, Inc. 615 SW Broadway Ste. 200 Portland, OR 97205 503.221.9860 ext. 111 http://isitedesign.com ********************************************************************** -----Original Message----- From: Robert Everland [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 14, 2002 9:06 AM To: CF-Talk Subject: RE: cfqueryparam - order of where/and I personally have found that when I have a huge query it makes it faster if I do the joins that get rid of the most data first. So say I have a query like this. Select * >From Accounts, Sales Where accounts.address = sales.address And salesdate = #now()# And item = 13882 I would redo the query so that it would get rid of the data it doesn't need first then do the join at the end. Otherwise it joins those tables with all the data, then gets rid of the rows you don't need. Select * >From Accounts, Sales Where salesdate = #now()# And item = 13882 And accounts.address = sales.address Results may vary. Robert Everland III Dixon Ticonderoga Web Developer Extraordinaire -----Original Message----- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 14, 2002 12:03 PM To: CF-Talk Subject: cfqueryparam - order of where/and I read an article by Ben Forta in CFDJ about using <cfqueryparam> I wanted to test it out, but had a question, well actually two questions. First has nothing to do with CFqueryparam 1) Does where/and clauses ORDER matter? ie select * from table where id = 'xxx' and data1 = 'xxxx' Verses select * from table where data1 = 'xxxx' and id = 'xxx' Does one find the information quicker? (switching the where and the and clauses?). Which takes me to my second question. Does the following matter? select * from table where data1 = <cfqueryparam value="#xxxx#" cfsqltype="CF_SQL_VARCHAR"> and id = 'xxx' Verses select * from table where id = 'xxx' and data1 = <cfqueryparam value="#xxxx#" cfsqltype="CF_SQL_VARCHAR"> (Note the where and the and clause switches placement). Does placement matter? Thanks Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector - Commerce Builder ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
