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

Откуда:
Сообщений: 163
Добрый день.
Задача - сделать ссылки на похожие (по параметрам) статьи, притом так, чтобы ссылки оставались постоянными до момента удаления статей (т.е. с одной статьи идут всегда одни и те же ссылки на другие статьи, если они существуют).
Алгоритм простой - берем подходящие по параметрам статьи с ID больше, чем у текущей. Если статей хватает (5 ссылок нужно), то все ок, если не хватает, то берем статьи с начала, т.е. как бы по кругу.
Например, есть 100 статей, а смотрим мы 98ю, то ссылки будут на 99, 100, 1, 2, 3.
Но это не главное. Вопрос именно по SQL.

DECLARE @Quan INT = 5 --количество нужных ссылок

DECLARE @a TABLE (ArticleID INT, Title VARCHAR(150), Description varchar(50))
INSERT @a
	SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT [KEY] As ArticleID FROM CONTAINSTABLE (Articles, Title, @Keywords)
		WHERE [KEY]  > @ArticleID 
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	WHERE 
		IsVisible=1
		AND a.CountryID=@CountryID
		AND (@CityID Is Null OR  Exists(SELECT 1 FROM ArticlesCities WHERE ArticleID=v.ArticleID AND CityID=@CityID))
		AND -- еще набор параметров
	ORDER BY ArticleID

DECLARE @NeedQuan INT = @Quan -  (SELECT Count(1) FROM @a) 

IF @NeedQuan > 0
   INSERT @a
	SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT [KEY] As ArticleID FROM CONTAINSTABLE (Articles, Title, @Keywords)
		WHERE [KEY]  < @ArticleID 
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	WHERE 
		IsVisible=1
		AND a.CountryID=@CountryID
		AND (@CityID Is Null OR  Exists(SELECT 1 FROM ArticlesCities WHERE ArticleID=v.ArticleID AND CityID=@CityID))
		AND -- еще набор параметров
	ORDER BY ArticleID


это решение было выбрано, несмотря на то что оно кривое, так как работает в несколько раз быстрее такого решения:
SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT [KEY] As ArticleID FROM CONTAINSTABLE (Articles, Title, @Keywords)
		WHERE [KEY]  != @ArticleID 
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	WHERE 
		IsVisible=1
		AND a.CountryID=@CountryID
		AND (@CityID Is Null OR  Exists(SELECT 1 FROM ArticlesCities WHERE ArticleID=v.ArticleID AND CityID=@CityID))
		AND -- еще набор параметров
	ORDER BY CASE WHEN a.ArticleID > @ArticleID THEN 1 ELSE 0 END, a.ArticleID 


Второе решение хоть и красивее, но работает в несколько раз медленнее.
Если я правильно понимаю, это связано с тем, что в первом решении, как только находится нужное количество записей по параметрам, поиск прекращается и обрабатывается только часть записей. В то время как во втором случае сначала происходит поиск ВСЕХ подходящий записей по параметрам и только потом сортировка и выборка.

В данных примерах можно исключить полнотекстовый поиск, так как на него всегда уходит менее 3 мс и он роли не играет.

Хотелось бы использовать второй вариант, но как его нужно оптимизировать, чтобы он работал шустрее?

Спасибо.
3 июн 14, 19:35    [16115215]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
kiwo
Member

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

Сделал простой тест:
-- IsMatch заменит нам поиск
create table Articles (ArticleID INT PRIMARY KEY, Title VARCHAR(150), Description varchar(50), IsMatch bit)
-- "Хорошие" статьи
declare @i int = 1
while @i <= 100
begin
  insert Articles values (@i, 'Title ' + cast(@i as varchar), 'Descrption ' + cast(@i as varchar), 1)
  set @i = @i + 1
end
-- "Плохие" статьи
set @i = 101
while @i <= 10000
begin
  insert Articles values (@i, 'Title ' + cast(@i as varchar), 'Descrption ' + cast(@i as varchar), 0)
  set @i = @i + 1
