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

Откуда:
Сообщений: 243
Существует 2 базы с набором таблиц одинаковой структуры.
Обе базы имеют некоторые данные в этих таблицах.
Необходимо из одной таблицы одной базы добавить в такую же таблицу другой базы недостающие записи.
На этой таблице стоит уник констрейт на поле Name. Примари кей установлен на поле RECID. Это поле неавтоинкрементное. Соответственно получается следующий скрипт:

INSERT INTO [base2].DBO.LISTVIEW
  SELECT * FROM [base1].DBO.LISTVIEW AS OLD
  WHERE NOT OLD.RECID IN (SELECT RECID FROM [base2].DBO.LISTVIEW)
  AND NOT OLD.NAME IN (SELECT NAME FROM [base2].DBO.LISTVIEW)

Вот можно ли как-то оптимизировать условие WHERE?

Спасибо заранее.
17 сен 09, 14:23    [7673352]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Можно.
Избавьтесь от IN(), например.
17 сен 09, 14:26    [7673381]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
not exists () ?
-------------------------------------
Jedem Das Seine
17 сен 09, 14:27    [7673388]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
europ
Member

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

In и Exists - это плохо! Выборки, которые будут в () повторяются для каждой строки и если данных много, то можно идти пить кофе! Поэтому советую делать так:
INSERT INTO [base2].DBO.LISTVIEW
	SELECT	* 
	FROM	[base1].DBO.LISTVIEW OLD  
				Left Join [base2].DBO.LISTVIEW OLD2 On OLD.RECID = OLD2.RECID
				Left Join [base2].DBO.LISTVIEW OLD3 On OLD.NAME = OLD3.NAME
	Where	OLD2.RECID Is Null
	  And	OLD3.NAME Is Null
17 сен 09, 15:40    [7674134]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
iljy
Member

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

прежде чем оптимизировать - убедитесь, а надо ли. Например посмотрите план запроса.

А замена not in на not exist врядли что-нибудь даст, как правило планировщик эти варианты трактует одинаково. Можно только лишний проход убрать, например так:

INSERT INTO [base2].DBO.LISTVIEW
  SELECT OLD.* FROM
      [base1].DBO.LISTVIEW AS OLD left join [base2].DBO.LISTVIEW new
        on OLD.RECID = NEW.RECID or OLD.NAME = NEW.NAME
WHERE new.RECID is null

но не факт что будет лучше, or может привести к скану, а в исходном варианте - поиск по двум индексам.
17 сен 09, 15:44    [7674173]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
europ, это неправда.
select * 
from test1 t
where name in (select name from test2)                                                                                                                                                    1           1           0           NULL                           NULL                           1                                                                                                                                                                                     NULL                                                                       300288        NULL          NULL          NULL        10,23515         NULL                                                                       NULL     SELECT                                                           0        NULL
  |--Hash Match(Inner Join, HASH:([test_truncate_logging].[dbo].[test2].[name])=([t].[name]), RESIDUAL:([test_truncate_logging].[dbo].[test1].[name] as [t].[name]=[test_truncate_logging].[dbo].[test2].[name]))  1           3           1           Hash Match                     Inner Join                     HASH:([test_truncate_logging].[dbo].[test2].[name])=([t].[name]), RESIDUAL:([test_truncate_logging].[dbo].[test1].[name] as [t].[name]=[test_truncate_logging].[dbo].[test2].[name])  NULL                                                                       300288        0             2,384055      35          10,23515         [t].[name], [t].[number], [t].[type], [t].[low], [t].[high], [t].[status]  NULL     PLAN_ROW                                                         0        1
       |--Hash Match(Aggregate, HASH:([test_truncate_logging].[dbo].[test2].[name]), RESIDUAL:([test_truncate_logging].[dbo].[test2].[name] = [test_truncate_logging].[dbo].[test2].[name]))                       1           4           3           Hash Match                     Aggregate                      HASH:([test_truncate_logging].[dbo].[test2].[name]), RESIDUAL:([test_truncate_logging].[dbo].[test2].[name] = [test_truncate_logging].[dbo].[test2].[name])                           NULL                                                                       1             0             3,792836      13          6,496751         [test_truncate_logging].[dbo].[test2].[name]                               NULL     PLAN_ROW                                                         0        1
       |    |--Table Scan(OBJECT:([test_truncate_logging].[dbo].[test2]))                                                                                                                                          1           5           4           Table Scan                     Table Scan                     OBJECT:([test_truncate_logging].[dbo].[test2])                                                                                                                                        [test_truncate_logging].[dbo].[test2].[name]                               600576        2,043125      0,6607906     13          2,703916         [test_truncate_logging].[dbo].[test2].[name]                               NULL     PLAN_ROW                                                         0        1
       |--Table Scan(OBJECT:([test_truncate_logging].[dbo].[test1] AS [t]))      
