Home Forums Article Discussions Article Discussions by Author Discuss content posted by Francis Rodrigues ROW_NUMBER(): An efficient alternative to subqueries – Scenario 2 RE: ROW_NUMBER(): An efficient alternative to subqueries – Scenario 2

  • June 28, 2009 at 6:37 pm

    #1016569

    FreeHansje (6/2/2009)

    Very educational article. Lately I have to deal with performance issues created by similar queries as given in this article. I like to read more about subqueries and their alternatives. For instance, could row_number be used to search in a string-column of a table? I now have something like:

    DECLARE @var1 AS String, @var2 AS String

    SELECT c1, c2, c3 FROM Table

    WHERE StringColumn LIKE '%' + @var1 + '%' AND StringColumn LIKE '%' + @var2 + '%'

    I'm trying several aproaches and I'm sure I'll either figure it out or conclude it can't be made pretty.

    Tnx again.

    For this scenario I'd say your best bet would be to use a method like n-gram search. Using LIKE '%' + @var1 + '%' will result in inefficient scans. Basically you lose the efficiencies associated with indexing. If you use a method like n-grams, you can index the resultant fixed-length character strings (3 characters = trigram, etc.) and retrieve results similar to the ones you would get with LIKE '%' + @var1 + '%'.

    When I do n-gram style searches in T-SQL I usually use ROW_NUMBER() to number each of the n-grams when I split words up.