end


Первый запрос:
declare @ArticleID int = 98
DECLARE @a TABLE (ArticleID INT, Title VARCHAR(150), Description varchar(50))
INSERT @a
	SELECT TOP (5) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT ArticleID FROM Articles WHERE IsMatch = 1  AND ArticleID > @ArticleID
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	ORDER BY ArticleID

DECLARE @NeedQuan INT = 5 -  (SELECT Count(1) FROM @a) 

IF @NeedQuan > 0
   INSERT @a
	SELECT TOP (@NeedQuan) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT ArticleID FROM Articles WHERE IsMatch = 1 
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	ORDER BY ArticleID


select * from @a


Второй запрос (пришлось исправить сортировку, иначе результат не тот):
declare @ArticleID int = 98
SELECT TOP 5 a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT ArticleID FROM Articles WHERE IsMatch = 1
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	ORDER BY CASE WHEN a.ArticleID > @ArticleID THEN 1 ELSE 0 END DESC, a.ArticleID 


И там и там результат
99	Title 99	Descrption 99
100	Title 100	Descrption 100
1	Title 1		Descrption 1
2	Title 2		Descrption 2
3	Title 3		Descrption 3


При этом второй запрос немного выигрывает и по logical reads, и по estimated cost. Меняйте мой код и показывайте что вы делаете по-другому.
4 июн 14, 00:41    [16116181]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
Alexey30
Например, есть 100 статей, а смотрим мы 98ю, то ссылки будут на 99, 100, 1, 2, 3.
Но это не главное. Вопрос именно по SQL.


-- патамушто надо головой работать, а не жопой

declare @t table(ArticleID INT not null unique, n int identity primary key clustered);

insert @t
  SELECT [KEY] FROM CONTAINSTABLE (Articles, Title, @Keywords) 
    WHERE [KEY] != @ArticleID
    ORDER BY sign( [KEY] - @ArticleID ), [KEY];

SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
	FROM @t t 
	left outer JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	WHERE 
		IsVisible=1
		AND a.CountryID=@CountryID
		AND (@CityID Is Null OR  Exists(SELECT 1 FROM ArticlesCities WHERE ArticleID=v.ArticleID AND CityID=@CityID))
		AND -- еще набор параметров
	ORDER BY n 
4 июн 14, 08:06    [16116415]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
kiwo
Member

Откуда:
Сообщений: 10
aleks2
-- патамушто надо головой работать, а не жопой
...


Если общее число подходящих статей много больше @Quan, получите некислый оверхед на insert-select
4 июн 14, 17:51    [16121363]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
kiwo
aleks2
-- патамушто надо головой работать, а не жопой
...


Если общее число подходящих статей много больше @Quan, получите некислый оверхед на insert-select


Глупенький, шоб ОТСОРТИРОВАТЬ - все одно выбрать надо ВСЕ.
5 июн 14, 06:02    [16123630]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
kiwo
Member

Откуда:
Сообщений: 10
aleks2,
см аттач

К сообщению приложен файл. Размер - 77Kb
5 июн 14, 11:01    [16124403]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Maxx
Member [скрыт]

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

Какой вы все таки потешный . Па добраму... уже 100 лет известно..ето ети % которые вы обвели- не более чем картинка,ИМХО. Производительность меряестья профайлером как минимум.
5 июн 14, 11:25    [16124566]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8577
kiwo, Qury cost - это то время, который занимает запрос по отношению к пачке запросов. Если запрос единственная операция, то стоимость будет 100%.
2 alex2 - "глупенький" или "умненький" решает доктор, а не Вы и не в этой конференции.
5 июн 14, 11:25    [16124574]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
kiwo
Member

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

statistics io сгодится?

Table '#A5AC0BB5'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Articles'. Scan count 1, logical reads 24, physical reads 0, read-ahead reads 514, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(5 row(s) affected)

