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

Откуда:
Сообщений: 82
Пример запроса
SELECT * FROM 
(SELECT DISTINCT m.MeetingId,m.[Name],m.Type,m.TimeZone,m.Status,dbo.MeetingPins(m.MeetingId,null) Pins,
(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) FirstDate,
(SELECT MAX(EndTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) LastDate,
CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeStart,
CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MAX(EndTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeEnd,
c.Channel  
FROM Meetings m INNER JOIN MeetingParticipants mp ON m.MeetingID=mp.MeetingID  
LEFT JOIN Channels c ON c.ChannelId=m.ChannelId  
WHERE m.RecurringMeetings>1  AND UPPER(m.Status)<>'CANCELLED'  AND mp.MemberID='a7e57a9c-5f7b-4def-8215-39a8742058ad')a 
WHERE DATEDIFF(Minute, GETDATE(), dbo.ToServerTimeZone(a.LastDate, a.TimeZone)) > -240

Думаю, что больше всего тормозят вложенные запросы на получение FirstDate и LastDate
Можно их как-то по-другому извлекать?
13 авг 09, 23:15    [7536357]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
thorn
Member

Откуда: Москва
Сообщений: 115
varlo,

план запроса покажи чтобы не гадать
13 авг 09, 23:21    [7536376]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
varlo
Member

Откуда:
Сообщений: 82
Только как картинку получилось его получить. В текст можно как-то сбросить это?

К сообщению приложен файл. Размер - 0Kb
13 авг 09, 23:27    [7536399]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
varlo
Member

Откуда:
Сообщений: 82
Вот Server Trace

SELECT @Res=    CASE      WHEN UPPER(@TimeZone)='PST' THEN @DateTime     WHEN UPPER(@TimeZone)='MST' THEN DATEADD(Hour, 1, @DateTime)     WHEN UPPER(@TimeZone)='CST' THEN DATEADD(Hour, 2, @DateTime)     WHEN UPPER(@TimeZone)='EST' THEN DATEADD(Hour,	SQL:StmtCompleted	0	0	0	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res= dbo.ToPST(@DateTime, @TimeZone)   /*If server is at the previous zone zone another 1 should be substracted*/  	SQL:StmtCompleted	0	0	6	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res=    CASE      WHEN UPPER(@TimeZone)='PST' THEN @DateTime     WHEN UPPER(@TimeZone)='MST' THEN DATEADD(Hour, 1, @DateTime)     WHEN UPPER(@TimeZone)='CST' THEN DATEADD(Hour, 2, @DateTime)     WHEN UPPER(@TimeZone)='EST' THEN DATEADD(Hour,	SQL:StmtCompleted	0	0	0	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res= dbo.ToPST(@DateTime, @TimeZone)   /*If server is at the previous zone zone another 1 should be substracted*/  	SQL:StmtCompleted	0	0	6	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res=    CASE      WHEN UPPER(@TimeZone)='PST' THEN @DateTime     WHEN UPPER(@TimeZone)='MST' THEN DATEADD(Hour, 1, @DateTime)     WHEN UPPER(@TimeZone)='CST' THEN DATEADD(Hour, 2, @DateTime)     WHEN UPPER(@TimeZone)='EST' THEN DATEADD(Hour,	SQL:StmtCompleted	0	0	0	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res= dbo.ToPST(@DateTime, @TimeZone)   /*If server is at the previous zone zone another 1 should be substracted*/  	SQL:StmtCompleted	0	0	6	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res=    CASE      WHEN UPPER(@TimeZone)='PST' THEN @DateTime     WHEN UPPER(@TimeZone)='MST' THEN DATEADD(Hour, 1, @DateTime)     WHEN UPPER(@TimeZone)='CST' THEN DATEADD(Hour, 2, @DateTime)     WHEN UPPER(@TimeZone)='EST' THEN DATEADD(Hour,	SQL:StmtCompleted	0	0	0	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res= dbo.ToPST(@DateTime, @TimeZone)   /*If server is at the previous zone zone another 1 should be substracted*/  	SQL:StmtCompleted	0	0	6	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res=    CASE      WHEN UPPER(@TimeZone)='PST' THEN @DateTime     WHEN UPPER(@TimeZone)='MST' THEN DATEADD(Hour, 1, @DateTime)     WHEN UPPER(@TimeZone)='CST' THEN DATEADD(Hour, 2, @DateTime)     WHEN UPPER(@TimeZone)='EST' THEN DATEADD(Hour,	SQL:StmtCompleted	0	0	0	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res= dbo.ToPST(@DateTime, @TimeZone)   /*If server is at the previous zone zone another 1 should be substracted*/  	SQL:StmtCompleted	0	0	6	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
@PIN=''     	SQL:StmtCompleted	0	0	0	0
SELECT @PIN=@PIN+PINVALUE+',' FROM Pins WHERE MeetingID = @MeetingId AND     ((@ParticipationRoleId IS NULL) OR (ParticipationRoleId=@ParticipationRoleId))     	SQL:StmtCompleted	0	0	166	0
IF LEN(@PIN)>0          SET	SQL:StmtCompleted	0	0	0	0
@PIN=SUBSTRING(@PIN,1,LEN(@PIN)-1)     	SQL:StmtCompleted	0	0	0	0
RETURN @PIN 	SQL:StmtCompleted	0	0	0	0
SELECT @Res=    CASE      WHEN UPPER(@TimeZone)='PST' THEN @DateTime     WHEN UPPER(@TimeZone)='MST' THEN DATEADD(Hour, 1, @DateTime)     WHEN UPPER(@TimeZone)='CST' THEN DATEADD(Hour, 2, @DateTime)     WHEN UPPER(@TimeZone)='EST' THEN DATEADD(Hour,	SQL:StmtCompleted	0	0	0	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT @Res= dbo.ToPST(@DateTime, @TimeZone)   /*If server is at the previous zone zone another 1 should be substracted*/  	SQL:StmtCompleted	0	0	6	0
RETURN @Res   /* value */  	SQL:StmtCompleted	0	0	0	0
SELECT * FROM   (SELECT DISTINCT m.MeetingId,m.[Name],m.Type,m.TimeZone,m.Status,dbo.MeetingPins(m.MeetingId,null) Pins,  (SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) FirstDate,  (SELECT MAX(EndTime) FROM MeetingSeries ms WHERE	SQL:StmtCompleted	63	63	216	0
13 авг 09, 23:29    [7536408]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
thorn
Member

Откуда: Москва
Сообщений: 115
varlo,
план запроса в текстовом виде
13 авг 09, 23:34    [7536425]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
varlo
Member

Откуда:
Сообщений: 82
SET SHOWPLAN_TEXT ON
go

SELECT * FROM 
(SELECT DISTINCT m.MeetingId,m.[Name],m.Type,m.TimeZone,m.Status,dbo.MeetingPins(m.MeetingId,null) Pins,
(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) FirstDate,
(SELECT MAX(EndTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) LastDate,
CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeStart,
CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MAX(EndTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeEnd,
c.Channel  
FROM Meetings m INNER JOIN MeetingParticipants mp ON m.MeetingID=mp.MeetingID  
LEFT JOIN Channels c ON c.ChannelId=m.ChannelId  
WHERE m.RecurringMeetings>1  AND UPPER(m.Status)<>'CANCELLED' AND mp.MemberID='a7e57a9c-5f7b-4def-8215-39a8742058ad'
)a 
WHERE DATEDIFF(Minute, GETDATE(), dbo.ToServerTimeZone(a.LastDate, a.TimeZone)) > -240
go

SET SHOWPLAN_TEXT OFF
go

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

SELECT * FROM 
(SELECT DISTINCT m.MeetingId,m.[Name],m.Type,m.TimeZone,m.Status,dbo.MeetingPins(m.MeetingId,null) Pins,
(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) FirstDate,
(SELECT MAX(EndTime) FROM MeetingSeries ms W

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Sort(DISTINCT ORDER BY:([m].[MeetingID] ASC, [Expr1003] ASC, [Expr1005] ASC, [Expr1009] ASC, [Expr1015] ASC, [Expr1019] ASC, [c].[Channel] ASC))
       |--Filter(WHERE:(datediff(minute, getdate(), [dbo].[ToServerTimeZone]([Expr1009], [m].[TimeZone]))>-240))
            |--Compute Scalar(DEFINE:([Expr1003]=[dbo].[MeetingPins]([m].[MeetingID], NULL), [Expr1005]=[Expr1005], [Expr1009]=[Expr1009], [Expr1015]=Convert(Convert([Expr1013])), [Expr1019]=Convert(Convert([Expr1017]))))
                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))
                      |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))
                      |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))
                      |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))
                      |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[ChannelId]))
                      |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mp].[MeetingId]))
                      |    |    |    |    |    |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingParticipants].[PK_MeetingParticipants] AS [mp]), WHERE:([mp].[MemberId]=A7E57A9C-5F7B-4DEF-8215-39A8742058AD))
                      |    |    |    |    |    |--Clustered Index Seek(OBJECT:([ccccdev].[dbo].[Meetings].[PK_Meetings] AS [m]), SEEK:([m].[MeetingID]=[mp].[MeetingId]),  WHERE:([m].[RecurringMeetings]>1 AND upper([m].[Status])<>'CANCELLED') ORDERED FORWAR
                      |    |    |    |    |--Clustered Index Seek(OBJECT:([ccccdev].[dbo].[Channels].[PK_Channels] AS [c]), SEEK:([c].[ChannelId]=[m].[ChannelId]) ORDERED FORWARD)
                      |    |    |    |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))
                      |    |    |         |--Stream Aggregate(DEFINE:([Expr1005]=MIN([ms].[StartTime])))
                      |    |    |              |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))
                      |    |    |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))
                      |    |         |--Stream Aggregate(DEFINE:([Expr1009]=MAX([ms].[EndTime])))
                      |    |              |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))
                      |    |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))
                      |         |--Stream Aggregate(DEFINE:([Expr1013]=MIN([ms].[StartTime])))
                      |              |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))
                      |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))
                           |--Stream Aggregate(DEFINE:([Expr1017]=MAX([ms].[EndTime])))
                                |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))