17 сен 09, 15:51    [7674228]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
europ
In и Exists - это плохо!
остается только удивляться упорству разработчиков СУБД,
которые продолжают поддерживать эти плохие In и Exists в своем MS SQL Server,
вместо того, чтобы раз и навсегда исключить их из функционала СУБД
и таким образом сразу добиться оптимальности подобных запросов
17 сен 09, 15:54    [7674266]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
Паганель, угу :)
17 сен 09, 15:57    [7674285]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
europ
Member

Откуда:
Сообщений: 377
Паганель
europ
In и Exists - это плохо!
остается только удивляться упорству разработчиков СУБД,
которые продолжают поддерживать эти плохие In и Exists в своем MS SQL Server,
вместо того, чтобы раз и навсегда исключить их из функционала СУБД
и таким образом сразу добиться оптимальности подобных запросов


Могу ошибаться, но при конструкция In и Exists SQL пытается джойнить вложенный селект к основному, если не удастся, то на каждую строку будет выполнять выборку. Сейчас источник моих высказываний найти не смогу =( Но по возможности постараюсь посмотреть! =)
Разработчики оставляют такую конструкцию и будут оставлять, т.к. она необходима в некоторых других случаях!
17 сен 09, 17:31    [7675174]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
iljy
Member

Откуда:
Сообщений: 8711
europ
Паганель
europ
In и Exists - это плохо!
остается только удивляться упорству разработчиков СУБД,
которые продолжают поддерживать эти плохие In и Exists в своем MS SQL Server,
вместо того, чтобы раз и навсегда исключить их из функционала СУБД
и таким образом сразу добиться оптимальности подобных запросов


Могу ошибаться, но при конструкция In и Exists SQL пытается джойнить вложенный селект к основному, если не удастся, то на каждую строку будет выполнять выборку. Сейчас источник моих высказываний найти не смогу =( Но по возможности постараюсь посмотреть! =)
Разработчики оставляют такую конструкцию и будут оставлять, т.к. она необходима в некоторых других случаях!


а что в вашем понимании значит "не удается джойнить"? Вы ради интереса посмотрите на планы запросов с in, exists и join, можно много интересного увидеть. Например то, что во многих случаях они полностью совпадают.
17 сен 09, 17:37    [7675225]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
europ

Сейчас источник моих высказываний найти не смогу =( Но по возможности постараюсь посмотреть! =)
Не надо :)
17 сен 09, 17:52    [7675369]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
В защиту europ-а могу сказать,
что NOT IN генерит плохие планы, если на столбце нет ограничения NOT NULL.
Ну собственно это и понятно. Потому что если столбец имеет значение NULL то контсрукции
NOT IN и LEFT JOIN .... WHERE ... IS NULL
дадут разные результаты.
Поэтому, в общем случае для подобного рода запросов, LEFT JOIN предпочтительнее NOT IN.
17 сен 09, 18:01    [7675441]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
--__Александр__--, Вы мой запрос видели?
Помогите оптимизировать запрос
В чём там плохой план?
Приведите свой запрос с плохим планом, пожалуйста.
18 сен 09, 09:40    [7676902]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Le Peace
--__Александр__--, Вы мой запрос видели?
Помогите оптимизировать запрос
В чём там плохой план?
Приведите свой запрос с плохим планом, пожалуйста.


Пожалуйста:
use AdventureWorks
SET NOCOUNT ON


IF object_id('AdventureWorks.dbo.t1')is not null DROP TABLE dbo.t1
CREATE TABLE t1 (id int)
DECLARE @r int
    SET @r = 1
WHILE @r<=100000
	begin
		INSERT INTO t1
		SELECT @r
		SET @r =@r +1
	end
CREATE  NONCLUSTERED INDEX ix ON dbo.t1(id)
GO
IF object_id('AdventureWorks.dbo.t2')is not null  DROP TABLE dbo.t2
CREATE TABLE t2 (id int)
DECLARE @r int
    SET @r = 1
WHILE @r<=100000
	begin
		INSERT INTO t2
		SELECT @r
		SET @r =@r +100
	end
CREATE  NONCLUSTERED INDEX ix ON dbo.t2(id)
GO


SET SHOWPLAN_ALL ON
GO
select id from t1
WHERE id IN (select id from t2)

select t1.id from t1
INNER join t2 on t1.id = t2.id
GO
SET SHOWPLAN_ALL OFF

