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

Откуда: от верблюда
Сообщений: 428
select *
from
table1
join table2 on table1.f1 = table2.f1
join table3 on table1.f1 = table3.f1
join table4 on table1.f1 = table4.f1 and table4.f2=1

select *
from
table1
join table2 on table1.f1 = table2.f1
join table3 on table1.f1 = table3.f1
join table4 on table1.f1 = table4.f1
where
table4.f2=1


(имена таблиц изменены)

какой вариант правильнее?

если смотреть план, то SSMS утверждает, что стоимость 1го запроса по отношению к пакету - 52%, а 2го - 48%

статистика 1го запроса
Table 'table3'. Scan count 4, logical reads 25, physical reads 0, read-ahead reads 4.
Table 'table4'. Scan count 4, logical reads 28, physical reads 7, read-ahead reads 0.
Table 'table1'. Scan count 7, logical reads 66, physical reads 0, read-ahead reads 6.
Table 'table2'. Scan count 1, logical reads 46, physical reads 4, read-ahead reads 4.


статистика для 2го запроса
Table 'table3'. Scan count 2, logical reads 7, physical reads 4, read-ahead reads 0.
Table 'table2'. Scan count 517, logical reads 3102, physical reads 453, read-ahead reads 0.
Table 'table1'. Scan count 912, logical reads 4289, physical reads 256, read-ahead reads 0.
Table 'table4'. Scan count 1, logical reads 7, physical reads 7, read-ahead reads 0.


так какой из вариантов все же лучше?
разве 2й не выполняет меньшее число чтений? если выполняет то почему он считается более дорогим?
объясните плз
3 апр 13, 10:54    [14129778]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Гость333
Member

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

Что-то вы не договариваете. Приведённые запросы идентичны (и оптимизатор MSSQL об этом прекрасно "осведомлён"), у них вряд ли могут быть разные планы выполнения.
3 апр 13, 11:04    [14129832]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
однако планы разные...
запрос я упростил за счет удаления других условий и полей.
т.е. изначально было 2 запроса, с идентичным условиями, единственное изменение, которое я внес в один из них, так это переставил условие из в where в join, точно так же как в моих примерах
если нужно, могу выложить запросы целиком...
3 апр 13, 11:12    [14129896]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Glory
Member

Откуда:
Сообщений: 104751
TJ001
запрос я упростил

Вот именно. А как вы добились, чтобы "упрощенная" часть не влияла на оптимизатор ?
3 апр 13, 11:15    [14129923]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Glory,

я имел ввиду что упростил в своем примере...
ща выложу целиком..
3 апр 13, 11:22    [14129959]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Glory
Member

Откуда:
Сообщений: 104751
TJ001
я имел ввиду что упростил в своем примере...
ща выложу целиком..

Т.е. вы хотите узнать, как скажется на плане перенос _одного_ условия при наличии _множества_ условий ?
3 апр 13, 11:25    [14129972]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Glory,

блин... не могу повторить ситуацию :'(

вот чесслово клянусъ вчера планы строились разные, вот 2 запроса и статистика в ним, специально сохранил вчера чтобы спросить, только планов нет, но они отличались!!! поэтому я и решил спросить...
+
dbcc dropcleanbuffers
exec sp_executesql N'
select top 20
_1SJOURN.DATE_TIME_IDDOC as DATE_TIME_IDDOC,
СпрУправлениеСкладом.ROW_ID as ROW_ID,
СпрУправлениеСкладом.sp8097 as Документ,
_1SJOURN.IDDOCDEF as ВидДокумента,
РегСостояниеСчетов.sp787 as ОстатокРезерва,
СпрУправлениеСкладом.Code as Код,
CONVERT(datetime, LEFT(_1SJOURN.DATE_TIME_IDDOC,8),112) as ДатаДокумента,
ISNULL(ДокСчет.sp1558,''     0   '') as Отдел,
СпрУправлениеСкладом.sp8098 as Статус,
ISNULL(ДокСчет.sp101,''     0   '') as Клиент
from sc8096 as СпрУправлениеСкладом

JOIN _1SJOURN(NOLOCK)
ON СпрУправлениеСкладом.sp8968 = _1SJOURN.IDDOC
LEFT JOIN rg785 as РегСостояниеСчетов
ON РегСостояниеСчетов.sp786 = СпрУправлениеСкладом.sp8097

