I've run into this issue on several occassions and this is the algorithm I use...
For example, I have a document library where users can search by company, division, department, dates and keywords. The company, division, departement and dates are all stored in the database record for a document that exists in the document library. The Verity collection indexes all the actual documents in the document library. Regardless of whether keywords are used, I perform a database query based on the other form fields. If no keywords are used, I'm done and this is the recordset I display. If keywords are submitted, I do a cfsearch using those keywords. Then I display the cfsearch results only if the ID of the cfsearch record is also in the database recordset. Hence, I'm finding the intersection of the 2 record sets. I hope this helps. Jackson Moore [EMAIL PROTECTED] --- Original Message --- From: [EMAIL PROTECTED] To: CF-Talk <[EMAIL PROTECTED]> Cc: Sent: Wed, 13 Mar 2002 09:28:48 -0000 Subject: RE: A Verity SQL Combo Question - Can this be sped up? Could try using MS indexing instead of Verity, I've found it better anyway. -----Original Message----- From: Mallory Woods [mailto:[EMAIL PROTECTED]] Sent: 08 March 2002 16:13 To: CF-Talk Subject: A Verity SQL Combo Question - Can this be sped up? Greetings all.. We have an interesting verity search/SQL search issue and I was wondering if anyone has any hints on how this possibly could be sped up.. Ok.. some background.. The Keyword search is done in verity and its fast by itself.. The other searches are fast and are regular SQL.. when they are both combined they take a long time.. Here's what happens.. The Verity search returns the Keys which are equal to the ID field in the SQL DB.. �if the keyword field is not blank then it add the keys in to the main query example query.. -- SELECT � � EPSTable.posting_date, agency_, office_, classcod_, subject_, solnbr_, epsid, (select top 1 par_id from par where rfpnumber=replace(epstable.solnbr_,'-','') and (rfpnumber is not null or rfpnumber<>'' or rfpnumber<>'N/A')) parid FROM �EPSTable WHERE (1 = 1) AND EPSTable.epsid IN (13913,11967,15428,15407,15057,15036,18538,14881,19636,18330,9411,16568, 7593,19812,19391,17230,16774,10795,13469,6393,11616,11293,18112,16529,15 461,15090,13871,11925,10778,9032,17558,16233,13893,13310,13069,11947,107 79,9771,8907,7441,2676,16772,12398,10547,7974,5239,4158,20497,20400,2039 9,20051,19996,19804,19640,19610,19299,18582,18300,18299,18291,18258,1812 3,18075,17862,17738,17111,17026,17014,16993,16982,16846) AND CLASSCOD_ = '10' ORDER BY POSTING_DATE -- As you can see it returns a whole lot of Keys which are included in the query.. this slows the whole query down.. �We have the DB indexed and have tried other things to speed this up.. any suggestions on getting this going faster? Thanks in advance.. Mallory Woods INPUT Inc ------------------------------------------------------------ ______________________________________________________________________ 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/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
