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

Откуда:
Сообщений: 9
Всем привет!

Есть такой запрос (на кириллицу не обращайте внимание, мне так удобнее) для выборки данных для постраничной навигации:

Select * From
(Select * From (Select
НомерСтроки = ROW_NUMBER() Over(Order by СправочникПубликацийПечати._Code desc),
СправочникПубликацийПечати._Description as Наименование,
СправочникПубликацийПечати._Fld9255 as Категория,
СправочникПубликацийПечати._Fld1204 as ДатаЗагрузки,
СправочникПубликацийПечати._Fld1203 as ТекстСтатьи,
СправочникПубликацийПечати._Fld1201 as ПутьКФайлуPDF
From
_Reference100 As СправочникПубликацийПечати
WHERE
contains( * , 'and')
and СправочникПубликацийПечати._Fld1201 is not NULL) as Выборка
WHERE НомерСтроки Between 1 And 10) as Выборка



Запрос отлично отрабатывает, буквально за 1-2 секунды. Но если попробовать отобрать данные с конца, например: "WHERE НомерСтроки Between 38611 And 38616", то всё повисает, и ошибки нет...

Почему повисает?

При этом если поменять строку на "НомерСтроки = ROW_NUMBER() Over(Order by (Select 1))", то всё ОК, работает всё быстро, но тогда нет сортировки по СправочникПубликацийПечати._Code, а она очень нужна.
10 фев 15, 17:16    [17245768]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
Жесть((
Guest
запрос в запросе запроса ваще огонь!! Зачем Вам так все сурово. Зачем первый SELECT нужен совсем не понятно, он явно лишний. Избавляйтесь от символа * в листинге, переучивайтесь избегать его. И в канце концов привидете запрос в нормальную форму. После этого план покажите.
10 фев 15, 17:29    [17245852]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
yukoz
Member

Откуда:
Сообщений: 9
Жесть((
запрос в запросе запроса ваще огонь!! Зачем Вам так все сурово. Зачем первый SELECT нужен совсем не понятно, он явно лишний. Избавляйтесь от символа * в листинге, переучивайтесь избегать его. И в канце концов привидете запрос в нормальную форму. После этого план покажите.


Это я еще сократил запрос, вобще он такой:

;With ВремТабл As
(
Select
НомерСтроки = ROW_NUMBER() Over(Order by (Select 1)),
СправочникПубликацийПечати._Description as Наименование,
СправочникПубликацийПечати._Fld9255 as Категория,
СправочникПубликацийПечати._Fld1204 as ДатаЗагрузки,
СправочникПубликацийПечати._Fld1203 as ТекстСтатьи,
СправочникПубликацийПечати._Fld1201 as ПутьКФайлуPDF
From
_Reference100 As СправочникПубликацийПечати
WHERE
contains( * , 'and')
and СправочникПубликацийПечати._Fld1201 is not NULL
)
Select * From
(Select * From ВремТабл
WHERE НомерСтроки Between (1) And (10)) as Выборка,
(Select top 1 COUNT(*) as ВсегоСтрок From ВремТабл) as Итог


Здесь мне выводиться всё что нужно, все поля и общее количество найденных строк. Но как сделать, чтобы данные сортировались по ДатаЗагрузки или по СправочникПубликацийПечати._Code без зависаний?
10 фев 15, 17:36    [17245898]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
yukoz,

потому что, для выборки строк с 1 по 10 при наличии подходящего индекса достаточно просканировать 10 строк.
а для выборки Between 38611 And 38616 надо уже сканировать все 38616.
10 фев 15, 17:49    [17245974]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
daw,

и даже если индекса подходящего нет, отобрать первые 10 записей в заданном порядке и 38616 - тоже может быть существенно разными задачами.
10 фев 15, 17:54    [17245997]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
Жесть((
Guest
еще разок, приведите запрос в нормальный логический вид (согласно вашему заданию)

contains( * , 'and')
Вам необходимо осуществлять поиск по всем столбцам??
10 фев 15, 17:58    [17246019]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
собственно, это не с конца сервер медленно выбирает. это с начала он умеет отбирать, не упорядочивая все 38616 записей.
а при Over(Order by (Select 1)) упорядочивать ничего вообще не надо.
10 фев 15, 17:59    [17246025]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
yukoz
Member

Откуда:
Сообщений: 9
Жесть((
еще разок, приведите запрос в нормальный логический вид (согласно вашему заданию)

contains( * , 'and')
Вам необходимо осуществлять поиск по всем столбцам??


Там всего один столбец как в полнотекстовом поиске, соответственно при *, он его одного и берет
10 фев 15, 18:01    [17246033]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
вот Paul White об это пишет:
http://sqlblog.com/blogs/paul_white/archive/2010/08/27/sorting-row-goals-and-the-top-100-problem.aspx
10 фев 15, 18:11    [17246083]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
daw
вот Paul White об это пишет:
http://sqlblog.com/blogs/paul_white/archive/2010/08/27/sorting-row-goals-and-the-top-100-problem.aspx

Не видя действительных планов, конечно, трудно говорить, может дело и в другом алгоритме сортировки, но, имхо, если уж про пэйджинг, то лучше почитать у него другую статью Optimising Server-Side Paging - Part I
Кое что и я писал на эту тему: Оконные функции и row goal
В целом, если там все стандартно, то логично, что чем дальше сервер запрашивает данные, тем больше ему приходится сканировать.
Но это наобум все, надо планы смотреть, быстрый и медленный, количество чтений, время и т.д.
10 фев 15, 21:32    [17247016]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
На всякий случай, в 2012 появился новый функционал для постраничной выборки (paging)

OFFSET .... ROWS
FETCH NEXT .... ROWS ONLY

Не путать с курсорами -- это не курсоры.

http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/
USE AdventureWorks2012
GO
--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO
--------------------------------------------------
-- SQL Server 2008 / R2
-- SQL Server 2005
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
GO
11 фев 15, 14:23    [17250235]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
yukoz
Member

Откуда:
Сообщений: 9
Я знаю про эту функцию в 2012-ом, но у меня 2008r2, и пока покупать 2012 никто не хочет.

Вообщем, сделал индексными все используемые колонки (в частности "СправочникПубликацийПечати._Fld1205"), думал, что поможет, но нет, так всё и висит...

вот еще разок пример:

;With ВремТабл As
(
Select 
НомерСтроки = ROW_NUMBER() Over(Order by СправочникПубликацийПечати._Fld1205 desc),
СправочникПубликацийПечати._Description as Наименование, 
СправочникПубликацийПечати._Fld9255 as Категория,
СправочникПубликацийПечати._Fld1205 as ДатаЗагрузки,
СправочникПубликацийПечати._Fld1203 as ТекстСтатьи,
СправочникПубликацийПечати._Fld1201 as ПутьКФайлуPDF
From 
_Reference100 As СправочникПубликацийПечати 
WHERE 
contains( СправочникПубликацийПечати._Fld1203 , 'and') 
)

Select * From
(Select * From ВремТабл
WHERE НомерСтроки Between (1) And (10)) as Выборка,
(Select top 1 COUNT(*) as ВсегоСтрок From ВремТабл) as Итог
ORDER BY ДатаЗагрузки DESC


он отрабатывает за секунду, даже меньше.

но вот если сделать "WHERE НомерСтроки Between (20000) And (20008))", то уже будет отрабатывать за ~30 секунд, а если сделать "WHERE НомерСтроки Between (38940) And (38949))" - это самый конец списка (38949 - последняя запись), то будет вобще крутить более полутора минуты, может быть еще больше, дальше ждать терпения не хватало...

При этом если сортировку убрать "Over(Order by СправочникПубликацийПечати._Fld1205 desc)" на "Over(Order by (Select 1))", то всё начинает летать, практически любой запрос обрабатывается за секунду-две.

Может как-то все-таки можно запрос оптимизировать?

Во вложении план по запросу который выполнялся примерно минуту.

К сообщению приложен файл (plan.rar - 8Kb) cкачать
18 фев 15, 13:45    [17281344]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
yukoz,
Добавьте в индекс [ut].[dbo].[_Reference100].[_Referen100_ByField9439_TSR] поля:
[ut].[dbo].[_Reference100]._Fld1201;
[ut].[dbo].[_Reference100]._Fld1203;
[ut].[dbo].[_Reference100]._Fld9255;

Обязательно одним запросом вам?
Если нет, то разделите на два - первый, который ищет из FTS, и второй который выбирает из найденного нужные по номеру строки.
Если конечно, не может быть так, что FTS вернет вам всю таблицу.

Если одним, то с условием добавления тех полей в индекс, можно вот так попробовать:
+
With ВремТабл As
(
Select top(30009) -- <-- Тут цифру меняйте в зависимости от того, сколько строк спрашиваете
	НомерСтроки = ROW_NUMBER() Over(Order by СправочникПубликацийПечати._Fld1205 desc), --!!!!
	СправочникПубликацийПечати._Description as Наименование, 
	СправочникПубликацийПечати._Fld9255 as Категория,
	СправочникПубликацийПечати._Fld1205 as ДатаЗагрузки,  --!!!!
	СправочникПубликацийПечати._Fld1203 as ТекстСтатьи,
	СправочникПубликацийПечати._Fld1201 as ПутьКФайлуPDF
From 
	_Reference100 As СправочникПубликацийПечати 
WHERE 
	contains( * , 'and')
order by
	НомерСтроки
)
select 
	*,
	ВсегоСтрок = count(*) over()
From 
	ВремТабл
where 
	НомерСтроки Between (30000) And (30009)
order by 
	НомерСтроки


Если не поможет, вот эту статью читайте, попробуйте реализовать описанный там подход.
http://www.sqlservercentral.com/articles/paging/69892/ (метод Key Seek).
18 фев 15, 17:53    [17283420]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
yukoz
Member

Откуда:
Сообщений: 9
SomewhereSomehow, спасибо! Наверное самое оптимальное здесь в 2 запроса искать... Т.к. по одному запросу вы написали, что нужно указать в TOP количество выбираемых записей, но я же их не знаю заранее, их может быть 10, а может быть и миллион.
19 фев 15, 10:07    [17285627]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная навигация - висит при выборке с конца  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
yukoz,

В топ можно указывать переменную, обычно делают как-нибудь так:
+
declare @RegionID int = 40;
declare @PageNumber int = 5, @PageSize int = 20;
with Paged as
(
    select
        rn = row_number() over (order by o.OrderID),
        o.*
    from
        Orders o
        join Customers c on o.CustomerID = c.CustomerID
    where
        c.RegionID = @RegionID
)
select top(@PageSize) * from Paged p where p.rn > @PageNumber*@PageSize - @PageSize
order by rn;

Или вот метод Key Seek, суть в том, что вы выбираете сначала по узкому индексу (который включает в себя только необходимые для нумерации и условий выборки поля и ключ кластерного) те записи что вам нужны, нумеруете их и потом по найденным ключам - уже достаете все остальное. Примерно так:
+
-- Rewrite Key Seek
declare @RegionID int = 40;
declare @PageNumber int = 5, @PageSize int = 20;
with Keys as (
    select top(@PageNumber * @PageSize)
        rn = row_number() over (order by o.OrderID),
        o.OrderID
    from
        Orders o
        join Customers c on o.CustomerID = c.CustomerID
    where
        c.RegionID = @RegionID
    order by
        o.OrderID
),
SelectedKeys as
(
select top(@PageSize)
    rn,
    OrderID
from
    Keys
where
    rn > ((@PageNumber-1)*@PageSize)
order by
    OrderID
)
select
    *
from
    SelectedKeys sk
    join Orders o on sk.OrderID = o.OrderID
order by
    sk.rn
go


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

Вы же еще в том же запросе получаете общее число всех отобранных записей. Вот глядя на план который получился у вас, можно сказать, что вся верхняя ветка - считает тот самый count, причем опять идет поиск по полнотекстовому индексу и т.д. То что я предложил выше count(*) over() - не должно к такой ветке приводить, но эта операция вызовет в плане Rowcount Spool - тоже не быстрая и не дешевая jgthfwbz. Так что лучшим решением - было бы материализовать результаты в промежуточную таблицу, особенно если предикат в Full Text Search селективный и будет отобран небольшой процент записей относительно 2-х милионной основной таблицы. Тогда вы сможете во первых общее число посчитать гораздо быстрее и эффективнее, т.к. агрегировать нужно будет сильно меньше. Во вторых пронумеровать и вывести нужные строки - тоже быстрее.
19 фев 15, 10:25    [17285748]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить