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

Откуда:
Сообщений: 15
Всем добрый день,
столкнулся с непонятной ситуацией.
Есть несложный запрос, использующий наряду с локальными таблицами несколько таблиц из linked сервера. Время выполнения порядка 4-5 секунд, возвращает порядка 500 записей.
Проблемы начинаются при попытке сохранить возвращаемые данные во временную таблицу, т.е. при добавлении в запросе кляузы INTO #t время выполнения увеличивается до 25 секунд. План выполнения у нового запроса по форме аналогичен плану с простым селектом (имеется ввиду порядок операторов, стратегии соединения таблиц). Но есть непонятное различие - при доступе к 2 удаленным таблицам в операторе remote query возвращается разное actual number of rows, в случае с select into оно в ~ 3 раза больше, соответственно при этом для 2-х таблиц идет table spool, в простом селекте данные не спулятся.
Сорри, планы выполнения приводить здесь не имею права.
Может кто сталкивался с подобным поведением сервера? До этого был уверен, что планы у селект и селект инту по сути отличаться не должны, а здесь разница во времени выполнеия раз в 5, количество логических чтений отличается на порядки.
10 янв 12, 12:20    [11877795]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
Glory
Member

Откуда:
Сообщений: 104751
andrei_78
До этого был уверен, что планы у селект и селект инту отличаться не должны

Планы _разных_ запросов могут быть разные
Если вы нашли более оптимальный план, чем выбрал сервер, то добивайтесь фиксации плана.
10 янв 12, 12:25    [11877831]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
Glory
Member

Откуда:
Сообщений: 104751
andrei_78
Сорри, планы выполнения приводить здесь не имею права.

Вот интересно, а что за секретная информация в них ?
10 янв 12, 12:26    [11877838]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
andrei_78
Member

Откуда:
Сообщений: 15
Glory
andrei_78
До этого был уверен, что планы у селект и селект инту отличаться не должны

Планы _разных_ запросов могут быть разные
Если вы нашли более оптимальный план, чем выбрал сервер, то добивайтесь фиксации плана.

Проблему вижу прежде всего в разном количестве строк, возвращаемых из remote query, как здесь может помочь фиксация плана?
10 янв 12, 12:30    [11877876]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
Glory
Member

Откуда:
Сообщений: 104751
andrei_78
Проблему вижу прежде всего в разном количестве строк, возвращаемых из remote query, как здесь может помочь фиксация плана?

Если разное количество строк, то и план другой
Под фиксацие плана и понимается получение в плане только тех операций, которые нужны
В вашем случае - избавление от TableSpool
10 янв 12, 12:34    [11877910]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
andrei_78
Member

Откуда:
Сообщений: 15
Glory
andrei_78
Сорри, планы выполнения приводить здесь не имею права.

Вот интересно, а что за секретная информация в них ?

просто есть соглашение о неразглашении, думаю сюда подпадают и куски кода с частю структуры бд, которые можно увидеть в плане.
10 янв 12, 12:34    [11877914]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
Glory
Member

Откуда:
Сообщений: 104751
andrei_78
просто есть соглашение о неразглашении, думаю сюда подпадают и куски кода с частю структуры бд, которые можно увидеть в плане.

Ну так замените их на произвольные.
10 янв 12, 12:37    [11877937]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
andrei_78
Member

Откуда:
Сообщений: 15
Glory
andrei_78
Проблему вижу прежде всего в разном количестве строк, возвращаемых из remote query, как здесь может помочь фиксация плана?

Если разное количество строк, то и план другой
Под фиксацие плана и понимается получение в плане только тех операций, которые нужны
В вашем случае - избавление от TableSpool


Если под фиксацией плана Вы имеете ввиду хинт USE PLAN, то не думаю, что это хороший вариант в данном случае (разница в 20 секунд не настолько критична). хотелось бы обойтись без хинтов и понять поведение сервера. remote query в обоих случаях имеют по сути одинаковые заросы, а возвращают разные данные.
10 янв 12, 12:42    [11877987]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
Glory
Member

Откуда:
Сообщений: 104751
andrei_78
Если под фиксацией плана Вы имеете ввиду хинт USE PLAN

Под фиксацией плана понимается приведение его к тому виду, который вы считаете лучшим

andrei_78
remote query в обоих случаях имеют по сути одинаковые заросы, а возвращают разные данные.

Что значит "по сути" ?
Любой другой символ в тексте запроса приводит к другому запросу

andrei_78
отелось бы обойтись без хинтов и понять поведение сервера.

Поведение простое: разные запросы - разные планы.
10 янв 12, 12:46    [11878033]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
andrei_78
Member

Откуда:
Сообщений: 15
Glory
andrei_78
просто есть соглашение о неразглашении, думаю сюда подпадают и куски кода с частю структуры бд, которые можно увидеть в плане.

Ну так замените их на произвольные.

Заменил. Планы и детали выполнения ниже:
+ SELECT

