Быстро прикрутить Paging к SQL запросу

[shadow7256]

Глубоко в коде программы обнаружил проблему. На стороне UI есть grid, который отображает данные из таблицы по страницам. По 10 штук на страницу. Но логика выборки данных из базы сделана неправильно:

На каждый запрос от UI сначала достаются ВСЕ записи из таблицы в память и потом уже в памяти выбираются нужные страницы. Грубо говоря

var data = SELECT Column FROM Table

var result = data.Skip(...).Take(...);

База - SQL SERVER.

Нужно Paging делать внутри SQL запроса к базе, а не потом в памяти. Записей до хрена в таблице (больше миллиона).. запрос выполняется долго. Как быстро прикрутить paging к SQL запросу? какие операторы добавить в каком месте?

Основательно переделывать все времени нет. Код достался от прежнего девелопера, клиент орет что надо починить прямо щас...

[ALV00]

http://lmgtfy.com/?q=sql+server+paging

[dema501]

OFFSET/FETCH
OFFSET/LIMIT

если вы планируете использовать paging с ORDER BY по не уникальному полю
результат может быть не предсказуемым

[shadow7256]

OFFSET/FETCH
OFFSET/LIMIT

если вы планируете использовать paging с ORDER BY по не уникальному полю
результат может быть не предсказуемым

это вроде работает только начиная с SQL Server 2012. У клиента 2008 стоит.

[ALV00]

OLAP функции там должны быть. Можно сделать так, хотя это м.б. неоптимально

select * from (
select c1, c2, c3, row_number() over (order by c1) as rn
from test12
)
where rn between 10 and 20

[Вячеслав Викторович]

https://www.google.de/search?q=sql+paging
я помню такой вопрос задавали Тому Кайту, когда ещё не было гугля.
открыл аскТом набрал paging в строке поиска: впервые такое спрашивали 17.6 лет назад!
https://asktom.oracle.com/pls/apex/f?p=100:1:::NO:::
Ёкарный бабай, как летит время! Я ведь тогда уже был отнюдь не юношей!

[shadow7256]

Сделал это все через NHibernate. Вижу, что в итоговом SQL запросе используются TOP (...), ROW_NUMBER (). Все работает..

НО вот какая беда..

В запросе участвует View, которая собирает информацию для грида из 6ти разных таблиц. Количество записей в этом View много (примерно миллион). При каждом запросе к View создаются два запроса:

1. Первый что то типа SELECT COUNT (*) FROM View WHERE ... который возращает общее количество записей для моего запроса (нужно это число показать в Гриде).

2. Соответственно сам запрос, который возвращает нужную порцию данных (первые десять.. вторые десять.. и так далее).

Каким образом можно ускорить выполение данных запросов? Поможет ли в этом случае сделать мою View как indexed? в Indexed views довольно много ограничений, нельзя использовать LEFT, RIGHT joins и еще куча других. У меня во View есть LEFT joins, поэтому кластерный индекс создать не получится.

[Uzito]

A чо, просто добавить счётчик в запрос ломает? Потом показывать значение из любой возващенной записи.

Code: Select all

select * from (
select v.*, row_number() over (order by 1, 2, 3) rn, count(1) over () total_records
from t_view v
where 1=1
) where rn between 20 and 30
order by rn

[shadow7256]

A чо, просто добавить счётчик в запрос ломает? Потом показывать значение из любой возващенной записи.

Code: Select all

select * from (
select v.*, row_number() over (order by 1, 2, 3) rn, count(1) over () total_records
from t_view v
where 1=1
) where rn between 20 and 30
order by rn

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

[Uzito]

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

Что значит не получится? Count(1) over () вернет количество отфильтрованых записей.

[shadow7256]

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

Что значит не получится? Count(1) over () вернет количество отфильтрованых записей.

Да, но только если условие выборки каким то образом добавляется в текст самого View. А у меня текст статический:

Code: Select all

CREATE VIEW [dbo].[TransactionsView]
AS
SELECT t.TransactionId, t.ClientId, t.TCN, t.Name, COUNT(1) over () TotalRecords
FROM   dbo.Transactions as t

Условия выборки добавляются поверх этого View, то есть конечный запрос, который генерится NHibernate выглядит так:

Code: Select all

SELECT TOP (10)  this_.TransactionId,  this_.ClientId, this_.TCN , this_.Name, this_.TotalRecords  
FROM TransactionsView as this_ 
WHERE (this_.TCN like '%4401329997712%') 

то есть сначала выполнится код внутри View который вернет все записи и общее количество записей будет показано в колонке TotalRecords, а уже потом выберутся записи по LIKE, но TotalRecords все равно будет показывать общее количество записей, а не то, что надо.. буду смотреть

[Uzito]

Да, но только если условие выборки каким то образом добавляется в текст самого View. А у меня текст статический:

В моем примере

Code: Select all

where 1=1

Это место, куда ваш фильтр вставлять.

[shadow7256]

Да, но только если условие выборки каким то образом добавляется в текст самого View. А у меня текст статический:

В моем примере

Code: Select all

where 1=1

Это место, куда ваш фильтр вставлять.

Ага, это я понял, но к сожалению не получится вставить

[Dmitry67]

Hibernate зло

[shadow7256]

Hibernate зло

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

[ALV00]

Hibernate умеет вызывать процедуры?