• ollyjolly - Monday, August 21, 2017 9:21 AM

    I currently have a large query which is taking over an hour to run, and am attempting to optimize. Unfortunately, I do not have permissions to see the execution plan, but believe I have narrowed the issue down to several like statements that are being used (shown below)...

    Is it possible to move the like statements to a join to make it more efficient?

    Yes. I answered this earlier today . Here's a similar but more simple but less dynamic way to tackle this: 

    First some sample data:
    CREATE TABLE dbo.sometable
    (
    someId int identity primary key clustered,
        col1 varchar(100) not null
    );

    INSERT dbo.sometable(col1)
    VALUES ('xxx blue'),('ggg red gg'),('xxx'),('fff pink h'),('I''m yellow!!!');

    Next for our indexed view:
    CREATE VIEW dbo.vwSomeTable
    WITH SCHEMABINDING AS
    SELECT someId, col1
    FROM dbo.sometable
    WHERE col1 LIKE '%blue%'
    OR  col1 LIKE '%pink%';
    GO

    CREATE UNIQUE CLUSTERED INDEX uq_cl_vwSomeTable ON dbo.vwSomeTable(someId);

    Now, if you run the query below (just as an example) you'll notice that the optimizer is able to perform an index seek against the index on my view even without me referencing it in my query.

    SELECT someId, col1
    FROM dbo.sometable
    WHERE
    (
      col1 LIKE '%blue%' OR 
      col1 LIKE '%pink%'
    )
    AND someId < 4;


    The execution plan:

    This is one of those very cool but rarely mentioned benefits of indexed views. 

    You could one or more add nonclustered indexes to your view to speed things up even further. As is the case with all Indexes,  however, Indexed views do not come without the usual overhead so you'll have to test for yourself to see if the improved performance is worth the added overhead. There's also no guarantee that the optimizer will always chose your indexed view which is why you can consider referencing it directly along with a NOEXPAND query hint. I generally don't recommend query hints but will say that I've had great success with NOEXPAND (after plenty of testing 😉 On the topic of NOEXPAND - note this great article by Paul White: Another Reason to Use NOEXPAND hints in Enterprise Edition

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001