What about taking a set approach? Load the file into a staging table and then do the rate resolution against the entire staging table. Afterwards, move the out of the staging table.
-Mike
>>Keith,
>>Thanks for your questions.
>>
><snip>
>
>>
It's not the text to table that is taking the time, it's the processing of each text line. Basically we're rating each line of information based on several lookups into underlying rate tables. Due to defaults (if a rate record exists for an over-ride field value, use it; else use the record for a default field value) and best case matches we have to use four sequential SQL reads to get to the rate record actually required. All of this is in a single Stored Procedure.
>
>It sounds like there is not a great technical reason for moving to SQL Server. However, from your description of the process, perhaps you will see some speed gains (on either platform) by flattening the lookup tables.
>
>Create a new table that is a cross-join of all of the lookup tables and use all of the lookup columns in one index. This will cut down on the number of queries required to match up the new record. Also, it is generally faster to perform the lookup when the new record is being inserted rather than loading the table and then processing it in a separate step.
>
>HTH.