That's trolling as in "Fishing", rather than "Being nasty just to get a
rise out of people" ;)
I have a need to create some sort of keyword-searchable index for a CF
application, so that the user can enter a person's first and last name
into a form and search on it. I'm not particular as to what kind, just
something better than various permutations of
"where lastname like '%#form.lastname#%' and firstname like
'#form.firstname#%'..."
The data we get in is from various thrid parties, and is choick full o'
errors- misspellings, lastname in the firstname column, first name
omitted, etc. Unless someone knows better- and I'm not claiming to be
an expert- a database full-text index or a Verity collection seems to be
the way to go.
Problem is, the data is stored in an Oracle 8i database which, to the
best of my knowledge and efforts, does not support full-text indexing.
Second problem: Indexing a Verity collection is taking about 3 hours.
I've only used Verity to index files in a folder, but I don't remember
it taking near this long to do- even with folders with large numbers of
files. There will be times when the collection would need to be
reindexed over the course of the day- three hours is way too long.
So first of all, here's the query and code I'm using to index the
collection. Am I doing something incorrectly that is causing such a
long index time?
<cfquery name="qryNames" datasource="datasource">
select id, trim(firstname)|| ' ' || trim(lastname) AS FullName
FROM people
WHERE level1org=2000 and status='a'
</cfquery>
<cfindex collection="names" action="update" title="Names" key="id"
body="id,FullName" type="Custom" query="qryNames"
status="verResults">
The query returns just over 48,000 rows, and is using an index on
Level1org and status, FWIW.
Barring someone pointing out something I've missed with the Verity
collection or presenting some other method that I haven't thought of, my
only other idea is to schedule a DTS package to import the table into
SQL Server and create a full-text index there. The DTS Package would
have to run periodically thoughout the day to catch any updates or new
records in the Oracle table, which would also mean reindexing the SQL
Server full-text index. There ought to be a better solution than that.
Just for the record, permanantly storing the People table in SQL Server
isn't an option, nor is an Oracle upgrade.
Incidently- the most recent <cfindex> finished up while writing this.
Start time 07:41:36 Finish Time: 10:25:34.
Matt Osbun
Web Developer
Health Systems, International
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213272
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
