如何优化此查询?
作业表中有100万条记录。以下查询的执行时间为30秒。查询不够高雅,它会返回符合条件的记录数。请帮我优化。如何优化此查询?
DECLARE @MaxIdValue varchar(100)
DECLARE @SerchFilter varchar(100) = 'test'
DECLARE @TotalRowCount int = (SELECT
Count(*) AS [RowCount]
FROM
[Jobs]
INNER JOIN [Sites] ON [Jobs].[SiteId] = [Sites].[Id]
INNER JOIN [JobStatusHistory] ON [Jobs].[JobStatusHistoryId] = [JobStatusHistory].[Id]
INNER JOIN [JobStatuses] ON [JobStatusHistory].[StatusId] = [JobStatuses].[Id]
INNER JOIN [JobTypes] ON [Jobs].[JobTypeId] = [JobTypes].[Id]
LEFT JOIN [Customers] ON [Jobs].[CustomerId] = [Customers].[Id]
LEFT JOIN [Priorities] ON [Jobs].[PriorityId] = [Priorities].[Id]
LEFT JOIN [TaskTypes] ON [Jobs].[TaskTypeId] = [TaskTypes].[Id]
LEFT JOIN [Areas] ON [Sites].[AreaId] = [Areas].[Id]
WHERE
(
[Sites].[Active]=1
AND
(
[Jobs].[OwnedByCompanyId]=18
OR
[Jobs].[OwnedByCompanyId] = 0)
AND
[Jobs].[Active]=1
)
AND
(
[Customers].[Name] LIKE '%'[email protected]+'%'
OR
[Sites].[Name] LIKE '%'[email protected]+'%'
OR
[Sites].[Address1] LIKE '%'[email protected]+'%'
OR
[Sites].[Postcode] LIKE '%'[email protected]+'%'
OR
[Sites].[UPRN] LIKE '%'[email protected]+'%'
OR
[Sites].[Contact] LIKE '%'[email protected]+'%'
OR
[Sites].[Telephone] LIKE '%'[email protected]+'%'
OR
[Sites].[Work] LIKE '%'[email protected]+'%'
OR
[Sites].[MobileNumber] LIKE '%'[email protected]+'%'
OR
[Areas].[Description] LIKE '%'[email protected]+'%'
OR
[Jobs].[Id] LIKE '%'[email protected]+'%'
OR
[Jobs].[OrderNumber] LIKE '%'[email protected]+'%'
OR
[Jobs].[Description_PlainText] LIKE '%'[email protected]+'%'
OR
[Jobs].[JobNumber] LIKE '%'[email protected]+'%'
OR
[JobTypes].[Description] LIKE '%'[email protected]+'%'
OR
[TaskTypes].[Description] LIKE '%'[email protected]+'%'
OR
[Priorities].[Description] LIKE '%'[email protected]+'%'
)
)
您可能想要使用更多索引&全文目录。
如果您使用MS SQL 2008,您可以检查执行计划中缺少的索引。也从这里你可以看到为什么它需要这么长时间。
优化将是与@SerchFilter+'%'
更换'%'[email protected]+'%'
。
Where子句'%'[email protected]+'%'
阻止使用索引seek。
编辑:
你真的需要同时在所有这些领域后进行过滤?如果这不是一个数字,为什么麻烦搜索Telephone
领域的监管。
如果您使用的是SQL Server 2005或更高版本,则可以考虑使用FullText-Search。
非常感谢您的帮助。我刚刚删除'%'+ @ SerchFilter +'%',仍然需要30秒才能完成执行。我将阅读全文搜索。 – Larry 2012-08-15 11:55:19
假设您的连接列已适当编制索引,我怀疑问题在于众多的LIKE。不幸的是,这些数据库操作相当昂贵。
做完类似的东西,我相信你或许能够做这样的事情来获得稍好的性能:
SELECT COUNT(*) FROM ... WHERE COL1 + COL2 + COL3 + COL4 + COL5 LIKE '%SEARCH%'
不是:
SELECT COUNT(*) FROM ... WHERE
COL1 LIKE '%SEARCH%'
OR COL2 LIKE '%SEARCH%'
OR COL3 LIKE '%SEARCH%'
OR COL4 LIKE '%SEARCH%'
OR COL5 LIKE '%SEARCH%'
只有当所有这些列都不是NULL时。 – 2012-08-15 12:17:41
就像他们在我的情况。如果任何字段都是可空的,你显然需要一个ISNULL或COALESCE在这里... – 2012-08-15 12:29:35
现在我对全文索引有一个基本的了解,关于我从msdn读取的内容,我有一些问题。 – Larry 2012-08-15 13:39:44
什么数据类型的每个字段的定义?就像描述字段文本或varchar字段一样? – 2012-08-15 11:46:45