493 1 select
e.VendorID,
e.BItemID,
cil.CatalogItemName,
sga.CityName,
sga.ProvinceName,
sga.CountryCode
from DB1.dbo.Table1 e with (nolock)
left join dbo.Table2 i with (nolock)
on i.BItemID = e.BItemID
and i.BSystemID = e.BSystemID
left join Server2.DB2.dbo.Table3 sk with (nolock)
on sk.VendorID = e.VendorID
left join Server2.DB3.dbo.Table4 cil with (nolock)
on sk.GroupID = cil.CatalogItemID
and cil.LangID = 1033
left join Server2.DB3.dbo.Table5 sga with (nolock)
on sga.GroupCatalogItemID = sk.GroupID
and sga.AddressTypeID = 2
where
e.ExceptionReason = 'Invalid VendorID'
and e.CreateDate >= dateadd(day,datediff(day,0, dateadd(year,-1,getdate())),0)
and e.VendorID is not null
and i.BItemID is null 1 1 0 NULL NULL NULL NULL 9.562616E+11 NULL NULL NULL 98177.43 NULL NULL SELECT 0 NULL
493 1 |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID])=([Server2].[DB2].[dbo].[Table3].[GroupID]), RESIDUAL:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID])) 1 2 1 Merge Join Right Outer Join MANY-TO-MANY MERGE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID])=([Server2].[DB2].[dbo].[Table3].[GroupID]), RESIDUAL:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]) NULL 9.562616E+11 243.5889 97752.06 193 98177.43 [e].[BItemID], [e].[VendorID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
0 0 |--Compute Scalar(DEFINE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName]=[Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName]=[Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode]=[Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode])) 1 3 2 Compute Scalar Compute Scalar DEFINE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName]=[Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName]=[Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode]=[Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode]) [Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName]=[Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName]=[Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode]=[Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode] 36186.42 0 12.07214 60 12.07214 [Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
253467 1 | |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1009"."GroupCatalogItemID" "Col1031","Tbl1009"."CityName" "Col1034","Tbl1009"."ProvinceName" "Col1035","Tbl1009"."CountryCode" "Col1037" FROM "DB3"."dbo"."Table5" "Tbl1009" WITH (NOLOCK) WHERE "Tbl1009"."AddressTypeID"=(2) ORDER BY "Col1031" ASC)) 1 4 3 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1009"."GroupCatalogItemID" "Col1031","Tbl1009"."CityName" "Col1034","Tbl1009"."ProvinceName" "Col1035","Tbl1009"."CountryCode" "Col1037" FROM "DB3"."dbo"."Table5" "Tbl1009" WITH (NOLOCK) WHERE "Tbl1009"."AddressTypeID"=(2) ORDER BY "Col1031" ASC) NULL 36186.42 0 12.07214 60 12.07214 [Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
493 1 |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[GroupID])=([Server2].[DB3].[dbo].[Table4].[CatalogItemID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID])) 1 14 2 Merge Join Left Outer Join MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[GroupID])=([Server2].[DB3].[dbo].[Table4].[CatalogItemID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID]) NULL 2.641941E+08 0.03152704 40.98081 150 169.7055 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
493 1 |--Sort(ORDER BY:([Server2].[DB2].[dbo].[Table3].[GroupID] ASC)) 1 15 14 Sort Sort ORDER BY:([Server2].[DB2].[dbo].[Table3].[GroupID] ASC) NULL 10145.6 0.01126126 0.6198574 19 41.88528 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID] NULL PLAN_ROW 0 1
493 1 | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([e].[VendorID])=([Server2].[DB2].[dbo].[Table3].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID])) 1 16 15 Merge Join Left Outer Join MANY-TO-MANY MERGE:([e].[VendorID])=([Server2].[DB2].[dbo].[Table3].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID]) NULL 10145.6 0.000313 0.02100956 19 41.25416 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID] NULL PLAN_ROW 0 1
329 1 | |--Sort(ORDER BY:([e].[VendorID] ASC)) 1 17 16 Sort Sort ORDER BY:([e].[VendorID] ASC) NULL 1 0.01126126 0.000100015 15 7.404164 [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
329 1 | | |--Filter(WHERE:([APRecon].[dbo].[Table2].[BItemID] as [i].[BItemID] IS NULL)) 1 18 17 Filter Filter WHERE:([APRecon].[dbo].[Table2].[BItemID] as [i].[BItemID] IS NULL) NULL 1 0 0.00110561 15 7.392803 [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
329 1 | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([e].[BSystemID], [e].[BItemID])) 1 19 18 Nested Loops Left Outer Join OUTER REFERENCES:([e].[BSystemID], [e].[BItemID]) NULL 2303.354 0 0.009607235 19 7.391697 [e].[BItemID], [e].[VendorID], [i].[BItemID] NULL PLAN_ROW 0 1
329 1 | | |--Clustered Index Scan(OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID')) 1 20 19 Clustered Index Scan Clustered Index Scan OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID') [e].[BSystemID], [e].[BItemID], [e].[VendorID] 2060 0.5623842 0.0408449 38 0.6032292 [e].[BSystemID], [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
0 329 | | |--Index Seek(OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD) 1 21 19 Index Seek Index Seek OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD [i].[BItemID] 1.115719 0.003125 0.0001582273 11 6.746311 [i].[BItemID] NULL PLAN_ROW 0 2060
0 0 | |--Compute Scalar(DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID])) 1 23 16 Compute Scalar Compute Scalar DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]) [Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID] 101456 0 33.82867 15 33.82867 [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID] NULL PLAN_ROW 0 1
101444 1 | |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1005"."GroupID" "Col1044","Tbl1005"."VendorID" "Col1045" FROM "DB2"."dbo"."Table3" "Tbl1005" WITH (NOLOCK) ORDER BY "Col1045" ASC)) 1 24 23 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1005"."GroupID" "Col1044","Tbl1005"."VendorID" "Col1045" FROM "DB2"."dbo"."Table3" "Tbl1005" WITH (NOLOCK) ORDER BY "Col1045" ASC) NULL 101456 0 33.82867 15 33.82867 [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID] NULL PLAN_ROW 0 1
0 0 |--Compute Scalar(DEFINE:([Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName])) 1 31 14 Compute Scalar Compute Scalar DEFINE:([Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName]) [Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName] 260393.6 0 86.80788 142 86.80788 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
1029458 1 |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1007"."CatalogItemID" "Col1040","Tbl1007"."CatalogItemName" "Col1042" FROM "DB3"."dbo"."Table4" "Tbl1007" WITH (NOLOCK) WHERE "Tbl1007"."LangID"=(1033) ORDER BY "Col1040" ASC)) 1 32 31 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1007"."CatalogItemID" "Col1040","Tbl1007"."CatalogItemName" "Col1042" FROM "DB3"."dbo"."Table4" "Tbl1007" WITH (NOLOCK) WHERE "Tbl1007"."LangID"=(1033) ORDER BY "Col1040" ASC) NULL 260393.6 0 86.80788 142 86.80788 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1

