Hi,
We had to write a stockist search function on a Spectra site that allowed
searching by type of product stocked (keywords) and sorting the results
according to the geographical distance between the stockist's postcode and a
postcode entered by the user using Pythagoras' theorem (we had a table of
postcode lat/lon centroids to do this). Using cfa_contentObjectFind it was
easy enough to get a list of OIDs for the stockists matching the correct
product types. To do the second part of this query we wanted to sort on a
calculated query field, but the only way to combine this with the results of
the cfa_contentObjectFind was to have a "where objectid in (list of 100's of
stockist objectids)" which apart from being truly ugly performed like a dog.
To work around this we came up with a solution that some of you may find
useful. We created a searchable keywords property on the stockist object,
took the keywords out of stkeywords, sorted them alphabetically and stored
them as a list in the keywords property. We needed to increase the length
of the column in the propeties table to accomodate the list. Now to do a
keyword search, take the list of keywords you are looking for, sort
alphabetically using "%" as a delimiter. Then you can add a "where
properties.text like '%kw1%kw2%kw3%'" clause to your select statement. This
was really fast (even with the pythagoras sort) against approx 1700 stockist
objects. I don't know how well it would scale - possibly with the right
indexes set up this would be viable against much larger databases.
Cheers,
Robin Hilliard
Senior Product Support Engineer - Asia Pacific
Macromedia, Inc.
-----Original Message-----
From: David Aden [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 24 May 2001 3:22 AM
To: Spectra-Talk
Subject: RE: Performance
Marc,
We're pretty consistently used a relational model for storing hierarchical
data in Spectra sites -- at least for management of pages. but my
inclination is that you are correct about a relational model for things like
metadata functioning better than the current model.
david aden
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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.
