How can I filter my query on a left joined table, if I don't know the exact value I'm searching? I had a hard time coming up with an appropriate title and it might be hard for me to explain the issue, but I'll try to be as clear as possible.

I have a dynamic query that is pulling assets based on their asset title or one of many "identifiers" that are attached to that asset. Every asset as a "snapshot" ID that is tied to a asset identifier snapshot table, that is then tied to a version table that holds the actual value. That relationship looks like this:

SELECT
    *
FROM
    vwAsset AS Asset
        INNER JOIN dbo.tblAssetIdentifierSnapshot ON tblAssetIdentifierSnapshot.AssetSnapshotId = Asset.AssetSnapshotId
            INNER JOIN dbo.tblAssetIdentifierVersion AS AssetIdentifierVersion ON AssetIdentifierVersion.AssetIdentifierVersionId = tblAssetIdentifierSnapshot.AssetIdentifierVersionId


The value in the tblAssetIdentifierVersion table is what I'm trying to get at. Here is what the full query looks like:



And here's a pastebin.

If I run this query without the two highlighted joins and remove the OR, it runs instantly. But adding these joins makes the query run for 25+ seconds.

When I run this through Sentry Plan Explorer, it shows that some 274K+ rows are being searched for each join, before it finally filters down to the 137 it returns.



This is the query columns that sentry plan explorer shows:



I'm not an expert, so take this with a grain of salt, but every looks mostly okay. I don't think anything can be done about index scans, right?

Is there any way to filter down the left joins more without knowing the snapshot ID or the versions that are tied to the asset?



Here's the execution plan: https://www.brentozar.com/pastetheplan/?id=HJtsYtGLe
I don't see any way in the immediate query to assist performance. You have indices on the requested columns and it has to do a full scan to figure out all of the data.

There are two solutions I see, both take architecture changes. 


Full Text Search
Processing and mapping tables with foreign key implementation.


If you are able to create a mapping table and a key column on the tblAssetIdentifierVersion and tblAssetIdentifierSnapshot table, you could have a process that runs that helps map to the table. This causes processing time that would typically be done in the select to be done in the update as it does not resolve the like operator in the where clause. You'll have to weigh whether this is a good option for your database or Full Text Search is a better idea or if it's best to be left alone.

Example of the mapping table:

DECLARE @m_people TABLE
    (
        M_Job_ID INT IDENTITY(1 , 1)  
        ,M_JOB VARCHAR(255)
    )

DECLARE @people TABLE
    (
        Name VARCHAR(255)
        ,JOB VARCHAR(255)
        ,Job_ID INT 
    )


INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Timmy' , 'Data Slave' , NULL)

INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Joann' , 'Manager' , NULL)    

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Data Slave')          

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Manager')         

UPDATE  @people
SET Job_ID = M_job_id
FROM @m_people
WHERE JOB LIKE '%' + m_job + '%'

SELECT * FROM @people
SELECT * FROM @m_people


Note: Due to variable tables, the objects due not exist in the sys.objects table so there is no obvious foreign key constraint declares. The FK would be Job_ID and M_Job_ID if that is not obvious.