+ SELECT INTO

493 1 select
e.VendorID,
e.BItemID,
cil.CatalogItemName,
sga.CityName,
sga.ProvinceName,
sga.CountryCode
into #t
from DB1.dbo.Table1 e with (nolock)
left join dbo.Table2 i with (nolock)
on i.BItemID = e.BItemID
and i.BSystemID = e.BSystemID
left join Server2.DB2.dbo.Table3 sk with (nolock)
on sk.VendorID = e.VendorID
left join Server2.DB3.dbo.Table4 cil with (nolock)
on sk.GroupID = cil.CatalogItemID
and cil.LangID = 1033
left join Server2.DB3.dbo.Table3Address sga with (nolock)
on sga.GroupCatalogItemID = sk.GroupID
and sga.AddressTypeID = 2
where
e.ExceptionReason = 'Invalid VendorID'
and e.CreateDate >= dateadd(day,datediff(day,0, dateadd(year,-1,getdate())),0)
and e.VendorID is not null 1 1 0 NULL NULL NULL NULL 1.229637E+13 NULL NULL NULL 1.060497E+08 NULL NULL SELECT INTO 0 NULL
493 1 |--Table Insert(OBJECT:([#t]), SET:([#t].[VendorID] = [DB1].[dbo].[Table1].[VendorID] as [e].[VendorID],[#t].[BItemID] = [DB1].[dbo].[Table1].[BItemID] as [e].[BItemID],[#t].[CatalogItemName] = [Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName],[#t].[CityName] = [Server2].[DB3].[dbo].[Table3Address].[CityName] as [sga].[CityName],[#t].[ProvinceName] = [Server2].[DB3].[dbo].[Table3Address].[ProvinceName] as [sga].[ProvinceName],[#t].[CountryCode] = [Server2].[DB3].[dbo].[Table3Address].[CountryCode] as [sga].[CountryCode])) 1 2 1 Table Insert Insert OBJECT:([#t]), SET:([#t].[VendorID] = [DB1].[dbo].[Table1].[VendorID] as [e].[VendorID],[#t].[BItemID] = [DB1].[dbo].[Table1].[BItemID] as [e].[BItemID],[#t].[CatalogItemName] = [Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName],[#t].[CityName] = [Server2].[DB3].[dbo].[Table3Address].[CityName] as [sga].[CityName],[#t].[ProvinceName] = [Server2].[DB3].[dbo].[Table3Address].[ProvinceName] as [sga].[ProvinceName],[#t].[CountryCode] = [Server2].[DB3].[dbo].[Table3Address].[CountryCode] as [sga].[CountryCode]) NULL 1.229637E+13 9.127838E+07 1.229637E+07 9 1.060497E+08 NULL NULL PLAN_ROW 0 1
493 1 |--Top(ROWCOUNT est 0) 1 3 2 Top Top TOP EXPRESSION:((0)) NULL 1.229637E+13 0 1229637 193 2474927 [e].[BItemID], [e].[VendorID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName], [Server2].[DB3].[dbo].[Table3Address].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode] NULL PLAN_ROW 0 1
493 1 |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID])=([Server2].[DB2].[dbo].[Table3].[GroupID]), RESIDUAL:([Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID] as [sga].[GroupCatalogItemID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID])) 1 4 3 Merge Join Right Outer Join MANY-TO-MANY MERGE:([Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID])=([Server2].[DB2].[dbo].[Table3].[GroupID]), RESIDUAL:([Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID] as [sga].[GroupCatalogItemID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]) NULL 1.229637E+13 1341.873 1243297 193 1245290 [e].[BItemID], [e].[VendorID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName], [Server2].[DB3].[dbo].[Table3Address].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode] NULL PLAN_ROW 0 1
253467 1 |--Table Spool 1 5 4 Table Spool Eager Spool NULL NULL 36191.18 0.013125 0.01312922 60 12.09998 [Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table3Address].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode] NULL PLAN_ROW 0 1
0 0 | |--Compute Scalar(DEFINE:([Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table3Address].[CityName]=[Server2].[DB3].[dbo].[Table3Address].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName]=[Server2].[DB3].[dbo].[Table3Address].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode]=[Server2].[DB3].[dbo].[Table3Address].[CountryCode] as [sga].[CountryCode])) 1 6 5 Compute Scalar Compute Scalar DEFINE:([Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table3Address].[CityName]=[Server2].[DB3].[dbo].[Table3Address].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName]=[Server2].[DB3].[dbo].[Table3Address].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode]=[Server2].[DB3].[dbo].[Table3Address].[CountryCode] as [sga].[CountryCode]) [Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table3Address].[CityName]=[Server2].[DB3].[dbo].[Table3Address].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName]=[Server2].[DB3].[dbo].[Table3Address].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode]=[Server2].[DB3].[dbo].[Table3Address].[CountryCode] as [sga].[CountryCode] 36191.18 0 12.07373 60 12.07373 [Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table3Address].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode] NULL PLAN_ROW 0 1
490862 1 | |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1012"."GroupCatalogItemID" "Col1061","Tbl1012"."CityName" "Col1064","Tbl1012"."ProvinceName" "Col1065","Tbl1012"."CountryCode" "Col1067" FROM "DB3"."dbo"."Table3Address" "Tbl1012" WITH (NOLOCK) WHERE "Tbl1012"."AddressTypeID"=(2) ORDER BY "Col1061" ASC)) 1 7 6 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1012"."GroupCatalogItemID" "Col1061","Tbl1012"."CityName" "Col1064","Tbl1012"."ProvinceName" "Col1065","Tbl1012"."CountryCode" "Col1067" FROM "DB3"."dbo"."Table3Address" "Tbl1012" WITH (NOLOCK) WHERE "Tbl1012"."AddressTypeID"=(2) ORDER BY "Col1061" ASC) NULL 36191.18 0 12.07373 60 12.07373 [Server2].[DB3].[dbo].[Table3Address].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table3Address].[CityName], [Server2].[DB3].[dbo].[Table3Address].[ProvinceName], [Server2].[DB3].[dbo].[Table3Address].[CountryCode] NULL PLAN_ROW 0 1
493 1 |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[GroupID])=([Server2].[DB3].[dbo].[Table4].[CatalogItemID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID])) 1 17 4 Merge Join Left Outer Join MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[GroupID])=([Server2].[DB3].[dbo].[Table4].[CatalogItemID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID]) NULL 3.396772E+09 0.1130406 499.8938 150 638.8679 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
493 1 |--Sort(ORDER BY:([Server2].[DB2].[dbo].[Table3].[GroupID] ASC)) 1 18 17 Sort Sort ORDER BY:([Server2].[DB2].[dbo].[Table3].[GroupID] ASC) NULL 130430.9 0.01126126 10.17338 19 51.93803 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID] NULL PLAN_ROW 0 1
493 1 | |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[VendorID])=([e].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID])) 1 19 18 Merge Join Right Outer Join MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[VendorID])=([e].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID]) NULL 130430.9 0.067921 0.4183601 19 41.75339 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID] NULL PLAN_ROW 0 1
0 0 | |--Compute Scalar(DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID])) 1 20 19 Compute Scalar Compute Scalar DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]) [Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID] 101456 0 33.82867 15 33.82867 [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID] NULL PLAN_ROW 0 1
101444 1 | | |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1008"."GroupID" "Col1079","Tbl1008"."VendorID" "Col1080" FROM "DB2"."dbo"."Table3" "Tbl1008" WITH (NOLOCK) ORDER BY "Col1080" ASC)) 1 21 20 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1008"."GroupID" "Col1079","Tbl1008"."VendorID" "Col1080" FROM "DB2"."dbo"."Table3" "Tbl1008" WITH (NOLOCK) ORDER BY "Col1080" ASC) NULL 101456 0 33.82867 15 33.82867 [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID] NULL PLAN_ROW 0 1
329 1 | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([e].[BSystemID], [e].[BItemID])) 1 27 19 Nested Loops Left Outer Join OUTER REFERENCES:([e].[BSystemID], [e].[BItemID]) NULL 2303.354 0 0.009607235 15 7.438436 [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
329 1 | |--Sort(ORDER BY:([e].[VendorID] ASC)) 1 28 27 Sort Sort ORDER BY:([e].[VendorID] ASC) NULL 2060 0.01126126 0.03547671 19 0.6825175 [e].[BSystemID], [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
329 1 | | |--Clustered Index Scan(OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID')) 1 29 28 Clustered Index Scan Clustered Index Scan OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID') [e].[BSystemID], [e].[BItemID], [e].[VendorID] 2060 0.5623842 0.0408449 38 0.6032292 [e].[BSystemID], [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
0 329 | |--Index Seek(OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD) 1 30 27 Index Seek Index Seek OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD NULL 1.115719 0.003125 0.0001582273 9 6.746311 NULL NULL PLAN_ROW 0 2060
1029458 1 |--Table Spool 1 32 17 Table Spool Eager Spool NULL NULL 260418 0.013125 0.09385068 142 86.92298 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
0 0 |--Compute Scalar(DEFINE:([Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName])) 1 33 32 Compute Scalar Compute Scalar DEFINE:([Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName]) [Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName] 260418 0 86.816 142 86.816 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
3249846 1 |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1010"."CatalogItemID" "Col1075","Tbl1010"."CatalogItemName" "Col1077" FROM "DB3"."dbo"."Table4" "Tbl1010" WITH (NOLOCK) WHERE "Tbl1010"."LangID"=(1033) ORDER BY "Col1075" ASC)) 1 34 33 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1010"."CatalogItemID" "Col1075","Tbl1010"."CatalogItemName" "Col1077" FROM "DB3"."dbo"."Table4" "Tbl1010" WITH (NOLOCK) WHERE "Tbl1010"."LangID"=(1033) ORDER BY "Col1075" ASC) NULL 260418 0 86.816 142 86.816 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1

10 янв 12, 14:07    [11878664]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31983
andrei_78
Заменил. Планы и детали выполнения ниже:
Так у вас запросы сами по себе отличаются, не только в into #t

Во втором запросе нет условия and i.BItemID is null ...

При этом в первом запросе left join dbo.Table2 i логически превращается в not exists
10 янв 12, 14:18    [11878756]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
andrei_78
Member

Откуда:
Сообщений: 15
alexeyvg
andrei_78
Заменил. Планы и детали выполнения ниже:
Так у вас запросы сами по себе отличаются, не только в into #t

Во втором запросе нет условия and i.BItemID is null ...

При этом в первом запросе left join dbo.Table2 i логически превращается в not exists


блин, не полностью скопировал... исправляю:
+ SELECT INTO
493	1	select 
e.VendorID,
e.BItemID,
cil.CatalogItemName,
sga.CityName,
sga.ProvinceName,
sga.CountryCode
into #t
from DB1.dbo.Table1 e with (nolock)
left join dbo.Table2 i with (nolock)
on i.BItemID = e.BItemID
and i.BSystemID = e.BSystemID
left join Server2.DB2.dbo.Table3 sk with (nolock)
on sk.VendorID = e.VendorID
left join Server2.DB3.dbo.Table4 cil with (nolock)
on sk.GroupID = cil.CatalogItemID
and cil.LangID = 1033
left join Server2.DB3.dbo.Table5 sga with (nolock)
on sga.GroupCatalogItemID = sk.GroupID
and sga.AddressTypeID = 2
where
e.ExceptionReason = 'Invalid VendorID'
and e.CreateDate >= dateadd(day,datediff(day,0, dateadd(year,-1,getdate())),0)
and e.VendorID is not null
and i.BItemID is null 1 1 0 NULL NULL NULL NULL 9.562616E+11 NULL NULL NULL 8248581 NULL NULL SELECT INTO 0 NULL
493 1 |--Table Insert(OBJECT:([#t]), SET:([#t].[VendorID] = [DB1].[dbo].[Table1].[VendorID] as [e].[VendorID],[#t].[BItemID] = [DB1].[dbo].[Table1].[BItemID] as [e].[BItemID],[#t].[CatalogItemName] = [Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName],[#t].[CityName] = [Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName],[#t].[ProvinceName] = [Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName],[#t].[CountryCode] = [Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode])) 1 2 1 Table Insert Insert OBJECT:([#t]), SET:([#t].[VendorID] = [DB1].[dbo].[Table1].[VendorID] as [e].[VendorID],[#t].[BItemID] = [DB1].[dbo].[Table1].[BItemID] as [e].[BItemID],[#t].[CatalogItemName] = [Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName],[#t].[CityName] = [Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName],[#t].[ProvinceName] = [Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName],[#t].[CountryCode] = [Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode]) NULL 9.562616E+11 7098516 956261.6 9 8248581 NULL NULL PLAN_ROW 0 1
493 1 |--Top(ROWCOUNT est 0) 1 3 2 Top Top TOP EXPRESSION:((0)) NULL 9.562616E+11 0 95626.16 193 193803.7 [e].[BItemID], [e].[VendorID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
493 1 |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID])=([Server2].[DB2].[dbo].[Table3].[GroupID]), RESIDUAL:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID])) 1 4 3 Merge Join Right Outer Join MANY-TO-MANY MERGE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID])=([Server2].[DB2].[dbo].[Table3].[GroupID]), RESIDUAL:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]) NULL 9.562616E+11 243.5889 97752.06 193 98177.56 [e].[BItemID], [e].[VendorID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
253467 1 |--Table Spool 1 5 4 Table Spool Eager Spool NULL NULL 36186.42 0.013125 0.01312751 60 12.09839 [Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
0 0 | |--Compute Scalar(DEFINE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName]=[Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName]=[Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode]=[Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode])) 1 6 5 Compute Scalar Compute Scalar DEFINE:([Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName]=[Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName]=[Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode]=[Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode]) [Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID]=[Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID] as [sga].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName]=[Server2].[DB3].[dbo].[Table5].[CityName] as [sga].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName]=[Server2].[DB3].[dbo].[Table5].[ProvinceName] as [sga].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode]=[Server2].[DB3].[dbo].[Table5].[CountryCode] as [sga].[CountryCode] 36186.42 0 12.07214 60 12.07214 [Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
490862 1 | |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1012"."GroupCatalogItemID" "Col1065","Tbl1012"."CityName" "Col1068","Tbl1012"."ProvinceName" "Col1069","Tbl1012"."CountryCode" "Col1071" FROM "DB3"."dbo"."Table5" "Tbl1012" WITH (NOLOCK) WHERE "Tbl1012"."AddressTypeID"=(2) ORDER BY "Col1065" ASC)) 1 7 6 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1012"."GroupCatalogItemID" "Col1065","Tbl1012"."CityName" "Col1068","Tbl1012"."ProvinceName" "Col1069","Tbl1012"."CountryCode" "Col1071" FROM "DB3"."dbo"."Table5" "Tbl1012" WITH (NOLOCK) WHERE "Tbl1012"."AddressTypeID"=(2) ORDER BY "Col1065" ASC) NULL 36186.42 0 12.07214 60 12.07214 [Server2].[DB3].[dbo].[Table5].[GroupCatalogItemID], [Server2].[DB3].[dbo].[Table5].[CityName], [Server2].[DB3].[dbo].[Table5].[ProvinceName], [Server2].[DB3].[dbo].[Table5].[CountryCode] NULL PLAN_ROW 0 1
493 1 |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[GroupID])=([Server2].[DB3].[dbo].[Table4].[CatalogItemID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID])) 1 17 4 Merge Join Left Outer Join MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[GroupID])=([Server2].[DB3].[dbo].[Table4].[CatalogItemID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID]) NULL 2.641941E+08 0.03152704 40.98081 150 169.8125 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
493 1 |--Sort(ORDER BY:([Server2].[DB2].[dbo].[Table3].[GroupID] ASC)) 1 18 17 Sort Sort ORDER BY:([Server2].[DB2].[dbo].[Table3].[GroupID] ASC) NULL 10145.6 0.01126126 0.6198574 19 41.88527 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID] NULL PLAN_ROW 0 1
493 1 | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([e].[VendorID])=([Server2].[DB2].[dbo].[Table3].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID])) 1 19 18 Merge Join Left Outer Join MANY-TO-MANY MERGE:([e].[VendorID])=([Server2].[DB2].[dbo].[Table3].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID]) NULL 10145.6 0.000313 0.02100956 19 41.25415 [e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID] NULL PLAN_ROW 0 1
329 1 | |--Sort(ORDER BY:([e].[VendorID] ASC)) 1 20 19 Sort Sort ORDER BY:([e].[VendorID] ASC) NULL 1 0.01126126 0.000100015 15 7.404163 [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
329 1 | | |--Filter(WHERE:([APRecon].[dbo].[Table2].[BItemID] as [i].[BItemID] IS NULL)) 1 21 20 Filter Filter WHERE:([APRecon].[dbo].[Table2].[BItemID] as [i].[BItemID] IS NULL) NULL 1 0 0.00110561 15 7.392802 [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
329 1 | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([e].[BSystemID], [e].[BItemID])) 1 22 21 Nested Loops Left Outer Join OUTER REFERENCES:([e].[BSystemID], [e].[BItemID]) NULL 2303.354 0 0.009607235 19 7.391696 [e].[BItemID], [e].[VendorID], [i].[BItemID] NULL PLAN_ROW 0 1
329 1 | | |--Clustered Index Scan(OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID')) 1 23 22 Clustered Index Scan Clustered Index Scan OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID') [e].[BSystemID], [e].[BItemID], [e].[VendorID] 2060 0.5623842 0.0408449 38 0.6032292 [e].[BSystemID], [e].[BItemID], [e].[VendorID] NULL PLAN_ROW 0 1
0 329 | | |--Index Seek(OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD) 1 24 22 Index Seek Index Seek OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD [i].[BItemID] 1.115719 0.003125 0.0001582273 11 6.74631 [i].[BItemID] NULL PLAN_ROW 0 2060
0 0 | |--Compute Scalar(DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID])) 1 26 19 Compute Scalar Compute Scalar DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]) [Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID] 101456 0 33.82867 15 33.82867 [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID] NULL PLAN_ROW 0 1
101456 1 | |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1008"."GroupID" "Col1083","Tbl1008"."VendorID" "Col1084" FROM "DB2"."dbo"."Table3" "Tbl1008" WITH (NOLOCK) ORDER BY "Col1084" ASC)) 1 27 26 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1008"."GroupID" "Col1083","Tbl1008"."VendorID" "Col1084" FROM "DB2"."dbo"."Table3" "Tbl1008" WITH (NOLOCK) ORDER BY "Col1084" ASC) NULL 101456 0 33.82867 15 33.82867 [Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID] NULL PLAN_ROW 0 1
1029458 1 |--Table Spool 1 34 17 Table Spool Eager Spool NULL NULL 260393.6 0.013125 0.09384191 142 86.91485 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
0 0 |--Compute Scalar(DEFINE:([Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName])) 1 35 34 Compute Scalar Compute Scalar DEFINE:([Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName]) [Server2].[DB3].[dbo].[Table4].[CatalogItemID]=[Server2].[DB3].[dbo].[Table4].[CatalogItemID] as [cil].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName]=[Server2].[DB3].[dbo].[Table4].[CatalogItemName] as [cil].[CatalogItemName] 260393.6 0 86.80788 142 86.80788 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1
3249846 1 |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1010"."CatalogItemID" "Col1079","Tbl1010"."CatalogItemName" "Col1081" FROM "DB3"."dbo"."Table4" "Tbl1010" WITH (NOLOCK) WHERE "Tbl1010"."LangID"=(1033) ORDER BY "Col1079" ASC)) 1 36 35 Remote Query Remote Query SOURCE:(Server2), QUERY:(SELECT "Tbl1010"."CatalogItemID" "Col1079","Tbl1010"."CatalogItemName" "Col1081" FROM "DB3"."dbo"."Table4" "Tbl1010" WITH (NOLOCK) WHERE "Tbl1010"."LangID"=(1033) ORDER BY "Col1079" ASC) NULL 260393.6 0 86.80788 142 86.80788 [Server2].[DB3].[dbo].[Table4].[CatalogItemID], [Server2].[DB3].[dbo].[Table4].[CatalogItemName] NULL PLAN_ROW 0 1

