I have a table that includes PartNbr and Description.  I want to the user
to be able to type in a few keywords and have a query return the parts
that have the most matches of keywords in the Description field.  I
decided to do this by creating a new table Word that includes PartNbr and
Keyword then use the IN operator to return a count of rows from the Word
table that contain those keywords.  This works great, however it takes
forever to run the program to build the Word table.  I am using VB4
Enterprise as the front end, SQL Server 6.5 as the back end and RDO as the
connection between the two.  I have tried everything under the sun to
optimize my RDO access (newest ODBC drivers, cursor-less access, etc.)
There are 9000 rows in the Part table, and the build program takes about
an hour to run.  This is all fast hardware (133 pentium) with big memory
64MB.  I know that ideally I should use some type of update query or store
procedure to do this, but for the like of me I can't figure out a way to
parse words in SQL or transact SQL (parsing needs to seperate at spaces
and remove a host of special characters such as ,/-etc.).  It seems that
SQL Server and VB4 are great at doing single queries, but really bog down
when it comes to mass updates like this.  Can anyone help me find a way to
make this process faster, or am I looking at this problem all wrong?

Thanks
Kevin

In my opinion:
Mass updates (or _batch_ processes) are best run at the server if they
read a lot of data to update a lot (or a little). This can either be done
through creative SQL programming (db crunches) or through stored
procedures, etc. However, if that is not possible, then I recommend
writing a batch program (in COBOL -gasp!- for example, or C for the C
zealots) that might run either on the client or the server.
It seems that slow links are:
1) retrieving a lot of data across the network...
2) running a lot of VB code a lot of times
Try to eliminate those and you might have better success...
My 2 cents...
Early Stephens
Corporate Technology Director - Manatron, inc