Привожу только планы.
StmtText
------------------------------------------------------
select id from t1
WHERE id IN (select id from t2)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Merge Join(Inner Join, MERGE:([AdventureWorks].[dbo].[t2].[id])=([AdventureWorks].[dbo].[t1].[id]), RESIDUAL:([AdventureWorks].[dbo].[t1].[id]=[AdventureWorks].[dbo].[t2].[id]))
|--Stream Aggregate(GROUP BY:([AdventureWorks].[dbo].[t2].[id]))
| |--Index Scan(OBJECT:([AdventureWorks].[dbo].[t2].[ix]), ORDERED FORWARD)
|--Index Scan(OBJECT:([AdventureWorks].[dbo].[t1].[ix]), ORDERED FORWARD)

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

select t1.id from t1
INNER join t2 on t1.id = t2.id

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks].[dbo].[t2].[id], [Expr1008]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([AdventureWorks].[dbo].[t2].[ix]))
|--Index Seek(OBJECT:([AdventureWorks].[dbo].[t1].[ix]), SEEK:([AdventureWorks].[dbo].[t1].[id]=[AdventureWorks].[dbo].[t2].[id]) ORDERED FORWARD)

С NOT IN дела обстает еще хуже.
18 сен 09, 10:48    [7677361]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
--__Александр__--, а почему Вы разные запросы между собой сравниваете?
where ... in vs inner join?
18 сен 09, 10:50    [7677381]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
Le Peace
--__Александр__--, а почему Вы разные запросы между собой сравниваете?
where ... in vs inner join?
А, сорри, мы же говорили про not in.
Ну так покажите, как "хуже".
18 сен 09, 10:50    [7677389]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104760
--__Александр__--


С NOT IN дела обстает еще хуже.

Т.е. вы считаете Merge Join хуже Nested Loops ?
А вы смотреле IO statistics этих запросов ?
18 сен 09, 10:52    [7677401]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
Le Peace
--__Александр__--, а почему Вы разные запросы между собой сравниваете?
where ... in vs inner join?

Да, и всё равно in и inner join - совершенно разные запросы.
18 сен 09, 10:56    [7677439]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
А сколько записей в [base2].DBO.LISTVIEW?
18 сен 09, 11:11    [7677561]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Le Peace
--__Александр__--, а почему Вы разные запросы между собой сравниваете?
where ... in vs inner join?


Потому что europ предложил человеку заменить NOT IN на LEFT JOIN(хотя не совсем верно обосновал почему это надо сделать).
В ответ вы привели запрос с IN, который по вашему является "хорошим".
Тогда я привел сравнение запроса именно WHERE ... IN и INNER JOIN, которые логически являются эквивалентными в моем примере.
Но как я показал, различаются по производительности.
То есть вы согласны, что в общем случае INNER JOIN предпочтительнее WHERE ... IN ?