Спасибо.
10 янв 12, 14:37    [11878950]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
Glory
Member

Откуда:
Сообщений: 104751
Планы разные
здесь Merge Join(Left Outer Join
493	1	            |    |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([e].[VendorID])=([Server2].[DB2].[dbo].[Table3].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID]))	1	16	15	Merge Join	Left Outer Join	MANY-TO-MANY MERGE:([e].[VendorID])=([Server2].[DB2].[dbo].[Table3].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID])	NULL	10145.6	0.000313	0.02100956	19	41.25416	[e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID]	NULL	PLAN_ROW	0	1
329	1	            |         |--Sort(ORDER BY:([e].[VendorID] ASC))	1	17	16	Sort	Sort	ORDER BY:([e].[VendorID] ASC)	NULL	1	0.01126126	0.000100015	15	7.404164	[e].[BItemID], [e].[VendorID]	NULL	PLAN_ROW	0	1
329	1	            |         |    |--Filter(WHERE:([APRecon].[dbo].[Table2].[BItemID] as [i].[BItemID] IS NULL))	1	18	17	Filter	Filter	WHERE:([APRecon].[dbo].[Table2].[BItemID] as [i].[BItemID] IS NULL)	NULL	1	0	0.00110561	15	7.392803	[e].[BItemID], [e].[VendorID]	NULL	PLAN_ROW	0	1
329	1	            |         |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([e].[BSystemID], [e].[BItemID]))	1	19	18	Nested Loops	Left Outer Join	OUTER REFERENCES:([e].[BSystemID], [e].[BItemID])	NULL	2303.354	0	0.009607235	19	7.391697	[e].[BItemID], [e].[VendorID], [i].[BItemID]	NULL	PLAN_ROW	0	1
329	1	            |         |              |--Clustered Index Scan(OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID'))	1	20	19	Clustered Index Scan	Clustered Index Scan	OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID')	[e].[BSystemID], [e].[BItemID], [e].[VendorID]	2060	0.5623842	0.0408449	38	0.6032292	[e].[BSystemID], [e].[BItemID], [e].[VendorID]	NULL	PLAN_ROW	0	1
0	329	            |         |              |--Index Seek(OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD)	1	21	19	Index Seek	Index Seek	OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD	[i].[BItemID]	1.115719	0.003125	0.0001582273	11	6.746311	[i].[BItemID]	NULL	PLAN_ROW	0	2060
0	0	            |         |--Compute Scalar(DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]))	1	23	16	Compute Scalar	Compute Scalar	DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID])	[Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]	101456	0	33.82867	15	33.82867	[Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]	NULL	PLAN_ROW	0	1
101444	1	            |              |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1005"."GroupID" "Col1044","Tbl1005"."VendorID" "Col1045" FROM "DB2"."dbo"."Table3" "Tbl1005" WITH (NOLOCK) ORDER BY "Col1045" ASC))	1	24	23	Remote Query	Remote Query	SOURCE:(Server2), QUERY:(SELECT "Tbl1005"."GroupID" "Col1044","Tbl1005"."VendorID" "Col1045" FROM "DB2"."dbo"."Table3" "Tbl1005" WITH (NOLOCK) ORDER BY "Col1045" ASC)	NULL	101456	0	33.82867	15	33.82867	[Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]	NULL	PLAN_ROW	0	1