(1 row(s) affected)
Table '#A5AC0BB5'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(5 row(s) affected)
Table '#A5AC0BB5'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table '#A6A02FEE'. Scan count 0, logical reads 4016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Articles'. Scan count 2, logical reads 3558, physical reads 0, read-ahead reads 2976, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(999 row(s) affected)

(1 row(s) affected)

(5 row(s) affected)
Table 'Articles'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#A6A02FEE'. 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.

(1 row(s) affected)
5 июн 14, 11:28    [16124599]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
kiwo
Member

Откуда:
Сообщений: 10
Владислав Колосов
kiwo, Qury cost - это то время, который занимает запрос по отношению к пачке запросов. Если запрос единственная операция, то стоимость будет 100%.

В данном случае, пачка запросов состоит из запроса автора и запроса alex2
5 июн 14, 11:30    [16124627]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
kiwo
statistics io сгодится?

Вы сравниваете статистику двух решений ?
Или статитстику разных запросов в одном решении ?
5 июн 14, 16:26    [16127266]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
kiwo
Member

Откуда:
Сообщений: 10
Glory,
Согласен, свалил всё в кучу - вышло непонятно. Попытка номер 2.

Вот статистика запроса, аналогичного первому запросу (батчу) автора:
Table '#AAE04BA2'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Articles'. Scan count 1, logical reads 24, physical reads 2, read-ahead reads 504, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(5 row(s) affected)

(1 row(s) affected)
Table '#AAE04BA2'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(5 row(s) affected)
Table '#AAE04BA2'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


Вот статистика запроса (батча) aleks2:
Table '#AEB0DC86'. Scan count 0, logical reads 4016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Articles'. Scan count 2, logical reads 3558, physical reads 0, read-ahead reads 3026, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(999 row(s) affected)

(1 row(s) affected)

(5 row(s) affected)
Table 'Articles'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#AEB0DC86'. 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.

(1 row(s) affected)


500к статей, 1000 "подходящих".
5 июн 14, 16:31    [16127319]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Alexey30
Member

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

И так, имеем 500 000 статей, около 1000 из которых правильные:
-- IsMatch заменит нам поиск
create table Articles (ArticleID INT PRIMARY KEY, Title VARCHAR(150), Description varchar(50), IsMatch bit)
-- "Хорошие" статьи
declare @i int = 1
while @i <= 500000
begin
  insert Articles SELECT
   @i, 'Title ' + cast(@i as varchar), 'Descrption ' + cast(@i as varchar), CASE WHEN RAND() > 0.998 then 1 ELSE 0 end
  set @i = @i + 1
end

CREATE NONCLUSTERED INDEX IX_Articles ON dbo.Articles	(IsMatch) 
GO
 

Далее приведу все четыре примера именно для данной конкретной таблицы. В качестве «параметров» будем использовать
WHERE EXISTS(SELECT 1 FROM dbo.Articles WHERE Description = a.Description)
DECLARE @Quan INT = 5
declare @ArticleID int = (SELECT TOP 1 ArticleID FROM dbo.Articles WHERE IsMatch = 1 ORDER BY NEWID())

-------------------------------------------------------------------
--------------------РЕШЕНИЕ 1 -------------------------------------
-------------------------------------------------------------------

DECLARE @dt1 DATETIME = GETDATE() 

DECLARE @a TABLE (ArticleID INT, Title VARCHAR(150), Description varchar(50))
INSERT @a
	SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT ArticleID FROM Articles WHERE IsMatch = 1  AND ArticleID > @ArticleID
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	WHERE 
		EXISTS(SELECT 1 FROM dbo.Articles WHERE Description = a.Description)
	ORDER BY ArticleID

DECLARE @NeedQuan INT = @Quan -  (SELECT Count(1) FROM @a) 

IF @NeedQuan > 0
   INSERT @a
	SELECT TOP (@NeedQuan) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT ArticleID FROM Articles WHERE IsMatch = 1   AND ArticleID < @ArticleID
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	WHERE 
		EXISTS(SELECT 1 FROM dbo.Articles WHERE Description = a.Description)
	ORDER BY ArticleID