(24 row(s) affected)

SET STATISTICS PROFILE ON 
GO

SELECT * FROM 
(SELECT DISTINCT m.MeetingId,m.[Name],m.Type,m.TimeZone,m.Status,dbo.MeetingPins(m.MeetingId,null) Pins,
(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) FirstDate,
(SELECT MAX(EndTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) LastDate,
CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeStart,
CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MAX(EndTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeEnd,
c.Channel  
FROM Meetings m INNER JOIN MeetingParticipants mp ON m.MeetingID=mp.MeetingID  
LEFT JOIN Channels c ON c.ChannelId=m.ChannelId  
WHERE m.RecurringMeetings>1  AND UPPER(m.Status)<>'CANCELLED' AND mp.MemberID='a7e57a9c-5f7b-4def-8215-39a8742058ad'
)a 
WHERE DATEDIFF(Minute, GETDATE(), dbo.ToServerTimeZone(a.LastDate, a.TimeZone)) > -240
go

SET STATISTICS PROFILE OFF
GO

MeetingId                            Name                                                                                                 Type            TimeZone Status                         Pins                                                                                                                                                                                                                                                            FirstDate                                              LastDate                                               TimeStart                                              TimeEnd                                                Channel              
------------------------------------ ---------------------------------------------------------------------------------------------------- --------------- -------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ -------------------- 
7D7AF977-BEDC-478A-A246-5E7CA1DEA582 Test2204R                                                                                            Meet & Confer   PST      Active                         6729876,448503                                                                                                                                                                                                                                                  2009-08-14 09:00:00.000                                2009-08-19 09:30:00.000                                1900-01-01 09:00:00.000                                1900-01-01 09:30:00.000                                27348

(1 row(s) affected)

Rows        Executes    StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                        DefinedValues                                                                                                                                                                          EstimateRows             EstimateIO               EstimateCPU              AvgRowSize  TotalSubtreeCost         OutputList                                                                                                                                        Warnings Type                           Parallel EstimateExecutions       
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------ 
1           1           SELECT * FROM 
(SELECT DISTINCT m.MeetingId,m.[Name],m.Type,m.TimeZone,m.Status,dbo.MeetingPins(m.MeetingId,null) Pins,
(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId) FirstDate,
(SELECT MAX(EndTime) FROM MeetingSeries ms WH 13          1           0           NULL                           NULL                           NULL                                                                                                                                                                                            NULL                                                                                                                                                                                   2.2547708                NULL                     NULL                     NULL        0.52102774               NULL                                                                                                                                              NULL     SELECT                         0        NULL
1           1             |--Sort(DISTINCT ORDER BY:([m].[MeetingID] ASC, [Expr1003] ASC, [Expr1005] ASC, [Expr1009] ASC, [Expr1015] ASC, [Expr1019] ASC, [c].[Channel] ASC))                                                                                                            13          2           1           Sort                           Distinct Sort                  DISTINCT ORDER BY:([m].[MeetingID] ASC, [Expr1003] ASC, [Expr1005] ASC, [Expr1009] ASC, [Expr1015] ASC, [Expr1019] ASC, [c].[Channel] ASC)                                                      NULL                                                                                                                                                                                   2.2547708                1.1261261E-2             1.0412589E-4             241         0.52102774               [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel], [Expr1003], [Expr1005], [Expr1009], [Expr1015], [Expr1019]  NULL     PLAN_ROW                       0        1.0
1           1                  |--Filter(WHERE:(datediff(minute, getdate(), [dbo].[ToServerTimeZone]([Expr1009], [m].[TimeZone]))>-240))                                                                                                                                                 13          3           2           Filter                         Filter                         WHERE:(datediff(minute, getdate(), [dbo].[ToServerTimeZone]([Expr1009], [m].[TimeZone]))>-240)                                                                                                  NULL                                                                                                                                                                                   2.2547708                0.0                      7.3655838E-6             241         0.50966233               [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel], [Expr1003], [Expr1005], [Expr1009], [Expr1015], [Expr1019]  NULL     PLAN_ROW                       0        1.0
6           1                       |--Compute Scalar(DEFINE:([Expr1003]=[dbo].[MeetingPins]([m].[MeetingID], NULL), [Expr1005]=[Expr1005], [Expr1009]=[Expr1009], [Expr1015]=Convert(Convert([Expr1013])), [Expr1019]=Convert(Convert([Expr1017]))))                                    13          4           3           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1003]=[dbo].[MeetingPins]([m].[MeetingID], NULL), [Expr1005]=[Expr1005], [Expr1009]=[Expr1009], [Expr1015]=Convert(Convert([Expr1013])), [Expr1019]=Convert(Convert([Expr1017])))  [Expr1003]=[dbo].[MeetingPins]([m].[MeetingID], NULL), [Expr1005]=[Expr1005], [Expr1009]=[Expr1009], [Expr1015]=Convert(Convert([Expr1013])), [Expr1019]=Convert(Convert([Expr1017]))  7.515902                 0.0                      7.5159022E-7             241         0.509655                 [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel], [Expr1003], [Expr1005], [Expr1009], [Expr1015], [Expr1019]  NULL     PLAN_ROW                       0        1.0
6           1                            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))                                                                                                                                                                            13          5           4           Nested Loops                   Left Outer Join                OUTER REFERENCES:([m].[MeetingID])                                                                                                                                                              NULL                                                                                                                                                                                   7.515902                 0.0                      3.1416472E-5             548         0.50965422               [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel], [Expr1005], [Expr1009], [Expr1013], [Expr1017]              NULL     PLAN_ROW                       0        1.0
6           1                                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))                                                                                                                                                                       13          6           5           Nested Loops                   Left Outer Join                OUTER REFERENCES:([m].[MeetingID])                                                                                                                                                              NULL                                                                                                                                                                                   7.515902                 0.0                      3.1416472E-5             540         0.39830077               [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel], [Expr1005], [Expr1009], [Expr1013]                          NULL     PLAN_ROW                       0        1.0
6           1                                 |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))                                                                                                                                                                  13          7           6           Nested Loops                   Left Outer Join                OUTER REFERENCES:([m].[MeetingID])                                                                                                                                                              NULL                                                                                                                                                                                   7.515902                 0.0                      3.1416472E-5             532         0.28694728               [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel], [Expr1005], [Expr1009]                                      NULL     PLAN_ROW                       0        1.0
6           1                                 |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[MeetingID]))                                                                                                                                                             13          8           7           Nested Loops                   Left Outer Join                OUTER REFERENCES:([m].[MeetingID])                                                                                                                                                              NULL                                                                                                                                                                                   7.515902                 0.0                      3.1416472E-5             524         0.17559379               [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel], [Expr1005]                                                  NULL     PLAN_ROW                       0        1.0
6           1                                 |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[ChannelId]))                                                                                                                                                        13          9           8           Nested Loops                   Left Outer Join                OUTER REFERENCES:([m].[ChannelId])                                                                                                                                                              NULL                                                                                                                                                                                   7.515902                 0.0                      3.1416472E-5             516         6.4240314E-2             [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID], [c].[Channel]                                                              NULL     PLAN_ROW                       0        1.0
6           1                                 |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mp].[MeetingId]))                                                                                                                                                       13          10          9           Nested Loops                   Inner Join                     OUTER REFERENCES:([mp].[MeetingId])                                                                                                                                                             NULL                                                                                                                                                                                   7.515902                 0.0                      3.1416472E-5             424         5.6979656E-2             [m].[ChannelId], [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID]                                                            NULL     PLAN_ROW                       0        1.0
9           1                                 |    |    |    |    |    |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingParticipants].[PK_MeetingParticipants] AS [mp]), WHERE:([mp].[MemberId]=A7E57A9C-5F7B-4DEF-8215-39A8742058AD))                                            13          11          10          Clustered Index Scan           Clustered Index Scan           OBJECT:([ccccdev].[dbo].[MeetingParticipants].[PK_MeetingParticipants] AS [mp]), WHERE:([mp].[MemberId]=A7E57A9C-5F7B-4DEF-8215-39A8742058AD)                                                   [mp].[MeetingId], [mp].[MemberId]                                                                                                                                                      7.515902                 4.8689611E-2             6.8900001E-4             96          4.9378611E-2             [mp].[MeetingId], [mp].[MemberId]                                                                                                                 NULL     PLAN_ROW                       0        1.0
6           9                                 |    |    |    |    |    |--Clustered Index Seek(OBJECT:([ccccdev].[dbo].[Meetings].[PK_Meetings] AS [m]), SEEK:([m].[MeetingID]=[mp].[MeetingId]),  WHERE:([m].[RecurringMeetings]>1 AND upper([m].[Status])<>'CANCELLED') ORDERED FORWAR 13          12          10          Clustered Index Seek           Clustered Index Seek           OBJECT:([ccccdev].[dbo].[Meetings].[PK_Meetings] AS [m]), SEEK:([m].[MeetingID]=[mp].[MeetingId]),  WHERE:([m].[RecurringMeetings]>1 AND upper([m].[Status])<>'CANCELLED') ORDERED FORWARD      [m].[ChannelId], [m].[RecurringMeetings], [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID]                                                                        1.0                      6.3284999E-3             7.9603E-5                336         7.2958614E-3             [m].[ChannelId], [m].[RecurringMeetings], [m].[Status], [m].[TimeZone], [m].[Type], [m].[Name], [m].[MeetingID]                                   NULL     PLAN_ROW                       0        7.515902
3           6                                 |    |    |    |    |--Clustered Index Seek(OBJECT:([ccccdev].[dbo].[Channels].[PK_Channels] AS [c]), SEEK:([c].[ChannelId]=[m].[ChannelId]) ORDERED FORWARD)                                                                              13          13          9           Clustered Index Seek           Clustered Index Seek           OBJECT:([ccccdev].[dbo].[Channels].[PK_Channels] AS [c]), SEEK:([c].[ChannelId]=[m].[ChannelId]) ORDERED FORWARD                                                                                [c].[Channel]                                                                                                                                                                          1.0                      6.3284999E-3             7.9603E-5                100         7.2292406E-3             [c].[Channel]                                                                                                                                     NULL     PLAN_ROW                       0        7.515902
6           6                                 |    |    |    |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))                                                                                                                                    13          14          8           Hash Match                     Cache                          HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID])                                                                                                                              NULL                                                                                                                                                                                   1.0                      0.0                      3.4553913E-4             15          0.11132207               [Expr1005]                                                                                                                                        NULL     PLAN_ROW                       0        7.515902
6           6                                 |    |    |         |--Stream Aggregate(DEFINE:([Expr1005]=MIN([ms].[StartTime])))                                                                                                                                                         13          15          14          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                            [Expr1005]=MIN([ms].[StartTime])                                                                                                                                                       1.0                      0.0                      1.1487414E-7             31          0.11094671               [Expr1005]                                                                                                                                        NULL     PLAN_ROW                       0        5.904891
28          6                                 |    |    |              |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))                                                                            13          16          15          Clustered Index Scan           Clustered Index Scan           OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID])                                                                                   [ms].[MeetingID], [ms].[StartTime]                                                                                                                                                     1.1487414                7.9059981E-2             3.7833001E-3             331         0.10139996               [ms].[MeetingID], [ms].[StartTime]                                                                                                                NULL     PLAN_ROW                       0        5.904891
6           6                                 |    |    |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))                                                                                                                                         13          22          7           Hash Match                     Cache                          HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID])                                                                                                                              NULL                                                                                                                                                                                   1.0                      0.0                      3.4553913E-4             15          0.11132207               [Expr1009]                                                                                                                                        NULL     PLAN_ROW                       0        7.515902
6           6                                 |    |         |--Stream Aggregate(DEFINE:([Expr1009]=MAX([ms].[EndTime])))                                                                                                                                                                13          23          22          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                            [Expr1009]=MAX([ms].[EndTime])                                                                                                                                                         1.0                      0.0                      1.1487414E-7             31          0.11094671               [Expr1009]                                                                                                                                        NULL     PLAN_ROW                       0        5.904891
28          6                                 |    |              |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))                                                                                 13          24          23          Clustered Index Scan           Clustered Index Scan           OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID])                                                                                   [ms].[MeetingID], [ms].[EndTime]                                                                                                                                                       1.1487414                7.9059981E-2             3.7833001E-3             331         0.10139996               [ms].[MeetingID], [ms].[EndTime]                                                                                                                  NULL     PLAN_ROW                       0        5.904891
6           6                                 |    |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))                                                                                                                                              13          30          6           Hash Match                     Cache                          HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID])                                                                                                                              NULL                                                                                                                                                                                   1.0                      0.0                      3.4553913E-4             15          0.11132207               [Expr1013]                                                                                                                                        NULL     PLAN_ROW                       0        7.515902
6           6                                 |         |--Stream Aggregate(DEFINE:([Expr1013]=MIN([ms].[StartTime])))                                                                                                                                                                   13          31          30          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                            [Expr1013]=MIN([ms].[StartTime])                                                                                                                                                       1.0                      0.0                      1.1487414E-7             31          0.11094671               [Expr1013]                                                                                                                                        NULL     PLAN_ROW                       0        5.904891
28          6                                 |              |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))                                                                                      13          32          31          Clustered Index Scan           Clustered Index Scan           OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID])                                                                                   [ms].[MeetingID], [ms].[StartTime]                                                                                                                                                     1.1487414                7.9059981E-2             3.7833001E-3             331         0.10139996               [ms].[MeetingID], [ms].[StartTime]                                                                                                                NULL     PLAN_ROW                       0        5.904891
6           6                                 |--Hash Match(Cache, HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID]))                                                                                                                                                   13          38          5           Hash Match                     Cache                          HASH:([m].[MeetingID]), RESIDUAL:([m].[MeetingID]=[m].[MeetingID])                                                                                                                              NULL                                                                                                                                                                                   1.0                      0.0                      3.4553913E-4             15          0.11132207               [Expr1017]                                                                                                                                        NULL     PLAN_ROW                       0        7.515902
6           6                                      |--Stream Aggregate(DEFINE:([Expr1017]=MAX([ms].[EndTime])))                                                                                                                                                                          13          39          38          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                            [Expr1017]=MAX([ms].[EndTime])                                                                                                                                                         1.0                      0.0                      1.1487414E-7             31          0.11094671               [Expr1017]                                                                                                                                        NULL     PLAN_ROW                       0        5.904891
28          6                                           |--Clustered Index Scan(OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID]))                                                                                           13          40          39          Clustered Index Scan           Clustered Index Scan           OBJECT:([ccccdev].[dbo].[MeetingSeries].[PK_MeetingSeries] AS [ms]), WHERE:([ms].[MeetingID]=[m].[MeetingID])                                                                                   [ms].[MeetingID], [ms].[EndTime]                                                                                                                                                       1.1487414                7.9059981E-2             3.7833001E-3             331         0.10139996               [ms].[MeetingID], [ms].[EndTime]                                                                                                                  NULL     PLAN_ROW                       0        5.904891

