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

Откуда:
Сообщений: 115
Приветствую!
После перехода с MS SQL Server 2000 на 2008 один из запросов стал сильно тормозить, но только с одним из пяти возможных параметров. Разница примерно 2сек и 8 мин. До этого с любым параметром срабатывало за 2сек. Причем для долгого и быстрого запроса строятся разные планы. Может кто-то посоветует, что можно предпринять для того, чтоб и для "медленного" запроса строился "быстрый" план. (Соотношение данных примерно одинаковое, ну уж точно не отличается в разы)


выложу для затравки планы
медленный:
|--Sort(ORDER BY:([st2].[Title] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([st2].[ID]))
|--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[Stratum].[PK_Stratum] AS [st2]))
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([w].[ID], [Expr1013]) WITH UNORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([w].[ID], [Expr1012]) WITH UNORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([cl].[WELLID], [Expr1011]) WITH UNORDERED PREFETCH)
| | |--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[GTM_LIST].[PK_GTM_LIST] AS [cl]), WHERE:([ShineDB].[dbo].[GTM_LIST].[GTMID] as [cl].[GTMID]<>(11) AND [ShineDB].[dbo].[GTM_LIST].[GTMID] as [cl].[GTMID]<>(35) AND datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[GTM_LIST].[DATE_FK] as [cl].[DATE_FK],0))>=(1995)))
| | |--Index Seek(OBJECT:([ShineDB].[dbo].[Well].[IX_Well_Id_VentureID] AS [w]), SEEK:([w].[ID]=[ShineDB].[dbo].[GTM_LIST].[WELLID] as [cl].[WELLID] AND [w].[VentureID]=(35)) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([ShineDB].[dbo].[Well].[PK_Well] AS [w]), SEEK:([w].[ID]=[ShineDB].[dbo].[Well].[ID] as [w].[ID]), WHERE:([ShineDB].[dbo].[Well].[ReportVentureID] as [w].[ReportVentureID]=(35)) LOOKUP ORDERED FORWARD)
|--Index Seek(OBJECT:([ShineDB].[dbo].[WellProduction].[IX_WellProduction_3] AS [wp]), SEEK:([wp].[WellID]=[ShineDB].[dbo].[Well].[ID] as [w].[ID] AND [wp].[StratumID]=[ShineDB].[dbo].[Stratum].[ID] as [st2].[ID] AND [wp].[VersionID]=NULL), WHERE:([ShineDB].[dbo].[WellProduction].[WorkDirection] as [wp].[WorkDirection]=(1) AND [ShineDB].[dbo].[WellProduction].[StratumID] as [wp].[StratumID]<>(273) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))>=(24118) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))<=(24118)) ORDERED FORWARD)

быстрый:
|--Sort(ORDER BY:([st2].[Title] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([st2].[ID]))
|--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[Stratum].[PK_Stratum] AS [st2]))
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([w].[ID], [Expr1008]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[Well].[PK_Well] AS [w]), WHERE:([ShineDB].[dbo].[Well].[ReportVentureID] as [w].[ReportVentureID]=(35) AND [ShineDB].[dbo].[Well].[VentureID] as [w].[VentureID]=(35)))
|--Index Seek(OBJECT:([ShineDB].[dbo].[WellProduction].[IX_WellProduction_3] AS [wp]), SEEK:([wp].[WellID]=[ShineDB].[dbo].[Well].[ID] as [w].[ID] AND [wp].[StratumID]=[ShineDB].[dbo].[Stratum].[ID] as [st2].[ID] AND [wp].[VersionID]=NULL), WHERE:([ShineDB].[dbo].[WellProduction].[WorkDirection] as [wp].[WorkDirection]=(1) AND [ShineDB].[dbo].[WellProduction].[StratumID] as [wp].[StratumID]<>(273) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))>=(24118) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))<=(24118)) ORDERED FORWARD)
14 дек 09, 20:16    [8065851]     Ответить | Цитировать Сообщить модератору
 Re: Два одинаковых запроса с разным параметром  [new]
TMN
Member

Откуда:
Сообщений: 115
точнее так:

медленный
  |--Sort(ORDER BY:([st2].[Title] ASC))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([st2].[ID]))
            |--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[Stratum].[PK_Stratum] AS [st2]))
            |--Top(TOP EXPRESSION:((1)))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([w].[ID], [Expr1013]) WITH UNORDERED PREFETCH)
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([w].[ID], [Expr1012]) WITH UNORDERED PREFETCH)
                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([cl].[WELLID], [Expr1011]) WITH UNORDERED PREFETCH)
                      |    |    |--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[GTM_LIST].[PK_GTM_LIST] AS [cl]), WHERE:([ShineDB].[dbo].[GTM_LIST].[GTMID] as [cl].[GTMID]<>(11) AND [ShineDB].[dbo].[GTM_LIST].[GTMID] as [cl].[GTMID]<>(35) AND datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[GTM_LIST].[DATE_FK] as [cl].[DATE_FK],0))>=(1995)))
                      |    |    |--Index Seek(OBJECT:([ShineDB].[dbo].[Well].[IX_Well_Id_VentureID] AS [w]), SEEK:([w].[ID]=[ShineDB].[dbo].[GTM_LIST].[WELLID] as [cl].[WELLID] AND [w].[VentureID]=(35)) ORDERED FORWARD)
                      |    |--Clustered Index Seek(OBJECT:([ShineDB].[dbo].[Well].[PK_Well] AS [w]), SEEK:([w].[ID]=[ShineDB].[dbo].[Well].[ID] as [w].[ID]),  WHERE:([ShineDB].[dbo].[Well].[ReportVentureID] as [w].[ReportVentureID]=(35)) LOOKUP ORDERED FORWARD)
                      |--Index Seek(OBJECT:([ShineDB].[dbo].[WellProduction].[IX_WellProduction_3] AS [wp]), SEEK:([wp].[WellID]=[ShineDB].[dbo].[Well].[ID] as [w].[ID] AND [wp].[StratumID]=[ShineDB].[dbo].[Stratum].[ID] as [st2].[ID] AND [wp].[VersionID]=NULL),  WHERE:([ShineDB].[dbo].[WellProduction].[WorkDirection] as [wp].[WorkDirection]=(1) AND [ShineDB].[dbo].[WellProduction].[StratumID] as [wp].[StratumID]<>(273) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))>=(24118) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))<=(24118)) ORDERED FORWARD)

быстрый:

  |--Sort(ORDER BY:([st2].[Title] ASC))
       |--Merge Join(Inner Join, MERGE:([st2].[ID])=([wp].[StratumID]), RESIDUAL:([ShineDB].[dbo].[Stratum].[ID] as [st2].[ID]=[ShineDB].[dbo].[WellProduction].[StratumID] as [wp].[StratumID]))
            |--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[Stratum].[PK_Stratum] AS [st2]), ORDERED FORWARD)
            |--Sort(ORDER BY:([wp].[StratumID] ASC))
                 |--Hash Match(Aggregate, HASH:([wp].[StratumID]))
                      |--Hash Match(Inner Join, HASH:([wp].[WellID])=([cl].[WELLID]), RESIDUAL:([ShineDB].[dbo].[GTM_LIST].[WELLID] as [cl].[WELLID]=[ShineDB].[dbo].[WellProduction].[WellID] as [wp].[WellID]))
                           |--Stream Aggregate(GROUP BY:([wp].[WellID], [wp].[StratumID]))
                           |    |--Nested Loops(Inner Join, OUTER REFERENCES:([w].[ID], [Expr1011]) WITH ORDERED PREFETCH)
                           |         |--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[Well].[PK_Well] AS [w]),  WHERE:([ShineDB].[dbo].[Well].[ReportVentureID] as [w].[ReportVentureID]=(33) AND [ShineDB].[dbo].[Well].[VentureID] as [w].[VentureID]=(33)) ORDERED FORWARD)
                           |         |--Index Seek(OBJECT:([ShineDB].[dbo].[WellProduction].[IX_WellProduction_6] AS [wp]), SEEK:([wp].[VersionID]=NULL AND [wp].[WellID]=[ShineDB].[dbo].[Well].[ID] as [w].[ID]),  WHERE:([ShineDB].[dbo].[WellProduction].[WorkDirection] as [wp].[WorkDirection]=(1) AND [ShineDB].[dbo].[WellProduction].[StratumID] as [wp].[StratumID]<>(273) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))>=(24118) AND (((12)*datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0))+datepart(month,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[WellProduction].[Date] as [wp].[Date],0)))-(1))<=(24118)) ORDERED FORWARD)
                           |--Clustered Index Scan(OBJECT:([ShineDB].[dbo].[GTM_LIST].[PK_GTM_LIST] AS [cl]), WHERE:([ShineDB].[dbo].[GTM_LIST].[GTMID] as [cl].[GTMID]<>(11) AND [ShineDB].[dbo].[GTM_LIST].[GTMID] as [cl].[GTMID]<>(35) AND datepart(year,CONVERT_IMPLICIT(datetime,[ShineDB].[dbo].[GTM_LIST].[DATE_FK] as [cl].[DATE_FK],0))>=(1995)))
14 дек 09, 20:38    [8065904]     Ответить | Цитировать Сообщить модератору
 Re: Два одинаковых запроса с разным параметром  [new]
TMN
Member

Откуда:
Сообщений: 115
Путем добавления

option (force order)

все запросы стали просто летать, но интересно было бы узнать как решить проблему менее кардинальным способом. UPDATE STATISTICS делал. Что еще?
14 дек 09, 21:12    [8065990]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить