January 13, 2011 at 3:48 pm
I have some data like
'location1' as label ,'a lot test here like PC,87,89,25,38;35,87,13,D6 etc' as data
Via join, I am comparing values in one table to this such as
select label, data from table
where charindex(val, data) > 0
but this also works:
select label, data from table
where data like '%' + val + '%'
Which is best/fastest? I know that the data is not normalized appropriately but I can't control that so I have to work with what I have. Also, I know that there is always some form of delimiting such as , or ; or - and that val never contains these.
Thanks
January 13, 2011 at 4:02 pm
Both versions will cause a table scan since the search condition is not SARGable.
Maybe it's more efficient to shred the data and query the new table with proper indexing.
It depends on how many rows you have with that delimited string (conider the string length, too) and how many rows you'd have if the string would have been shredded (including evaluation of the shredded string length).
January 13, 2011 at 4:12 pm
LutzM, that other thread you have posted in I started is an attempt at doing just that. But, I have a bunch of columns that are in the csv-value format and unpivoting them all will create quite a table.
excuse me if I am spreading my questions around, but what I am really trying to do here is capitalize on any help I can get to LEARN how to do all this rather then present a problem and get a complete solution.
If I normalize the data into another table I can index it! 🙂
btw, the other table (from the other thread) which is the one I compare against here is very static. It gets very few updates/deletes/changes, maybe 10 per month. I was thinking I would put a trigger on the original table to update the normalized version.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply