Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MSSQL 2000. Большое elapsed time.  [new]
denakol
Member

Откуда:
Сообщений: 1
Используется база данных Mssql 2000.
Запросы представляют собой чтение данных.
Первый запрос это написанный на linq второй.
Первый запрос сгенерированный Entity Framework

Статистика
Table 'OrdersKeysTable'. Scan count 17296, logical reads 34667, physical reads 0, read-ahead reads 0.
Table 'OrdersSystemTable'. Scan count 35396, logical reads 106358, physical reads 182, read-ahead reads 0.
Table 'OrdersBaseTable'. Scan count 16596, logical reads 80580, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 8859 ms,  elapsed time = 168351 ms.

План показывает стоимость выполнения корня
TotalSubFreeCost
43,33583
SELECT 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            [Join4].[TransID1] AS [TransID]
            FROM    [dbo].[OrdersBaseTable] AS [Extent1]
            INNER JOIN [dbo].[OrdersSystemTable] AS [Extent2] ON [Extent1].[TransID] = [Extent2].[TransID]
            INNER JOIN [dbo].[OrdersKeysTable] AS [Extent3] ON [Extent1].[TransID] = [Extent3].[ChildTransID]
            INNER JOIN  (SELECT [Extent4].[TransID] AS [TransID1], [Extent4].[FirstName] AS [FirstName], [Extent4].[LastName] AS [LastName], [Extent4].[Address1] AS [Address1], [Extent4].[Address2] AS [Address2], [Extent4].[City] AS [City], [Extent4].[State] AS [State], [Extent5].[SystemID] AS [SystemID], [Extent6].[ChildTransID] AS [ChildTransID]
                FROM   [dbo].[OrdersBaseTable] AS [Extent4]
                INNER JOIN [dbo].[OrdersSystemTable] AS [Extent5] ON [Extent4].[TransID] = [Extent5].[TransID]
                LEFT OUTER JOIN [dbo].[OrdersKeysTable] AS [Extent6] ON [Extent4].[TransID] = [Extent6].[ChildTransID] ) AS [Join4] ON (([Extent2].[SystemID] = [Join4].[SystemID]) OR (([Extent2].[SystemID] IS NULL) AND ([Join4].[SystemID] IS NULL))) AND (((CASE WHEN ([Extent1].[FirstName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[FirstName]))) ELSE N'' END) = (CASE WHEN ([Join4].[FirstName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[FirstName]))) ELSE N'' END)) OR ((CASE WHEN ([Extent1].[FirstName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[FirstName]))) ELSE N'' END IS NULL) AND (CASE WHEN ([Join4].[FirstName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[FirstName]))) ELSE N'' END IS NULL))) AND (((CASE WHEN ([Extent1].[LastName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[LastName]))) ELSE N'' END) = (CASE WHEN ([Join4].[LastName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[LastName]))) ELSE N'' END)) OR ((CASE WHEN ([Extent1].[LastName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[LastName]))) ELSE N'' END IS NULL) AND (CASE WHEN ([Join4].[LastName] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[LastName]))) ELSE N'' END IS NULL))) AND (((CASE WHEN ([Extent1].[Address1] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[Address1]))) ELSE N'' END) = (CASE WHEN ([Join4].[Address1] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[Address1]))) ELSE N'' END)) OR ((CASE WHEN ([Extent1].[Address1] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[Address1]))) ELSE N'' END IS NULL) AND (CASE WHEN ([Join4].[Address1] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[Address1]))) ELSE N'' END IS NULL))) AND (((CASE WHEN ([Extent1].[Address2] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[Address2]))) ELSE N'' END) = (CASE WHEN ([Join4].[Address2] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[Address2]))) ELSE N'' END)) OR ((CASE WHEN ([Extent1].[Address2] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[Address2]))) ELSE N'' END IS NULL) AND (CASE WHEN ([Join4].[Address2] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[Address2]))) ELSE N'' END IS NULL))) AND (((CASE WHEN ([Extent1].[City] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[City]))) ELSE N'' END) = (CASE WHEN ([Join4].[City] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[City]))) ELSE N'' 
END)) OR ((CASE WHEN ([Extent1].[City] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[City]))) ELSE N'' END IS NULL) AND (CASE WHEN ([Join4].[City] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[City]))) ELSE N'' END IS NULL))) AND (((CASE WHEN ([Extent1].[State] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[State]))) ELSE N'' END) = (CASE WHEN ([Join4].[State] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[State]))) ELSE N'' END)) OR ((CASE WHEN ([Extent1].[State] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Extent1].[State]))) ELSE N'' END IS NULL) AND (CASE WHEN ([Join4].[State] IS NOT NULL) THEN LOWER(LTRIM(RTRIM([Join4].[State]))) ELSE N'' END IS NULL)))
            WHERE ([Extent3].[ChildTransID] = [Extent3].[MasterTransID]) AND ([Extent3].[MasterTransID] IS NOT NULL) AND ([Join4].[ChildTransID] IS NULL)
        )  AS [Distinct1]

