独特的顶级唱片有着巨大的性能问题

Question

我有两个具有巨大数据集的表&我试图根据可选的搜索标准过滤数据。

Table 1 : Item
Table 2 : ScanCode

Item可以有0或n个ScanCodes，所以它们之间的关系是1到0或多个关系，这就是为什么我在ScanCode表上保留了join，我试图通过对ScanCode的可选搜索获得与ScanCode连接的项目数据。

Declare @p_scanCode BIGINT = NULL, @p_limit INT = 500

SELECT TOP(@p_limit) i.ItemCode, i.StandardDescription,i.ItemType 
FROM Item i
LEFT OUTER JOIN ScanCode sc
ON sc.FK_ItemCode = i.ItemCode
WHERE ((@p_scanCode IS NULL) OR (@p_scanCode IS NOT NULL AND sc.ScanCode = @p_scanCode))
ORDER BY ItemCode

上面的查询工作得很好，但是由于我们对一个项有多个ScanCodes，所以结果集中会有重复的查询，所以我将查询更改为包含distinct。

Declare @p_scanCode BIGINT = NULL, @p_limit INT = 500

SELECT DISTINCT TOP(@p_limit) i.ItemCode, i.StandardDescription,i.ItemType 
FROM Item i
LEFT OUTER JOIN ScanCode sc
ON sc.FK_ItemCode = i.ItemCode
WHERE ((@p_scanCode IS NULL) OR (@p_scanCode IS NOT NULL AND sc.ScanCode = @p_scanCode))
ORDER BY ItemCode

在对查询添加了distinct之后，现在需要超过30秒才能获得结果，以前它所用的时间还不到1秒。ScanCode表很大，它有1200万个数据。

如何获得不同的最高记录，根据提供的限制，没有任何绩效问题。

请建议一下。

Answer 1

使用条件联接&动态查询将解决这个问题，因为与ScanCode表连接时存在重复项问题，如果没有扫描代码，则可以进行条件连接，前提是我们不加入，这样就不会得到重复项。如果提供了ScanCode，那么每个扫描代码将只有一个项&在这种情况下，我们不会得到重复的项。

此外，如果我们使用的是带有top(@p_limit)记录的动态限制，则需要在查询结束时使用选项(重新编译)，这样它就不会使用带有以前限制的缓存执行计划，性能就会下降。

    Declare @p_scanCode BIGINT = '12345', @p_limit INT = 500
    
    DECLARE @sql nvarchar(max) = N'
    SELECT TOP(@p_limit) i.ItemCode, i.StandardDescription 
    FROM Item i '
            
    + 
    CASE WHEN @p_scanCode IS NOT NULL THEN
    N' LEFT OUTER JOIN          SCANCODE AS SC
    ON  I.ItemCode = SC.FK_RetailItemCode
    AND GETDATE() BETWEEN SC.EffectiveDate AND SC.TerminationDate ' ELSE N'' END
    +

   'WHERE  1 = 1'
    
    + CASE WHEN @p_scanCode IS NOT NULL THEN
            N' AND sc.ScanCode = @p_scanCode' ELSE N'' END
    +

    ' ORDER BY  I.ItemCode asc OPTION (RECOMPILE)'
    
    print @sql

Answer 2

您只需要从Item输出项吗？ScanCode只是作为过滤器使用吗？看来你需要exists。如果关系是1:多，那么你就不需要担心distinct了。

declare 
    @p_scanCode bigint = null, 
    @p_limit int = 500

select      
top (@p_limit)  i.ItemCode, i.StandardDescription,i.ItemType 
from            item i
where           @p_scanCode is null
or              exists (
                    select    0
                    from      scanCode sc
                    where     sc.FK_ItemCode = i.ItemCode
                    and       scanCode = @p_scanCode
                )