Ок, пример с NOT IN. С теме же входными данными :
StmtText
---------------------------------------------------------
select id from t1
WHERE id NOT IN (select id from t2)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Parallelism(Gather Streams)
|--Hash Match(Right Anti Semi Join, HASH:([AdventureWorks].[dbo].[t2].[id])=([AdventureWorks].[dbo].[t1].[id]), RESIDUAL:([AdventureWorks].[dbo].[t1].[id]=[AdventureWorks].[dbo].[t2].[id]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([AdventureWorks].[dbo].[t2].[id]))
| |--Index Scan(OBJECT:([AdventureWorks].[dbo].[t2].[ix]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([AdventureWorks].[dbo].[t1].[id]))
|--Nested Loops(Left Anti Semi Join)
|--Nested Loops(Left Anti Semi Join, WHERE:([AdventureWorks].[dbo].[t1].[id] IS NULL))
| |--Index Scan(OBJECT:([AdventureWorks].[dbo].[t1].[ix]))
| |--Top(TOP EXPRESSION:((1)))
| |--Table Scan(OBJECT:([AdventureWorks].[dbo].[t2]))
|--Row Count Spool
|--Index Seek(OBJECT:([AdventureWorks].[dbo].[t2].[ix]), SEEK:([AdventureWorks].[dbo].[t2].[id]=NULL) ORDERED FORWARD)

StmtText
---------------------------------------------------------------------------
select t1.id from t1
LEFT join t2 on t1.id = t2.id
WHERE t2.id IS NULL

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Filter(WHERE:([AdventureWorks].[dbo].[t2].[id] IS NULL))
|--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([AdventureWorks].[dbo].[t2].[id])=([AdventureWorks].[dbo].[t1].[id]), RESIDUAL:([AdventureWorks].[dbo].[t1].[id]=[AdventureWorks].[dbo].[t2].[id]))
|--Index Scan(OBJECT:([AdventureWorks].[dbo].[t2].[ix]), ORDERED FORWARD)
|--Index Scan(OBJECT:([AdventureWorks].[dbo].[t1].[ix]), ORDERED FORWARD)

Теперь, если добавить ограничение NOT NULL
DROP INDEX ix ON dbo.t1
ALTER TABLE t1 ALTER COLUMN id int NOT NULL
CREATE  NONCLUSTERED INDEX ix ON dbo.t1(id)

DROP INDEX ix ON dbo.t2
ALTER TABLE t2 ALTER COLUMN id int NOT NULL
CREATE  NONCLUSTERED INDEX ix ON dbo.t2(id)
select id from t1
WHERE id NOT IN (select id from t2)

StmtText
---------------------------------------------------------
select id from t1
WHERE id NOT IN (select id from t2)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Merge Join(Right Anti Semi Join, MERGE:([AdventureWorks].[dbo].[t2].[id])=([AdventureWorks].[dbo].[t1].[id]), RESIDUAL:([AdventureWorks].[dbo].[t1].[id]=[AdventureWorks].[dbo].[t2].[id]))
|--Stream Aggregate(GROUP BY:([AdventureWorks].[dbo].[t2].[id]))
| |--Index Scan(OBJECT:([AdventureWorks].[dbo].[t2].[ix]), ORDERED FORWARD)
|--Index Scan(OBJECT:([AdventureWorks].[dbo].[t1].[ix]), ORDERED FORWARD)


StmtText
---------------------------------------------------------------------------
select t1.id from t1
LEFT join t2 on t1.id = t2.id
WHERE t2.id IS NULL

StmtText
-------------------------------------------------------------------------------------------------------------------
|--Filter(WHERE:([AdventureWorks].[dbo].[t2].[id] IS NULL))
|--Hash Match(Right Outer Join, HASH:([AdventureWorks].[dbo].[t2].[id])=([AdventureWorks].[dbo].[t1].[id]))
|--Index Scan(OBJECT:([AdventureWorks].[dbo].[t2].[ix]))
|--Index Scan(OBJECT:([AdventureWorks].[dbo].[t1].[ix]))
18 сен 09, 11:15    [7677598]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104760
--__Александр__--
Le Peace
--__Александр__--, а почему Вы разные запросы между собой сравниваете?
where ... in vs inner join?


Потому что europ предложил человеку заменить NOT IN на LEFT JOIN(хотя не совсем верно обосновал почему это надо сделать).
В ответ вы привели запрос с IN, который по вашему является "хорошим".
Тогда я привел сравнение запроса именно WHERE ... IN и INNER JOIN, которые логически являются эквивалентными в моем примере.
Но как я показал, различаются по производительности.
То есть вы согласны, что в общем случае INNER JOIN предпочтительнее WHERE ... IN ?

Статистика чтения для первого вашего примера

Table 't1'. Scan count 1, logical reads 226, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't2'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't1'. Scan count 1000, logical reads 2147, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Вы считаете, что 231 чтений IN() хуже 2149 чтений INNER JOIN-а ?
Или что 2 сканирования хуже 1001 сканирования ?
18 сен 09, 11:18    [7677619]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
--__Александр
...Тогда я привел сравнение запроса именно WHERE ... IN и INNER JOIN, которые логически являются эквивалентными в моем примере.
18 сен 09, 11:19    [7677643]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
сорри не то нажал-)
--__Александр
...Тогда я привел сравнение запроса именно WHERE ... IN и INNER JOIN, которые логически являются эквивалентными в моем примере.

а разве поле id уникально в Ващих таблицах? всё таки запросы ОЧЕНЬ разные
18 сен 09, 11:22    [7677668]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
--__Александр__--
То есть вы согласны, что в общем случае INNER JOIN предпочтительнее WHERE ... IN
Нет, ибо это разные запросы, как я уже сказал выше. Я привёл пример с in, чтобы показать, что необязательно в случае с in будет использоваться nested loops, как утверждал europ.
18 сен 09, 11:22    [7677669]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Glory
--__Александр__--


С NOT IN дела обстает еще хуже.

Т.е. вы считаете Merge Join хуже Nested Loops ?
А вы смотреле IO statistics этих запросов ?


Смотрел EstimateIO и EstimateCPU в инструкции SHOWPLAN_ALL.
По этим данным получается второй запрос работает быстрее.
Да и если чисто логически подумать:
-В первом запросе два индекс скана, а во втром запросе Index Seek по большой таблице t1.
-Одна таблица 1000 строк, вторая - 100 000. Запрос возвращает 1000 строк.
18 сен 09, 11:29    [7677733]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить