Alister, could you please post your SQL statement to check that it's written in the most optimal way? (As an aside to everyone on this list, could you _please_ include the sample code in your question when asking about it? It's hard to guess what you've written and attempt to provide a solution otherwise and it often ends up in three or four wasted emails that could have been avoided with the bug spotted in the first email).

The reason I'm asking about your SQL is that the query times looked a little high for a contentObjectFind styled select, even for your hand written SQL (unless you're doing something particularly complicated or a number of sorts). If you're selecting on more than say two properties... I'd suggest writing your own SQL as I don't like the way cfa_contentObjectFind handles three or more property searches. It has been slightly improved in version 1.5, but still doesn't make me feel warm and fuzzy when attempting complex searches.

Other questions... did you migrate the content object database from somewhere beforehand? Are you sure your content object database table's indexes are still present (these can disappear if one copies data between databases incorrectly). Without table indexes, such select statements will certainly slow down.

-----Original Message----- From: nick_lansbury@cwcom.net To: Spectra-Talk Sent: 7/02/01 12:09 Subject: RE: speed problems

Hi,

I think you'll find that Alister is suggesting his SQL code is _faster_, not _slower_, than using cfa_ContentObjectFind.

N.

>Hi Alister... > >I could be how you're approaching your SQL... make sure you're not using >nested SELECTS or separate SQL statements, use equijoins! > >Assuming your database's optimizer understands transitivity, you should be

>able to join the objects and properties table according to the N-1 rule: > >Say you want to find objects based on three searchable properties, you can

>construct the following SQL statement: > >SELECT o.objectid, o.label >FROM objects o, properties p1, properties p2, properties p3 >WHERE o.objectid = p1.objectid >AND p1.objectid = p2.objectid >AND p2.objectid = p3.objectid >AND p1.propertyname = 'Amount' >AND p1.integerdata > 200 >AND p2.propertyname = 'ProductName' >AND p2.chardata = 'Vegemite' >AND p3.propertyname = 'Manufacturer' >AND p3.chardata = 'Kraft' > > >-----Original Message----- >From: Alister Lilley >To: Spectra-Talk >Sent: 6/02/01 17:42 >Subject: speed problems > >I am using spectra 1.01 and I have been using cfa_contentobjectfind, >this custom tag takes the page load speed from 849 milliseconds to over >2200 milliseconds, if I use sql and talk directly with the database I >get speeds of 875 milliseconds. > >Any suggestions of how to speed things up. > >Alister > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm ------------------------------------------------------------------------------ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/spectra_talk or send a message to spectra-talk-request@houseoffusion.com with 'unsubscribe' in the body.