SQL Server查询速度较慢，但在tempdb上进行测试时，它的工作速度很快

Question

我有一个很慢的查询，它包含10多个左连接和10个like操作符。请参见bellow。

SystemContact有大约10K的数据，另外两个有超过100k的数据。查询看起来并未优化，这是因为它是由用于搜索SystemContact的程序生成的。

我面临的问题是，查询在我们的系统上运行非常慢，至少需要一分钟才能运行。我们已经完成了更新统计数据和对表进行碎片整理，但这似乎并没有太大提高性能。

最初我认为这可能与字符串DataType有关，该字符串当前正在使用nvarchar，但当我将所有包含数据的表加载到同一服务器上的tempdb中时，查询运行了大约2秒。

我想知道是什么原因导致速度慢？

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%')

编辑：

以下是执行计划的链接：https://goo.gl/gICWir

Answer 1

对tempdb中的操作进行最低限度的日志记录，以便可以回滚事务。每次启动SQL Server时都会重新创建tempdb，以便系统始终以干净的数据库副本启动。断开连接时，将自动删除临时表和存储过程，并且在系统关闭时没有任何连接处于活动状态。

tempdb永远不会将任何内容从一个SQL Server会话保存到另一个会话。不允许对tempdb执行备份和恢复操作。

https://docs.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)
    )