Второй запрос. на чистом sql
Статистика:
Table 'OrdersBaseTable'. Scan count 16596, logical reads 97673, physical reads 3, read-ahead reads 4008.
Table 'OrdersSystemTable'. Scan count 16596, logical reads 58880, physical reads 0, read-ahead reads 6848.
Table 'OrdersKeysTable'. Scan count 2, logical reads 52, physical reads 0, read-ahead reads 14.

SQL Server Execution Times:
   CPU time = 6766 ms,  elapsed time = 7648 ms.


SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            [Extent1].[TransID] AS [TransID]
            FROM  (SELECT        b.SystemID, LOWER(LTRIM(RTRIM(ISNULL(a.FirstName, '')))) AS FirstName, LOWER(LTRIM(RTRIM(ISNULL(a.LastName, '')))) AS LastName, LOWER(LTRIM(RTRIM(ISNULL(a.Address1, '')))) AS Address1, 
                         LOWER(LTRIM(RTRIM(ISNULL(a.Address2, '')))) AS Address2, LOWER(LTRIM(RTRIM(ISNULL(a.City, '')))) AS City, LOWER(LTRIM(RTRIM(ISNULL(a.State, '')))) AS State, a.TransID, a.TransDate
FROM            dbo.OrdersBaseTable AS a INNER JOIN
                         dbo.OrdersSystemTable AS b ON a.TransID = b.TransID LEFT OUTER JOIN
                         dbo.OrdersKeysTable AS c ON c.ChildTransID = a.TransID
WHERE        (c.ChildTransID IS NULL)) AS [Extent1]
            INNER JOIN (SELECT        b.SystemID, LOWER(LTRIM(RTRIM(ISNULL(a.FirstName, '')))) AS FirstName, LOWER(LTRIM(RTRIM(ISNULL(a.LastName, '')))) AS LastName, LOWER(LTRIM(RTRIM(ISNULL(a.Address1, '')))) AS Address1, 
                         LOWER(LTRIM(RTRIM(ISNULL(a.Address2, '')))) AS Address2, LOWER(LTRIM(RTRIM(ISNULL(a.City, '')))) AS City, LOWER(LTRIM(RTRIM(ISNULL(a.State, '')))) AS State, LOWER(LTRIM(RTRIM(ISNULL(a.Postal, '')))) 
                         AS Postal, LOWER(LTRIM(RTRIM(ISNULL(b.Plus4, '')))) AS Plus4, dbo.OrdersKeysTable.MasterTransID
FROM            dbo.OrdersBaseTable AS a INNER JOIN
                         dbo.OrdersSystemTable AS b ON a.TransID = b.TransID INNER JOIN
                         dbo.OrdersKeysTable ON a.TransID = dbo.OrdersKeysTable.ChildTransID
WHERE        (dbo.OrdersKeysTable.MasterTransID = dbo.OrdersKeysTable.ChildTransID)) AS [Extent2] ON (([Extent1].[SystemID] = [Extent2].[SystemID]) OR (([Extent1].[SystemID] IS NULL) AND ([Extent2].[SystemID] IS NULL))) AND (([Extent1].[FirstName] = [Extent2].[FirstName]) OR (([Extent1].[FirstName] IS NULL) AND ([Extent2].[FirstName] IS NULL))) AND (([Extent1].[LastName] = [Extent2].[LastName]) OR (([Extent1].[LastName] IS NULL) AND ([Extent2].[LastName] IS NULL))) AND (([Extent1].[Address1] = [Extent2].[Address1]) OR (([Extent1].[Address1] IS NULL) AND ([Extent2].[Address1] IS NULL))) AND (([Extent1].[Address2] = [Extent2].[Address2]) OR (([Extent1].[Address2] IS NULL) AND ([Extent2].[Address2] IS NULL))) AND (([Extent1].[City] = [Extent2].[City]) OR (([Extent1].[City] IS NULL) AND ([Extent2].[City] IS NULL))) AND (([Extent1].[State] = [Extent2].[State]) OR (([Extent1].[State] IS NULL) AND ([Extent2].[State] IS NULL)))
        )  AS [Distinct1]
    )  AS [GroupBy1]

План показывает стоимость выполнения корня
TotalSubFreeCost
87,40255

Из-за чего может быть такое большое Elapsed Time? Такое долгое чтение?
Через tuning dvisor прогнал базу, создал индексы дополнительные, но у первого уменьшается CPU time, но elapsed time такое же огромное. Графический план показывает по 0% работу с индексами таблицы OrdersKeysTable, но в статистике там дофига чтений.

Если я использую MSSQL2014 с полностью идентичными индексами запросы второй запрос работает лишь на секунду больше.(6-7 сек) В чем может быть причина большого Elapsed Time на MSSQL 2000?
2 июл 14, 02:04    [16246487]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2000. Большое elapsed time.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Оба запроса - адовый ад.
Перепишите их по-человечески и не мучайтесь с этими говногенераторами.

И да. Хотите понять, почему -- смотрите планы.

Сообщение было отредактировано: 2 июл 14, 02:57
2 июл 14, 02:44    [16246523]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2000. Большое elapsed time.  [new]
Glory
Member

Откуда:
Сообщений: 104751
denakol
Из-за чего может быть такое большое Elapsed Time? Такое долгое чтение?


Ничего, что запросы разные ? Что там куски кода переставлены местами ?
2 июл 14, 10:55    [16247532]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить