another performance tuning request

Question

another performance tuning request

Oblio Leitch

SSCrazy

Points: 2137
More actions
January 23, 2007 at 6:25 am

#116029

I've got a list of strings I need to look up. They are a little variable, and the best way I've found to correlate between the search criteria and a hit in the db would look like this "401-9-05%cnjv_". There are a few possible characters in where the % is, and there will be one final one at the end; otherwise the field should match. My source is, alas, an xls that I'm fetching and inserting in to a table variable. However, the real performance loss is when I try getting matches. Here's one example match query I've tried -
DECLARE @sample TABLE(criteria varchar(15), match tinyint)
UPDATE @sample
SET match=(
SELECT COUNT([target])
FROM realTable
WHERE [target] LIKE criteria
)
The trouble is, just matching 183 records is taking a full 12 seconds. Does anyone know of a better way?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Oblio

So you have two "unknowns" - the % in the middle, which can be any number of characters, and one single unknown character at the end. The best way of doing what you want depends on whether this is a regular task or a one-off, and on whether realTable is frequently updated.

If you are going to be doing this a lot, then try creating an indexed view of realTable, with the two columns, one for each unknown. Then you can strip out the "unknown" data from your spreadsheet into your table variable and test for matches with the indexed view. Hope that makes sense. Beware, though - if realTable gets a lot of updates, the overhead of maintaining the indexed view might be too much to make this viable.

John

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 2

I would also try turning the table variable into a #temp table. I have seen different performance based on using the different table expressions. You should also have an index on the realtable target column. That should really speed up performance if you currently do not have an index on that field.

Tom

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 3

Thanks for the reply.

Forgive my naiveté, but I'm not really sure what an "indexed view" is. Is that a view that puts substring(target,1,9) into one column, and substring(target,11,4) and then match the two fields?

The upside is that realTable doesn't get updated but once a day, downside is it gets wiped and reloaded.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Yes, that is the essence of what the indexed view is. See Books Online for more details. Of course the other important thing about it is that it is indexed - important for all the searching and/or joining you will be doing on it. If realTable is cleared down and recreated then so much the better, especially if it's done during a maintenance window (ie at night). Just add two extra steps to the job: one to drop the indexed view before the table is cleared down, and another to recreate it at the end.

John