а здесь Merge Join(Right Outer Join
493	1	                      |    |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[VendorID])=([e].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID]))	1	19	18	Merge Join	Right Outer Join	MANY-TO-MANY MERGE:([Server2].[DB2].[dbo].[Table3].[VendorID])=([e].[VendorID]), RESIDUAL:([Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]=[DB1].[dbo].[Table1].[VendorID] as [e].[VendorID])	NULL	130430.9	0.067921	0.4183601	19	41.75339	[e].[BItemID], [e].[VendorID], [Server2].[DB2].[dbo].[Table3].[GroupID]	NULL	PLAN_ROW	0	1
0	0	                      |         |--Compute Scalar(DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]))	1	20	19	Compute Scalar	Compute Scalar	DEFINE:([Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID])	[Server2].[DB2].[dbo].[Table3].[GroupID]=[Server2].[DB2].[dbo].[Table3].[GroupID] as [sk].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]=[Server2].[DB2].[dbo].[Table3].[VendorID] as [sk].[VendorID]	101456	0	33.82867	15	33.82867	[Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]	NULL	PLAN_ROW	0	1
101444	1	                      |         |    |--Remote Query(SOURCE:(Server2), QUERY:(SELECT "Tbl1008"."GroupID" "Col1079","Tbl1008"."VendorID" "Col1080" FROM "DB2"."dbo"."Table3" "Tbl1008" WITH (NOLOCK) ORDER BY "Col1080" ASC))	1	21	20	Remote Query	Remote Query	SOURCE:(Server2), QUERY:(SELECT "Tbl1008"."GroupID" "Col1079","Tbl1008"."VendorID" "Col1080" FROM "DB2"."dbo"."Table3" "Tbl1008" WITH (NOLOCK) ORDER BY "Col1080" ASC)	NULL	101456	0	33.82867	15	33.82867	[Server2].[DB2].[dbo].[Table3].[GroupID], [Server2].[DB2].[dbo].[Table3].[VendorID]	NULL	PLAN_ROW	0	1
329	1	                      |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([e].[BSystemID], [e].[BItemID]))	1	27	19	Nested Loops	Left Outer Join	OUTER REFERENCES:([e].[BSystemID], [e].[BItemID])	NULL	2303.354	0	0.009607235	15	7.438436	[e].[BItemID], [e].[VendorID]	NULL	PLAN_ROW	0	1
329	1	                      |              |--Sort(ORDER BY:([e].[VendorID] ASC))	1	28	27	Sort	Sort	ORDER BY:([e].[VendorID] ASC)	NULL	2060	0.01126126	0.03547671	19	0.6825175	[e].[BSystemID], [e].[BItemID], [e].[VendorID]	NULL	PLAN_ROW	0	1
329	1	                      |              |    |--Clustered Index Scan(OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID'))	1	29	28	Clustered Index Scan	Clustered Index Scan	OBJECT:([DB1].[dbo].[Table1].[XPKTable1] AS [e]), WHERE:([DB1].[dbo].[Table1].[CreateDate] as [e].[CreateDate]>=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',dateadd(year,(-1),getdate())),'1900-01-01 00:00:00.000') AND [DB1].[dbo].[Table1].[ExceptionReason] as [e].[ExceptionReason]='Invalid VendorID')	[e].[BSystemID], [e].[BItemID], [e].[VendorID]	2060	0.5623842	0.0408449	38	0.6032292	[e].[BSystemID], [e].[BItemID], [e].[VendorID]	NULL	PLAN_ROW	0	1
0	329	                      |              |--Index Seek(OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD)	1	30	27	Index Seek	Index Seek	OBJECT:([APRecon].[dbo].[Table2].[XIE3Table2] AS [i]), SEEK:([i].[BItemID]=[DB1].[dbo].[Table1].[BItemID] as [e].[BItemID] AND [i].[BSystemID]=[DB1].[dbo].[Table1].[BSystemID] as [e].[BSystemID]) ORDERED FORWARD	NULL	1.115719	0.003125	0.0001582273	9	6.746311	NULL	NULL	PLAN_ROW	0	2060


т.е. оптимизатор решил джойнить таблицы по-другому
10 янв 12, 16:20    [11879989]     Ответить | Цитировать Сообщить модератору
 Re: Разные планы у select и select into при распределенном запросе  [new]
andrei_78
Member

Откуда:
Сообщений: 15
Glory
Планы разные

Вы сравнили планы разных запросов, в сообщении 11878664 запрос для SELECT INTO неправильный (как заметил alexeyvg).
Следует сравнивать запрос в сообщении 11878664 для SELECT и запрос в сообщении 11878950 , в обоих запросах в этом месте используется Merge Join(Left Outer Join).
10 янв 12, 16:46    [11880230]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить