代码之家 › 专栏 › 技术社区 › Dennis

“优化使用不存在且不存在”WHERE子句中有许多列的SQL查询”

optimization sql-server-2005 sql-server sql

Dennis · 技术社区 · 15 年前

编辑:使用SQL Server 2005。

我有一个查询,它必须检查旧数据库中的行是否已经导入到新数据库中,如果它们还没有导入,则导入它们。由于遗留数据库的设计不好,所以遗留表中的行没有唯一的ID,所以我必须使用试探法来决定是否导入了该行。(我无法控制旧数据库。)新数据库的结构稍有不同,我必须检查几个值,例如创建日期是否匹配、组号是否匹配等,以试探性地确定新数据库中是否存在行。不是很漂亮,但是它必须与之接口的遗留系统的糟糕设计让我别无选择。

不管怎样,系统的用户开始向系统抛出比我设计的多10到100倍的数据,现在查询运行得太慢了。你能建议一种使它更快的方法吗?下面是代码,为了隐私或简化而修订了一些代码,但我认为我留下了重要的部分:

INSERT INTO [...NewDatabase...]
SELECT [...Bunch of columns...]
  FROM  [...OldDatabase...] AS t1
 WHERE t1.Printed = 0
  AND NOT EXISTS(SELECT *
                   FROM [...New Database...] AS s3
                  WHERE year(s3.dtDatePrinted) = 1850  --This allows for re-importing rows marked for reprint
                    AND CAST(t1.[Group] AS int) = CAST(s3.vcGroupNum AS int)
                    AND RTRIM(t1.Subgroup) = s3.vcSubGroupNum
                    AND RTRIM(t1.SSN) = s3.vcPrimarySSN
                    AND RTRIM(t1.FirstName) = s3.vcFirstName
                    AND RTRIM(t1.LastName) = s3.vcLastName
                    AND t1.CaptureDate = s3.dtDateCreated)

2 回复 | 直到 15 年前

Schwern 15 年前

不知道模式是什么样子的,第一步是 EXPLAIN 这些子查询。这将显示数据库占用的时间。如果没有索引,它可能会进行多个完整表扫描。如果我不得不猜的话,我会说 t1.printed 和 s3.dtDatePrinted 这两个是获得索引最重要的,因为它们将清除已经转换的内容。

另外,任何需要计算的内容都可能导致数据库不使用索引。例如,调用 RTRIM 和 CAST . 这意味着新数据库中有脏数据。将其永久修剪掉,并参见关于将T1.Group更改为正确的类型。

year(s3.dtDatePrinted) = 1850 可能会欺骗优化器,使其不使用 S3.D打印日期 (解释应该让你知道)。这似乎只是您设置的一个标志,用于检查行是否已被转换,因此请将其设置为特定日期(即1850-01-01 00:00:00),并执行特定匹配(即。 s3.dtDatePrinted = "1850-01-01 00:00:00" )现在这是一个简单的索引查找。

让你的比较更简单也会有帮助。本质上,这里的T1和S3之间是1对1的关系(如果T1是新表的实名,请考虑更具描述性的内容)。所以,不要将S3的每个单独的位与T1匹配,只要给T1一个列来引用它相应S3行的主键。那你只有一件事要检查。如果不能更改T1,那么可以使用第三个表跟踪T1到S3的映射。

一旦你有了它,你所要做的就是一个连接来查找S3中不在T1中的行。

SELECT s3.*
FROM s3
LEFT JOIN t1 ON t1.s3 = s3.id   -- or whatever s3's primary key is
WHERE t1.s3 IS NULL

Lluis Martinez 15 年前

尝试替换此:

年份(s3.dtdateprinted)=1850

用这个:

s3.dtdateprinted>='1850-01-01'和s3.dtdateprinted<'1851-01-01'

在这种情况下,如果dtdateprinted上有一个索引,优化器可能会使用范围索引扫描。

但我同意以前的海报,你应该避免轮胎。一个想法是在S3中保留未经修剪(原始)的值,或者创建一个中间表,用修剪(新)的值映射未修剪的值。甚至创造物化的观点。但是,没有适当的索引,所有这些工作都是无用的。