(25 row(s) affected)

13 авг 09, 23:37    [7536444]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
iljy
Member

Откуда:
Сообщений: 8711
varlo,

думаю есть смысл переделать ваши подзапросы в такой join:

....
FROM Meetings m INNER JOIN MeetingParticipants mp ON m.MeetingID=mp.MeetingID  
LEFT JOIN Channels c ON c.ChannelId=m.ChannelId  
LEFT JOIN
(
  SELECT MeetingId, MIN(StartTime) FirstDate, MAX(EndTime) LastDate
  FROM MeetingSeries
  GROUP BY MeetingId
) ms
   on ms.MeetingId=m.MeetingId
....
13 авг 09, 23:49    [7536486]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
thorn
Member

Откуда: Москва
Сообщений: 115
iljy,

CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MIN(StartTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeStart,
CONVERT(DATETIME,CONVERT(VARCHAR,(SELECT MAX(EndTime) FROM MeetingSeries ms WHERE ms.MeetingId=m.MeetingId),114),101) TimeEnd,

эти два лишних подзапроса убери. Лучше запросом "выше" CONVERT по расчитанным значениям FirstDate, LastDate
13 авг 09, 23:55    [7536499]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
varlo
Member

Откуда:
Сообщений: 82
Ок, спасибо

Такой запрос получается теперь

SELECT * FROM 
(SELECT DISTINCT m.MeetingId,m.[Name],m.Type,m.TimeZone,m.Status,dbo.MeetingPins(m.MeetingId,null) Pins, FirstDate, LastDate,
CONVERT(DATETIME,CONVERT(VARCHAR,FirstDate,114),101) TimeStart,
CONVERT(DATETIME,CONVERT(VARCHAR,LastDate,114),101) TimeEnd,
c.Channel  
FROM Meetings m INNER JOIN MeetingParticipants mp ON m.MeetingID=mp.MeetingID  
LEFT JOIN Channels c ON c.ChannelId=m.ChannelId  
LEFT JOIN
(
  SELECT MeetingId, MIN(StartTime) FirstDate, MAX(EndTime) LastDate
  FROM MeetingSeries ms 
  GROUP BY MeetingId
) ms ON ms.MeetingId=m.MeetingId
WHERE m.RecurringMeetings>1  AND UPPER(m.Status)<>'CANCELLED' AND mp.MemberID='a7e57a9c-5f7b-4def-8215-39a8742058ad'
)a 
WHERE DATEDIFF(Minute, GETDATE(), dbo.ToServerTimeZone(a.LastDate, a.TimeZone)) > -240
14 авг 09, 00:01    [7536527]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36814
UPPER(m.Status)<>'CANCELLED' - upper убрать
DATEDIFF(Minute, GETDATE(), dbo.ToServerTimeZone(a.LastDate, a.TimeZone)) > -240 - тут без вычисляемого поля строить условия хреново. Будете скан всегда иметь.
14 авг 09, 00:53    [7536607]     Ответить | Цитировать Сообщить модератору
 Re: Помогите упростить запрос - избавиться от вложенных запросов  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
А вы проверили, это эквивалентный запрос? DISTINCT, кстати, из-за того, что со схемой что-то не так или с данными не все хорошо?

В любом случае,


1. Для оригинального запроса вас спасут следующие индексы:
MeetingSeries:
MeetingId, StartTime
MeetingId, EndTime

MeetingParticipants:
MemberId, MeetingID

Meetings:
либо:
RecurringMeetings, Status, MeetingId, ChannelId

либо, если Status <> 'cancelled' отфильтрует больше записей, чем RecurringMeetings > 1, то
Status, RecurringMeetings, MeetingId, ChannelId

либо, если и то, и то условие малоселективны, то оставить кластерный PK по MeetingId, хоть Merge Join возможно будет


2. Для нового запроса:

Индексы:

MeetingParticipants:
MemberId, MeetingID

MeetingSeries:
MeetingId, StartTime, EndTime

Meetings:
либо:
RecurringMeetings, Status, MeetingId, ChannelId

либо, если Status <> 'cancelled' отфильтрует больше записей, чем RecurringMeetings > 1, то
Status, RecurringMeetings, MeetingId, ChannelId

либо, если и то, и то условие малоселективны, то оставить кластерный PK по MeetingId, хоть Merge Join возможно будет


Если это возможно, индексы делайте уникальными (MemberId, MeetingId -- уникальная комбинация?)


Функцию dbo.MeetingPins постарайтесь сделать deterministic (как -- почитай в BOL).

Если collation у вас case-insensitive, то, как сказал Сергей, уберите UPPER.

Сделайте эксперимент и покажите планы (которые от statistics profile).
14 авг 09, 02:04    [7536710]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить