SQL命令运行缓慢。需要帮助识别缓慢
我有一个SQL查询需要几个小时才能运行。基本上,我期待在我们的“页面”表格中找到具有特殊字符的文档(PDF)。我在PDFFile表中找到这些PDF。执行第一个AND子句将在16秒内返回。添加第二个子句使SQL花费3小时。试图找出我做错了什么。任何帮助是极大的赞赏。SQL命令运行缓慢。需要帮助识别缓慢
查询:
select b.bookletname, b.trackingID, b.[version], s.name
from page p
inner join section s on s.id = p.sectionid
inner join booklet b on b.id = s.bookletid
INNER JOIN [user] u ON b.CreatedBy = u.id
INNER JOIN client c ON c.id = u.clientID
WHERE u.clientID = 2
AND p.[filename] IN (
SELECT DISTINCT pdf.[FileName]
FROM PDFFile pdf
WHERE pdf.fileName LIKE '%\<%' ESCAPE '\'
OR pdf.fileName LIKE '%\>%' ESCAPE '\'
OR pdf.fileName LIKE '%\"%' ESCAPE '\'
OR pdf.fileName LIKE '%\%%' ESCAPE '\'
OR pdf.fileName LIKE '%''%' ESCAPE '\'
OR pdf.fileName LIKE '%\*%' ESCAPE '\'
OR pdf.fileName LIKE '%\+%' ESCAPE '\'
OR pdf.fileName LIKE '%\\%' ESCAPE '\'
OR pdf.fileName LIKE '%\/%' ESCAPE '\'
OR pdf.fileName LIKE '%\:%' ESCAPE '\'
OR pdf.fileName LIKE '%\?%' ESCAPE '\'
OR pdf.fileName LIKE '%\[%' ESCAPE '\'
OR pdf.fileName LIKE '%\]%' ESCAPE '\'
OR pdf.fileName LIKE '%\|%' ESCAPE '\'
)
OR p.[PDF_File_Name] IN (
SELECT DISTINCT pdf.[FileName]
FROM PDFFile pdf
WHERE pdf.fileName LIKE '%\<%' ESCAPE '\'
OR pdf.fileName LIKE '%\>%' ESCAPE '\'
OR pdf.fileName LIKE '%\"%' ESCAPE '\'
OR pdf.fileName LIKE '%\%%' ESCAPE '\'
OR pdf.fileName LIKE '%''%' ESCAPE '\'
OR pdf.fileName LIKE '%\*%' ESCAPE '\'
OR pdf.fileName LIKE '%\+%' ESCAPE '\'
OR pdf.fileName LIKE '%\\%' ESCAPE '\'
OR pdf.fileName LIKE '%\/%' ESCAPE '\'
OR pdf.fileName LIKE '%\:%' ESCAPE '\'
OR pdf.fileName LIKE '%\?%' ESCAPE '\'
OR pdf.fileName LIKE '%\[%' ESCAPE '\'
OR pdf.fileName LIKE '%\]%' ESCAPE '\'
OR pdf.fileName LIKE '%\|%' ESCAPE '\'
)
OR p.[PDFName] IN (
SELECT DISTINCT pdf.[FileName]
FROM PDFFile pdf
WHERE pdf.fileName LIKE '%\<%' ESCAPE '\'
OR pdf.fileName LIKE '%\>%' ESCAPE '\'
OR pdf.fileName LIKE '%\"%' ESCAPE '\'
OR pdf.fileName LIKE '%\%%' ESCAPE '\'
OR pdf.fileName LIKE '%''%' ESCAPE '\'
OR pdf.fileName LIKE '%\*%' ESCAPE '\'
OR pdf.fileName LIKE '%\+%' ESCAPE '\'
OR pdf.fileName LIKE '%\\%' ESCAPE '\'
OR pdf.fileName LIKE '%\/%' ESCAPE '\'
OR pdf.fileName LIKE '%\:%' ESCAPE '\'
OR pdf.fileName LIKE '%\?%' ESCAPE '\'
OR pdf.fileName LIKE '%\[%' ESCAPE '\'
OR pdf.fileName LIKE '%\]%' ESCAPE '\'
OR pdf.fileName LIKE '%\|%' ESCAPE '\'
)
用exists
重写这个,并简化like
s。 SQL Server允许你在模式中有字符列表,并且你所拥有的大部分字符都不需要转义(我希望我的结果模式完全正确)。
我会建议:
select b.bookletname, b.trackingID, b.[version], s.name
from page p inner join
section s
on s.id = p.sectionid inner join
booklet b
on b.id = s.bookletid inner join
[user] u
on b.CreatedBy = u.id inner join
client c
on c.id = u.clientID
where u.clientID = 2 and
(exists (select 1
from PDFFile pdf
where pdf.fileName LIKE '%[<>"''*+\\/:?\[\]|]%' ESCAPE '\' and
pdf.fileName = p.filename
) or
exists (select 1
from PDFFile pdf
where pdf.fileName LIKE '%[<>"''*+\\/:?\[\]|]%' ESCAPE '\' and
pdf.fileName = p.PDF_File_Name
) or
exists (select 1
from PDFFile pdf
where pdf.fileName LIKE '%[<>"''*+\\/:?\[\]|]%' ESCAPE '\' and
pdf.fileName = p.PDFName
)
);
你要确保你的PDFFile(fileName)
性能有一个索引。
我也改变了逻辑。您的原始逻辑像where A and B or C or D
这被解析为where (A and B) or C or C
。我将其更改为where A and (B or C or D)
。这也可能是导致性能瓶颈的原因。
嘿@Gordon Linoff。绝对对你的答案感兴趣,但它有语法错误... Msg 102,Level 15,State 1,Line 14 '\'附近的语法不正确。 消息105,级别15,状态1,行24 字符串'和 pdf.fileName = p之后未封闭的引号。 PDF名称 ) ) '。 – 2014-11-07 01:22:16
不知道如何解决,或者我会。 – 2014-11-07 01:23:49
修正了它。这真太了不起了。谢谢! – 2014-11-07 19:56:54
尝试取出三次表命中,并将该选择到临时表,因为你使用的是相同的选择要检查三列。所以你可以在任何情况下使用临时表。
SELECT DISTINCT pdf.[FileName]
INTO #temp
FROM PDFFile pdf
WHERE pdf.fileName LIKE '%\<%' ESCAPE '\'
OR pdf.fileName LIKE '%\>%' ESCAPE '\'
OR pdf.fileName LIKE '%\"%' ESCAPE '\'
OR pdf.fileName LIKE '%\%%' ESCAPE '\'
OR pdf.fileName LIKE '%''%' ESCAPE '\'
OR pdf.fileName LIKE '%\*%' ESCAPE '\'
OR pdf.fileName LIKE '%\+%' ESCAPE '\'
OR pdf.fileName LIKE '%\\%' ESCAPE '\'
OR pdf.fileName LIKE '%\/%' ESCAPE '\'
OR pdf.fileName LIKE '%\:%' ESCAPE '\'
OR pdf.fileName LIKE '%\?%' ESCAPE '\'
OR pdf.fileName LIKE '%\[%' ESCAPE '\'
OR pdf.fileName LIKE '%\]%' ESCAPE '\'
OR pdf.fileName LIKE '%\|%' ESCAPE '\'
SELECT b.bookletname,
b.trackingID,
b.[version],
s.NAME
FROM page p
INNER JOIN section s
ON s.id = p.sectionid
INNER JOIN booklet b
ON b.id = s.bookletid
INNER JOIN [user] u
ON b.CreatedBy = u.id
INNER JOIN client c
ON c.id = u.clientID
WHERE u.clientID = 2
AND p.[filename] IN (SELECT *
FROM #temp)
OR p.[PDF_File_Name] IN (SELECT *
FROM #temp)
OR p.[PDFName] IN (SELECT *
FROM #temp)
这工作。明确的性能提升。从3小时到10分钟。试着用+1来尝试其他解决方案,看看他的解决方案是否表现更好。谢谢你的答案d(-_-)b – 2014-11-07 01:20:19
有那么多'LIKE',这怎么可能不慢呢? – Lamak 2014-11-06 16:49:43
为什么您的PDF表格有两个单独的文件列?如果其中一个是空的,你可以使用'COALESCE()'吗? – Kevin 2014-11-06 16:56:11
@Lamak:特别是用'LIKE'和一个前导'%' - >确保**没有索引**可以使用! – 2014-11-06 17:03:48