查询和老式数据库的复杂性

Question

我最近开始在一家大型政府机构工作。我学了4年软件开发。在那段时间里，我们浏览了基本的老式数据库使用和数据库优先设计，我可以写一个基本的查询，但我们没有进入非常复杂的数据库设计。我们更多地关注更新的技术，如代码优先的实体框架和模型优先等。

现在，我所在组织的各种IT政策几乎使所使用的技术和技术的任何更改或进步都变得不可能。就在今天早上，我收到了一个工作请求，要求解决一个从数据库调用信息的网络应用程序超时的问题。我很快确定网页背后的代码正在调用一个存储过程，该过程正在查询一个有20多万条记录的表，所以我打开它，看看是否可以衡量该过程的效率。

这个过程的复杂性远远超出了我的复制能力，而且几乎太复杂了，甚至无法理解！DB管理员通过向表添加索引来帮助我，所以我不会在这里要求技术解决方案。接下来，在我继续之前，先看看有问题的存储过程：

CREATE PROCEDURE [dbo].[stp_RandomisationResultCount]
@noOfRows INT,
@subcouncil INT,
@wards VARCHAR(MAX) = NULL,
@suburbs VARCHAR(MAX) = NULL,
@courts VARCHAR(MAX) = NULL,
@skills VARCHAR(MAX) = NULL,
@isDisabled VARCHAR(1) = NULL,
@hasQualification VARCHAR(1) = NULL,
@gender VARCHAR(1) = NULL,
@age INT = NULL
AS
BEGIN
SELECT  COUNT(tblJobSeekerDetails.JobSeeker_ID) resultCount
FROM tblJobSeekerDetails LEFT JOIN tbl_lnk_JobSeeker_OtherSkills 
ON tblJobSeekerDetails.JobSeeker_ID = tbl_lnk_JobSeeker_OtherSkills.JobSeeker_ID LEFT JOIN tblOtherSkills 
ON tbl_lnk_JobSeeker_OtherSkills.OtherSkillsID = tblOtherSkills.OtherSkillsID LEFT JOIN tblJobSeekerEmploymentHistory
ON tblJobSeekerDetails.JobSeeker_ID = tblJobSeekerEmploymentHistory.JobSeekerID
WHERE (tblJobSeekerDetails.SubCouncilID = @subcouncil)
AND ((WardID IN (SELECT s.Item                       FROM ufn_SplitIntArray(@wards, ',') s)) OR (@wards IS NULL))
AND ((Suburb IN (SELECT s.Item                       FROM ufn_SplitIntArray(@suburbs, ',') s)) OR (@suburbs IS NULL))
AND ((RoadType IN (SELECT s.Item FROM ufn_SplitIntArray(@courts, ',') s)) OR (@courts IS NULL))
AND ((tblOtherSkills.OtherSkillsID in (SELECT s.Item FROM ufn_SplitIntArray(@skills, ',') s)) OR (@skills IS NULL))
AND ((Disability = @isDisabled) OR (@isDisabled IS NULL))
AND ((HasTertiaryQualification = @hasQualification) OR (@hasQualification IS NULL))
AND ((Gender = @gender) OR (@gender IS NULL))
AND ((ISNUMERIC(IdentityNumber) = 1 AND 
IdentityNumber NOT LIKE '%.%' AND 
DATEPART(YEAR, GETDATE()) - (CONVERT(INT, '19' + SUBSTRING(IdentityNumber, 1, 2), 100)) = @age) OR (@age IS NULL))
AND (TypeID = 1)--Jobseekers only
AND (tblJobSeekerDetails.IsExcludedFromRandomisation <> 1 OR tblJobSeekerDetails.IsExcludedFromRandomisation IS NULL)--Explicitly excluded jobseekers
AND (tblJobSeekerDetails.JobSeeker_ID not in (SELECT DISTINCT tj.JobSeeker_ID --Exclude Jobseekers as per stipulated exclusion periods
FROM tblJobSeekerDetails tj INNER JOIN tblJobSeekerEmploymentHistory wh
ON tj.JobSeeker_ID = wh.JobSeekerID
WHERE ((DATEDIFF(DAY, wh.DateOfEmploymentFrom, wh.DateOfEmploymentTo) <= 14 AND DATEDIFF(MONTH, wh.DateOfEmploymentTo, GETDATE()) <= 3 ))
OR
((DATEDIFF(DAY, wh.DateOfEmploymentFrom, wh.DateOfEmploymentTo) > 14 AND DATEDIFF(MONTH, wh.DateOfEmploymentTo, GETDATE()) BETWEEN 4 AND 6))
OR
(GETDATE() BETWEEN wh.DateOfEmploymentFrom AND wh.DateOfEmploymentTo)
OR (wh.DateOfEmploymentFrom > GETDATE() OR wh.DateOfEmploymentTo > GETDATE())
GROUP BY tj.JobSeeker_ID))
END

现在，这已经足够让我害怕了。我可以大致理解它应该做什么，但目前我不可能提高它的效率。我被要求研究这个问题的唯一原因是负责该应用程序的开发人员今天因病请假。我的问题是，这通常是用开发人员的sql知识创建的，还是用某种工具来帮助创建这种可怕的sql？

Answer 1

这看起来像是一个典型的过程。它可能是手工编码的。

该过程似乎是基于许多搜索参数进行搜索(可能来自应用程序中的搜索表单)。

这种查询通常对优化器来说很难，因为它缺少关键字搜索项(或者在不同的调用中具有不同的关键字搜索项)。

这个特定查询的最佳优化可能是全表扫描，所以请检查最近是否添加了任何索引，并尝试删除它们。请注意，添加索引可能是有原因的，因此删除索引可能会在应用程序的其他部分引入性能问题。

如果失败，您应该检查查询计划。

Answer 2

实际上没有，这里有严重的问题。

首先，请提供查询计划。

但需要第二个-WITH RECOMPILE提示。这个SP根据参数有很多不同的查询路径，但没有表示出来——第一个调用将确定查询路径，无论多么合理。解决这一问题的标准(基本知识)方法是向查询添加要优化的内容的提示，或者告诉编译器不要重用计划，这就是WITH RECOMPILE所做的。

http://technet.microsoft.com/en-us/library/ms181714.aspx

这不是老派——对不起。这种SP在15年前就已经腐朽了。这是任何更高级别的查询框架都会大放异彩的地方(EF/LINQ就是其中之一，但嘿，我在1990年左右使用了类似的东西，这不是开玩笑的，所以不要对比你学习编程时间长的东西说"现代")。SQL(尤其是SP中的SQL)很难处理可变查询需求。