ПЛАН 61 1 SELECT Жур.IDDoc as [Док $Документ], Жур.IDDocDef as Док_вид, COALESCE(ДокР.sp6722, ДокП.sp15755) as [Маршрут $Справочник.Маршруты], ДокР.sp2959 as [Клиент $Справочник.Клиенты], ДокП.sp3242 as [Субконто2 $Неопределенный] FROM _1SJourn as Ж 2 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 6.9807231E-2 NULL NULL SELECT 0 NULL 61 1 |--Compute Scalar(DEFINE:([Expr1007]=If ([ДокР].[SP6722]<>NULL) then [ДокР].[SP6722] else [ДокП].[SP15755])) 2 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1007]=If ([ДокР].[SP6722]<>NULL) then [ДокР].[SP6722] else [ДокП].[SP15755]) [Expr1007]=If ([ДокР].[SP6722]<>NULL) then [ДокР].[SP6722] else [ДокП].[SP15755] 1.0 0.0 0.0000001 61 6.9807231E-2 [Жур].[IDDOC], [Жур].[IDDOCDEF], [ДокР].[SP2959], [ДокП].[SP3242], [Expr1007] NULL PLAN_ROW 0 1.0 61 1 |--Filter(WHERE:([ДокР].[SP9794]=' 1E ' OR [ДокП].[SP9794]=' 1E ')) 2 3 2 Filter Filter WHERE:([ДокР].[SP9794]=' 1E ' OR [ДокП].[SP9794]=' 1E ') NULL 1.0 0.0 8.8000002E-7 319 6.9807135E-2 [Жур].[IDDOC], [Жур].[IDDOCDEF], [ДокР].[SP6722], [ДокР].[SP2959], [ДокП].[SP15755], [ДокП].[SP3242] NULL PLAN_ROW 0 1.0 185 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Жур].[IDDOC])) 2 4 3 Nested Loops Left Outer Join OUTER REFERENCES:([Жур].[IDDOC]) NULL 1.0 0.0 0.00001254 319 6.9806255E-2 [Жур].[IDDOC], [Жур].[IDDOCDEF], [ДокР].[SP9794], [ДокР].[SP6722], [ДокР].[SP2959], [ДокП].[SP9794], [ДокП].[SP15755], [ДокП].[SP3242] NULL PLAN_ROW 0 1.0 185 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Жур].[IDDOC])) 2 5 4 Nested Loops Left Outer Join OUTER REFERENCES:([Жур].[IDDOC]) NULL 1.0 0.0 0.00001254 218 5.6897905E-2 [Жур].[IDDOC], [Жур].[IDDOCDEF], [ДокР].[SP9794], [ДокР].[SP6722], [ДокР].[SP2959] NULL PLAN_ROW 0 1.0 185 1 | |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Жур].[IDDOC])) 2 6 5 Nested Loops Left Anti Semi Join OUTER REFERENCES:([Жур].[IDDOC]) NULL 1.0 0.0 0.00001254 104 4.3988116E-2 [Жур].[IDDOC], [Жур].[IDDOCDEF] NULL PLAN_ROW 0 1.0 588 1 | | |--Filter(WHERE:((([Жур].[IDDOCDEF]=2956 AND (Convert([Жур].[CLOSED])&1)=1) OR ([Жур].[IDDOCDEF]=3231 AND (Convert([Жур].[CLOSED])&1)=0)) AND Convert([Жур].[ISMARK])=0)) 2 7 6 Filter Filter WHERE:((([Жур].[IDDOCDEF]=2956 AND (Convert([Жур].[CLOSED])&1)=1) OR ([Жур].[IDDOCDEF]=3231 AND (Convert([Жур].[CLOSED])&1)=0)) AND Convert([Жур].[ISMARK])=0) NULL 1.0 0.0 2.7799999E-6 104 1.2661987E-2 [Жур].[IDDOC], [Жур].[IDDOCDEF] NULL PLAN_ROW 0 1.0 1925 1 | | | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([BackupPharm05].[dbo].[_1SJOURN] AS [Жур])) 2 8 7 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1000]), OBJECT:([BackupPharm05].[dbo].[_1SJOURN] AS [Жур]) [Жур].[ISMARK], [Жур].[CLOSED], [Жур].[IDDOC], [Жур].[IDDOCDEF] 1.0 6.2500001E-3 0.0000011 104 1.2659207E-2 [Жур].[ISMARK], [Жур].[CLOSED], [Жур].[IDDOC], [Жур].[IDDOCDEF] NULL PLAN_ROW 0 1.0 1925 1 | | | |--Index Seek(OBJECT:([BackupPharm05].[dbo].[_1SJOURN].[ACDATETIME] AS [Жур]), SEEK:([Жур].[DATE_TIME_IDDOC] >= '20080818' AND [Жур].[DATE_TIME_IDDOC] <= '20080821Z') ORDERED FORWARD) 2 9 8 Index Seek Index Seek OBJECT:([BackupPharm05].[dbo].[_1SJOURN].[ACDATETIME] AS [Жур]), SEEK:([Жур].[DATE_TIME_IDDOC] >= '20080818' AND [Жур].[DATE_TIME_IDDOC] <= '20080821Z') ORDERED FORWARD [Bmk1000] 1.0 6.3284999E-3 7.9607002E-5 52 6.408107E-3 [Bmk1000] NULL PLAN_ROW 0 1.0 403 588 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ЖурДоп].[IDDOC])) 2 36 6 Nested Loops Inner Join OUTER REFERENCES:([ЖурДоп].[IDDOC]) NULL 1.0 0.0 1.221979E-4 202 3.1313587E-2 NULL NULL PLAN_ROW 0 3.0 42709 588 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ЖурДоп].[IDDOC])) 2 37 36 Nested Loops Inner Join OUTER REFERENCES:([ЖурДоп].[IDDOC]) NULL 1.0 0.0 0.00001254 150 1.8325411E-2 [ЖурДоп].[IDDOC] NULL PLAN_ROW 0 3.0 795090 588 | | | |--Filter(WHERE:(Convert([ЖурДоп].[ISMARK])=0)) 2 38 37 Filter Filter WHERE:(Convert([ЖурДоп].[ISMARK])=0) NULL 1.0 0.0 5.8000001E-7 104 6.8270173E-3 [ЖурДоп].[IDDOC] NULL PLAN_ROW 0 3.0 834446 588 | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1005]), OBJECT:([BackupPharm05].[dbo].[_1SJOURN] AS [ЖурДоп])) 2 39 38 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1005]), OBJECT:([BackupPharm05].[dbo].[_1SJOURN] AS [ЖурДоп]) [ЖурДоп].[ISMARK], [ЖурДоп].[IDDOC] 1.0 9.7656251E-5 0.0000011 104 6.8252771E-3 [ЖурДоп].[ISMARK], [ЖурДоп].[IDDOC] NULL PLAN_ROW 0 3.0 834446 588 | | | | |--Index Seek(OBJECT:([BackupPharm05].[dbo].[_1SJOURN].[ACDATETIME] AS [ЖурДоп]), SEEK:([ЖурДоп].[DATE_TIME_IDDOC] >= '20080818' AND [ЖурДоп].[DATE_TIME_IDDOC] <= '20080821Z') ORDERED FORWARD) 2 40 39 Index Seek Index Seek OBJECT:([BackupPharm05].[dbo].[_1SJOURN].[ACDATETIME] AS [ЖурДоп]), SEEK:([ЖурДоп].[DATE_TIME_IDDOC] >= '20080818' AND [ЖурДоп].[DATE_TIME_IDDOC] <= '20080821Z') ORDERED FORWARD [Bmk1005] 1.0 6.3284999E-3 7.9607002E-5 52 6.5673208E-3 [Bmk1005] NULL PLAN_ROW 0 3.0 42709 795090 | | | |--Clustered Index Seek(OBJECT:([BackupPharm05].[dbo].[DH4918].[PK_DH4918] AS [ДокМЛ]), SEEK:([ДокМЛ].[IDDOC]=[ЖурДоп].[IDDOC]), WHERE:([ДокМЛ].[SP9794]=' 1E ') ORDERED FORWARD) 2 45 37 Clustered Index Seek Clustered Index Seek OBJECT:([BackupPharm05].[dbo].[DH4918].[PK_DH4918] AS [ДокМЛ]), SEEK:([ДокМЛ].[IDDOC]=[ЖурДоп].[IDDOC]), WHERE:([ДокМЛ].[SP9794]=' 1E ') ORDERED FORWARD [ДокМЛ].[SP9794] 1.0 6.3284999E-3 7.9603E-5 54 1.1456453E-2 [ДокМЛ].[SP9794] NULL PLAN_ROW 0 9.0 403 42709 | | |--Clustered Index Seek(OBJECT:([BackupPharm05].[dbo].[DT4918].[PK_DT4918] AS [ДокМЛСтроки]), SEEK:([ДокМЛСтроки].[IDDOC]=[ЖурДоп].[IDDOC]), WHERE:(right(Convert([ДокМЛСтроки].[SP4923]), 9)=NULL OR [Жур].[IDDOC]=right(Conve 2 46 36 Clustered Index Seek Clustered Index Seek OBJECT:([BackupPharm05].[dbo].[DT4918].[PK_DT4918] AS [ДокМЛСтроки]), SEEK:([ДокМЛСтроки].[IDDOC]=[ЖурДоп].[IDDOC]), WHERE:(right(Convert([ДокМЛСтроки].[SP4923]), 9)=NULL OR [Жур].[IDDOC]=right(Convert([ДокМЛСтроки].[SP4923]), 9)) ORDERED FORWARD [ДокМЛСтроки].[SP4923] 3.0 6.3284999E-3 9.2261362E-5 61 0.01295465 [ДокМЛСтроки].[SP4923] NULL PLAN_ROW 0 3.0 148 185 | |--Clustered Index Seek(OBJECT:([BackupPharm05].[dbo].[DH2956].[PK_DH2956] AS [ДокР]), SEEK:([ДокР].[IDDOC]=[Жур].[IDDOC]), WHERE:([ДокР].[SP2977]<>' 2ER ') ORDERED FORWARD) 2 47 5 Clustered Index Seek Clustered Index Seek OBJECT:([BackupPharm05].[dbo].[DH2956].[PK_DH2956] AS [ДокР]), SEEK:([ДокР].[IDDOC]=[Жур].[IDDOC]), WHERE:([ДокР].[SP2977]<>' 2ER ') ORDERED FORWARD [ДокР].[SP2977], [ДокР].[SP9794], [ДокР].[SP6722], [ДокР].[SP2959] 1.0 6.3284999E-3 7.9603E-5 122 1.2895809E-2 [ДокР].[SP2977], [ДокР].[SP9794], [ДокР].[SP6722], [ДокР].[SP2959] NULL PLAN_ROW 0 3.0 19 185 |--Clustered Index Seek(OBJECT:([BackupPharm05].[dbo].[DH3231].[PK_DH3231] AS [ДокП]), SEEK:([ДокП].[IDDOC]=[Жур].[IDDOC]) ORDERED FORWARD) 2 48 4 Clustered Index Seek Clustered Index Seek OBJECT:([BackupPharm05].[dbo].[DH3231].[PK_DH3231] AS [ДокП]), SEEK:([ДокП].[IDDOC]=[Жур].[IDDOC]) ORDERED FORWARD [ДокП].[SP9794], [ДокП].[SP15755], [ДокП].[SP3242] 1.0 6.3284999E-3 7.9603E-5 110 1.2895809E-2 [ДокП].[SP9794], [ДокП].[SP15755], [ДокП].[SP3242] NULL PLAN_ROW 0 3.0 САМ ЗАПРОС SELECT Жур.IDDoc as [Док $Документ], Жур.IDDocDef as Док_вид, COALESCE(ДокР.sp6722, ДокП.sp15755) as [Маршрут $Справочник.Маршруты], ДокР.sp2959 as [Клиент $Справочник.Клиенты], ДокП.sp3242 as [Субконто2 $Неопределенный] FROM _1SJourn as Жур (NOLOCK) LEFT JOIN dh2956 as ДокР (NOLOCK) ON ДокР.IDDoc = Жур.IDDoc AND ДокР.sp2977 <> ' 2ER ' LEFT JOIN dh3231 as ДокП (NOLOCK) ON ДокП.IDDoc = Жур.IDDoc WHERE Жур.Date_Time_IDDoc BETWEEN '20080818' AND '20080821Z' AND (ДокР.sp9794 = ' 1E ' OR ДокП.sp9794 = ' 1E ') AND ((Жур.IDDocDef = 2956 AND Жур.Closed & 1 = 1) OR (Жур.IDDocDef = 3231 AND Жур.Closed & 1 = 0)) AND Жур.IsMark = 0 AND Жур.IDDoc NOT IN ( SELECT RIGHT(ДокМЛСтроки.sp4923,9) FROM dt4918 as ДокМЛСтроки (NOLOCK) INNER JOIN dh4918 as ДокМЛ (NOLOCK) ON ДокМЛ.IDDoc = ДокМЛСтроки.IDDoc AND ДокМЛ.sp9794 = ' 1E ' INNER JOIN _1SJourn as ЖурДоп (NOLOCK) ON ЖурДоп.IDDoc = ДокМЛСтроки.IDDoc AND ЖурДоп.Date_Time_IDDoc BETWEEN '20080818' AND '20080821Z' AND ЖурДоп.IsMark = 0 )