Thanks Nick.
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: [EMAIL PROTECTED]
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 [EMAIL PROTECTED] with 'unsubscribe' in the body.