SELECT * FROM @a

-------------------------------------------------------------------
--------------------РЕШЕНИЕ 2--------------------------------------
-------------------------------------------------------------------

DECLARE @dt2 DATETIME = GETDATE() 

SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
	FROM ( 
		SELECT ArticleID FROM Articles WHERE IsMatch = 1 AND [ArticleID] != @ArticleID
	) t 
	INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID 
	WHERE 
		EXISTS(SELECT 1 FROM dbo.Articles WHERE Description = a.Description)
	ORDER BY CASE WHEN a.ArticleID > @ArticleID THEN 0 ELSE 1 END , a.ArticleID 

-------------------------------------------------------------------
-------------РЕШЕНИЕ 3 (Предложенное aleks2)-----------------------
-------------------------------------------------------------------


DECLARE @dt3 DATETIME = GETDATE() 

declare @t table(ArticleID  INT not null unique, n int identity primary key clustered)
INSERT @t
  SELECT ArticleID FROM dbo.Articles
    WHERE ArticleID   != @ArticleID  AND IsMatch = 1
    ORDER BY sign( ArticleID  - @ArticleID) DESC, ArticleID ;


SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
FROM @t t 
LEFT OUTER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID
WHERE 
	EXISTS(SELECT 1 FROM dbo.Articles WHERE Description = a.Description)
ORDER BY t.n

-------------------------------------------------------------------
--РЕШЕНИЕ 4 (На основе решения от aleks2) -------------------------
-------------------------------------------------------------------

DECLARE @dt4 DATETIME = GETDATE() 

SELECT TOP (@Quan) a.ArticleID, a.Title, a.Description
FROM ( 
	SELECT ArticleID, ROW_NUMBER() OVER (ORDER BY SIGN(ArticleID-@ArticleID) DESC, ArticleID) n
	FROM Articles
	WHERE ArticleID  != @ArticleID  AND IsMatch = 1
) t 
INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID
WHERE 
		EXISTS(SELECT 1 FROM dbo.Articles WHERE Description = a.Description)
ORDER BY t.n

DECLARE @dt5 DATETIME = GETDATE() 

PRINT CONVERT(VARCHAR, @dt2-@dt1, 13)
PRINT CONVERT(VARCHAR, @dt3-@dt2, 13)
PRINT CONVERT(VARCHAR, @dt4-@dt3, 13)
PRINT CONVERT(VARCHAR, @dt5-@dt4, 13)




По тестам получил немного неожиданный результат. В моей ситуации первое решение было самым быстрым. В этом случае вышло так (в среднем после 10 тестов):
Первое решение: 330 мс
Второе решение: 67 мс
Третье решение: 943 мс
Четвертое решение 64 мс

Таким образом, решение на основе предложенного aleks2 (без промежуточных таблиц), выиграло.
Попробую поработать с этим вариантом именно в своей ситуации – возможно поможет.
6 июн 14, 17:16    [16134222]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
Alexey30
И так, имеем 500 000 статей, около 1000 из которых правильные:

Таким образом, решение на основе предложенного aleks2 (без промежуточных таблиц), выиграло.
Попробую поработать с этим вариантом именно в своей ситуации – возможно поможет.


Опять безмозглое применение компьютера для забивания гвоздей.
Если у вас только 1000 из 500 000 статей подпадает под условия отбора, то СНАЧАЛА фильтруете статьи, ПОТОМ применяете полнотекстовый поиск.
А выдумывать "усовершенствование" не надо.
За одно ЭТО
INNER JOIN Articles a WITH(NOLOCK) ON a.ArticleID=t.ArticleID
WHERE 
		EXISTS(SELECT 1 FROM dbo.Articles WHERE Description = a.Description)

надо гнать по полному служебному несоответствию.
6 июн 14, 20:59    [16135269]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить