Блог

У меня есть медленный запрос к нашей базе данных Microsoft sql. И если я удаляю его часть, это ускоряется, но я не понимаю почему, и планировщик запросов мне не помогает (я чего-то не понимаю).

Запрос, который выполняется медленно > 30 секунд (queryplan):

 SELECT COUNT(*)
FROM [dbo].[CarImage] AS [t0]
     LEFT OUTER JOIN [dbo].[Dismantled] AS [t1] ON [t1].[Id] = [t0].[DismantledId]
WHERE([t1].[FinishedReason] <> 0)
     AND ([t1].[FinishedDate] < GETDATE() - 365)
     AND (NOT(EXISTS
(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Part] AS [t2]
    WHERE([t2].[Status] <> 4)
         AND ([t2].[Status] <> 3)
         AND ([t2].[DismantledId] = [t1].[Id])
)))
     AND (NOT(EXISTS
(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Part] AS [t3]
    WHERE([t3].[Status] = 3)
         AND ([t3].[SoldDate] > GETDATE() - 365)
         AND ([t3].[DismantledId] = [t1].[Id])
)))
     AND ((NOT(EXISTS
(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Partner] AS [t4], 
         [dbo].[SelfPickSite] AS [t5]
    WHERE([t4].[Id] = [t1].[PartnerId])
         AND ([t5].[PartnerId] = [t4].[Id])
)))
          OR (EXISTS
(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[SelfPickCar] AS [t6]
    WHERE [t6].[DismantledId] = [t1].[Id]
)));
 

Если я удалю последнюю часть, это <1 сек (queryplan):

 SELECT COUNT(*)
FROM [dbo].[CarImage] AS [t0]
     LEFT OUTER JOIN [dbo].[Dismantled] AS [t1] ON [t1].[Id] = [t0].[DismantledId]
WHERE([t1].[FinishedReason] <> 0)
     AND ([t1].[FinishedDate] < GETDATE() - 365)
     AND (NOT(EXISTS
(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Part] AS [t2]
    WHERE([t2].[Status] <> 4)
         AND ([t2].[Status] <> 3)
         AND ([t2].[DismantledId] = [t1].[Id])
)))
     AND (NOT(EXISTS
(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Part] AS [t3]
    WHERE([t3].[Status] = 3)
         AND ([t3].[SoldDate] > GETDATE() - 365)
         AND ([t3].[DismantledId] = [t1].[Id])
)))
     AND ((NOT(EXISTS
(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Partner] AS [t4], 
         [dbo].[SelfPickSite] AS [t5]
    WHERE([t4].[Id] = [t1].[PartnerId])
         AND ([t5].[PartnerId] = [t4].[Id])
))));
 

ИЛИ убивает производительность; пожалуйста, попробуйте этот запрос и дайте мне результат

   SELECT COUNT(*)
    FROM (
        SELECT 1
        FROM [dbo].[CarImage] AS [t0]
        LEFT JOIN [dbo].[Dismantled] AS [t1] ON [t1].[Id] = [t0].[DismantledId]
        WHERE ([t1].[FinishedReason] <> 0)
            AND ([t1].[FinishedDate] < GETDATE() - 365)
            AND (
                NOT (
                    EXISTS (
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[Part] AS [t2]
                        WHERE ([t2].[Status] <> 4)
                            AND ([t2].[Status] <> 3)
                            AND ([t2].[DismantledId] = [t1].[Id])
                        )
                    )
                )
            AND (
                NOT (
                    EXISTS (
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[Part] AS [t3]
                        WHERE ([t3].[Status] = 3)
                            AND ([t3].[SoldDate] > GETDATE() - 365)
                            AND ([t3].[DismantledId] = [t1].[Id])
                        )
                    )
                )
            AND (
                (
                    NOT (
                        EXISTS (
                            SELECT NULL AS [EMPTY]
                            FROM [dbo].[Partner] AS [t4]
                                ,[dbo].[SelfPickSite] AS [t5]
                            WHERE ([t4].[Id] = [t1].[PartnerId])
                                AND ([t5].[PartnerId] = [t4].[Id])
                            )
                        )
                    )

                UNION ALL

                SELECT 1
                FROM [dbo].[CarImage] AS [t0]
                LEFT JOIN [dbo].[Dismantled] AS [t1] ON [t1].[Id] = [t0].[DismantledId]
                WHERE (
                        EXISTS (
                            SELECT NULL AS [EMPTY]
                            FROM [dbo].[SelfPickCar] AS [t6]
                            WHERE [t6].[DismantledId] = [t1].[Id]
                            )
                        )
                )
        ) X
 

ps: если у вас проблемы с производительностью, запустите этот код SET STATISTICS IO ON , а затем запустите запрос и опубликуйте результат