SQL Server query is slow but when testing on tempdb, it works fast

Question

I have a slow query which contains more than 10 left join and 10 like operators. see bellow.

SystemContact has approx. 10K of data, the other two have more than 100k of data. The query looks not optimized, this is because it is generated by a program used to search SystemContact.

The problem that I faced is, the query is running very slow on our system which takes at least a minute to run. We have done updating statistics and defragging tables but it does not seem to improve performance much.

Initially I was thinking it might be related to the string DataType which is currently using nvarchar, but when I load all those tables with data into tempdb on the same server, the query runs about 2 seconds.

I am wondering what could be cause of the slowness?

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    select
    Id, Context1, Context2, Context3, LastModifiedBy

    from SystemContact
    left join SystemField as SF1 on SystemContact.Id = SF1.ContactId and SF1.FieldId = 1111
    left join SystemField as SF2 on SystemContact.Id = SF2.ContactId and SF2.FieldId = 5445
    left join SystemField as SF3 on SystemContact.Id = SF3.ContactId and SF3.FieldId = 3423
    left join SystemField as SF4 on SystemContact.Id = SF4.ContactId and SF4.FieldId = 6545
    left join SystemField as SF5 on SystemContact.Id = SF5.ContactId and SF5.FieldId = 5464
    left join SystemFieldText as SFT1 on SystemContact.Id = SFT1.ContactId and SFT1.FieldId = 546
    left join SystemFieldText as SFT2 on SystemContact.Id = SFT2.ContactId and SFT2.FieldId = 7565
    left join SystemFieldText as SFT3 on SystemContact.Id = SFT3.ContactId and SFT3.FieldId = 456
    left join SystemFieldText as SFT4 on SystemContact.Id = SFT4.ContactId and SFT4.FieldId = 457
    left join SystemFieldText as SFT5 on SystemContact.Id = SFT5.ContactId and SFT5.FieldId = 56
    left join SystemFieldText as SFT6 on SystemContact.Id = SFT6.ContactId and SFT6.FieldId = 85

    where
    (SF1.Value like '%john.smith@example.com%') or (SF2.Value like '%john.smith@example.com%') or (SF3.Value like '%john.smith@example.com%') 
    or (SF4.Value like '%john.smith@example.com%') or (SF5.Value like '%john.smith@example.com%') 
    or (SFT2.Value like '%john.smith@example.com%') or (SFT3.Value like '%john.smith@example.com%') or (SFT4.Value like '%john.smith@example.com%')
    or (SFT5.Value like '%john.smith@example.com%') or (SFT6.Value like '%john.smith@example.com%')

EDIT:

Here is the link to the Execution Plan : https://goo.gl/gICWir

Answer 1

Operations within tempdb are minimally logged so that transactions can be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down.

tempdb never has anything to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#:~:text=The%20tempdb%20system%20database%20is,%2Dvalued%20functions%2C%20and%20cursors.

Answer 2

CREATE NONCLUSTERED INDEX ix
    ON dbo.SystemFieldText (FieldId, ContactId) INCLUDE (value)
GO

SELECT id,
       Context1,
       Context2,
       Context3,
       LastModifiedBy
FROM dbo.SystemContact
WHERE EXISTS(
        SELECT *
        FROM dbo.SystemField SF
        WHERE SystemContact.id = SF.ContactId
            AND SF.value LIKE '%john.smith@example.com%'
            AND SF.FieldId IN (1111, 5445, 3423, 6545, 5464)
    )
    OR EXISTS(
        SELECT *
        FROM dbo.SystemFieldText
        WHERE SystemContact.id = SFT.ContactId
            AND SFT.value LIKE '%john.smith@example.com%'
            AND SFT.FieldId IN (546, 7565, 7565, 456, 457, 56, 85)
    )