LEFT JOIN dh271 as ДокСчет
ON СпрУправлениеСкладом.sp8968 = ДокСчет.IDDOC
where
РегСостояниеСчетов.Period = {d ''2013-03-01''} and

_1SJOURN.DATE_TIME_IDDOC BETWEEN @QParam_НачДата_1_8_0 AND @QParam_КонДата_1_8_0
AND
(@QParam_ВыбСтатус_1_9_0 = ''     0   '' OR СпрУправлениеСкладом.sp8098 = @QParam_ВыбСтатус_1_9_0)
AND _1SJOURN.IDDOCDEF IN( 271)
order by _1SJOURN.DATE_TIME_IDDOC
', N'@QParam_НачДата_1_8_0 char(8),@QParam_КонДата_1_8_0 char(8),@QParam_ВыбСтатус_1_9_0 char(9)', '20121222', 'яяяяяяяя', '     0   ' 

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 133 ms.

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

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

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 41 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'DH271'. Scan count 2, logical reads 7, physical reads 4, read-ahead reads 0.
Table '_1SJOURN'. Scan count 517, logical reads 3102, physical reads 453, read-ahead reads 0.
Table 'SC8096'. Scan count 912, logical reads 4289, physical reads 256, read-ahead reads 0.
Table 'RG785'. Scan count 1, logical reads 7, physical reads 7, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1421 ms.

(строк обработано: 2)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1422 ms.

(строк обработано: 15)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1422 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1423 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

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

dbcc dropcleanbuffers

exec sp_executesql N'
select top 20
_1SJOURN.DATE_TIME_IDDOC as DATE_TIME_IDDOC,
СпрУправлениеСкладом.ROW_ID as ROW_ID,
СпрУправлениеСкладом.sp8097 as Документ,
_1SJOURN.IDDOCDEF as ВидДокумента,
РегСостояниеСчетов.sp787 as ОстатокРезерва,
СпрУправлениеСкладом.Code as Код,
CONVERT(datetime, LEFT(_1SJOURN.DATE_TIME_IDDOC,8),112) as ДатаДокумента,
ISNULL(ДокСчет.sp1558,''     0   '') as Отдел,
СпрУправлениеСкладом.sp8098 as Статус,
ISNULL(ДокСчет.sp101,''     0   '') as Клиент
from sc8096 as СпрУправлениеСкладом

JOIN _1SJOURN(NOLOCK)
ON СпрУправлениеСкладом.sp8968 = _1SJOURN.IDDOC
LEFT JOIN rg785 as РегСостояниеСчетов
ON РегСостояниеСчетов.sp786 = СпрУправлениеСкладом.sp8097 and РегСостояниеСчетов.Period = {d ''2013-03-01''}

LEFT JOIN dh271 as ДокСчет
ON СпрУправлениеСкладом.sp8968 = ДокСчет.IDDOC
where
--РегСостояниеСчетов.Period = {d ''2013-03-01''} and

_1SJOURN.DATE_TIME_IDDOC BETWEEN @QParam_НачДата_1_8_0 AND @QParam_КонДата_1_8_0
AND
(@QParam_ВыбСтатус_1_9_0 = ''     0   '' OR СпрУправлениеСкладом.sp8098 = @QParam_ВыбСтатус_1_9_0)
AND _1SJOURN.IDDOCDEF IN( 271)
order by _1SJOURN.DATE_TIME_IDDOC
', N'@QParam_НачДата_1_8_0 char(8),@QParam_КонДата_1_8_0 char(8),@QParam_ВыбСтатус_1_9_0 char(9)', '20121222', 'яяяяяяяя', '     0   ' 

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 35 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'DH271'. Scan count 4, logical reads 25, physical reads 0, read-ahead reads 4.
Table 'RG785'. Scan count 4, logical reads 28, physical reads 7, read-ahead reads 0.
Table 'SC8096'. Scan count 7, logical reads 66, physical reads 0, read-ahead reads 6.
Table '_1SJOURN'. Scan count 1, logical reads 46, physical reads 4, read-ahead reads 4.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 247 ms.

(строк обработано: 4)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 248 ms.

(строк обработано: 13)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 248 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 248 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
3 апр 13, 11:46    [14130098]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

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

и еще - результаты у этих 2х запросов разные... почему?
3 апр 13, 11:52    [14130136]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ничего, что у вас там еще и LEFT JOIN, а не INNER JOIN как в начале ?
3 апр 13, 11:53    [14130139]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Glory
Member

Откуда:
Сообщений: 104751
TJ001
и еще - результаты у этих 2х запросов разные... почему?

Потому что это разные запросы.
3 апр 13, 11:54    [14130147]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Glory,

да с left и inner я упустил, уж простите за Отнятое время....
3 апр 13, 11:57    [14130165]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Glory
TJ001
и еще - результаты у этих 2х запросов разные... почему?

Потому что это разные запросы.


разные? в чем принципиальная разница? я не спорю, просто скажите почему перенос условия повлиял на результат?
3 апр 13, 11:59    [14130172]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Glory
Member

Откуда:
Сообщений: 104751
TJ001
разные? в чем принципиальная разница?

В текстах.
3 апр 13, 12:00    [14130183]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
TJ001
Glory
пропущено...

Потому что это разные запросы.


разные? в чем принципиальная разница? я не спорю, просто скажите почему перенос условия повлиял на результат?
Условие в WHERE на поля правой таблицы LEFT JOINа
обычно отфильтровывает записи, в которых получились NULLы
и которыми, собственно говоря, результат LEFT JOINа отличается от результата INNER JOINа.
Это из-за того, что сначала выполняется FROM с JOINами, а WHERE - потом.
3 апр 13, 12:06    [14130235]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
TJ001
разные? в чем принципиальная разница? я не спорю, просто скажите почему перенос условия повлиял на результат?

Такой конструкцией:
  LEFT JOIN Tab t ON t.XXX = xxx
WHERE t.YYY = yyy
вы фактически превращаете LEFT JOIN в INNER JOIN, т.к. запрос без условия WHERE возвращает пустые значения t.*, если не найдены записи по условию "t.XXX = xxx". Но потом эти пустые значения отсеиваются в блоке WHERE по условию "t.YYY = yyy", т.к. условие "NULL = yyy" не выполняется.

Поэтому все условия для "левосоединяемой" таблицы пишут в самом LEFT JOIN:
  LEFT JOIN Tab t ON t.XXX = xxx AND t.YYY = yyy
3 апр 13, 12:11    [14130265]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Гость333
TJ001
разные? в чем принципиальная разница? я не спорю, просто скажите почему перенос условия повлиял на результат?

Такой конструкцией:
...
[/src]

Полагаю после "1С дизайнера/rконструкотра запросов" еще и не такое бывает. Без понимания как писать SQL запросы "плохие" 1С-ники воротят жуть что. Хорошо если 1С-ник знаком с SQL. А ведь бывают и незнакомы.
3 апр 13, 12:22    [14130346]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гость333
Поэтому все условия для "левосоединяемой" таблицы пишут в самом LEFT JOIN:
  LEFT JOIN Tab t ON t.XXX = xxx AND t.YYY = yyy

Хотя, конечно, не все — это я погорячился. Например, "найти значения, отсутствующие в Tab":
  LEFT JOIN Tab t ON t.XXX = xxx
WHERE t.XXX IS NULL

В общем, см. 14130235
3 апр 13, 12:27    [14130407]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
спасибо за подробное разъяснение, я этого не знал
:)
3 апр 13, 13:01    [14130705]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
решил продолжить здесь же...

нужно выбрать из table1 только те записи, которым есть соответствующие в table2 и/или table3

возьму в качестве примера видоизмененный запрос из шапки
получается так:
select *
from
table1
join table4 on table1.f1 = table4.f1

left join table2 on table1.f1 = table2.f1 and table2.f2 = 0
left join table3 on table1.f1 = table3.f1 and table3.f2 = 0

where

table4.f2=1
and
(
table2.f2 is not null
or
table3.f2 is not null
)

но план этого запроса неудовлетворительный, можно еще через union сделать, план будет хороший, но в моем случае это не подходит по некоторым причинам...

есть ли другие варианты?
17 апр 13, 18:16    [14194613]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
iap
Member

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

NOT EXISTS()
17 апр 13, 21:38    [14195290]     Ответить | Цитировать Сообщить модератору
 Re: как лучше использовать дополнительное условие для join?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
iap,

благодарствую, кит-рыба ©

спс:)
18 апр 13, 14:59    [14198860]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить