Как правильно делать Select из большого кол-ва данных (gridview + paging)?

Question

[Therapyx]

Заглянул в будушее и представил, что в 1 из таблиц будет к примеру 1М строк. Решил добавить и проверить как будет все работать. К сожалению мои ожидания совсем не оправдались, и к примеру, чтобы на локальной базе переключится между страницами пейджинга, требуется где-то ~5 секунд. (п.с. а это только от одного пользователя), в дальнейшем все может быть куда хуже.
В голову сразу пришла идея делать селект какого-либо диапазона или же первые "х" строк, но тут опять же получается проблема. А если юзеру нужно будет отсортировать в дальнейшем данные, которые он добавлял 3 года назад? Собственно я в полном замешательстве. Опытные люди, подскажите пожалуста как лучше всего действовать в таких ситуациях? :)
П.с. На данный момент select запрос происходит через следующую процедуру:

ALTER Procedure [dbo].[usp_Purchase_Select_All] (		
		@SelectByDate1 date = null,
		@SelectByDate2 date = null,
		@ddl_Category varchar(70) = null,
                @UserID int
	) as
	 select * 
	 from Purchase
	 where UserID = @UserID
         and P_Date >= coalesce(@SelectByDate1, p_Date)    //параметры для сортировки(если не пустые)
	 and P_Date <= coalesce(@SelectByDate2, p_Date)    //по дате до, после и категориям
	 and P_Text_ddl = coalesce(@ddl_Category, P_Text_ddl)
	 order by p_date desc

Answer 1

[antiso]

Если вопрос по MSSql, то LIMIT в помощь:

select * from Purchase where .... 
order by p_date desc SKIP @page*@limit LIMIT @limit;

Для страницы 5 по 20 будет "SKIP 4*20 LIMIT 20"

Источник https://msdn.microsoft.com/en-us/library/vstudio/b...

Comments

[Therapyx]

если изначально на датабаунде берется определенное кол-во строк, то как быть с сортировками? Которым нужно будет перерывать все данные. П.с. Вопрос не по MySQL в тегах стоит MS SQL c# asp.net

[antiso]

Therapyx: Сортировки изначально в индексы закидывать. Решение для пэйджинга и пересортировки без перерывания базы заново ограниченно возможно только для одной странички.

[Therapyx]

antiso: просто в моей процедуре сделанно все так, что и различные сортировки и датабаунд работают именно через нее. Если делать просто на датабаунд селект с лимитом, то да, все ок. Но когда пользователь будет вводить, что хочет к примеру увидеть все данные за 3 года, то лимит будет так же включен в этот запрос, и в итоге он получит х кол-во строчек.

[antiso]

Therapyx: Это решается простым lifehack: сделать возможной сортировку только на одной страничке, например с помощью UI компонента. А все данные за 3 года вроде бы и так вычислятся. Лимит же просто параметризовать как и те же даты (для MSSQL "SKIP @skip LIMIT @limit" . Либо я не совсем понимаю вопрос.

[Therapyx]

antiso: либо я не понимаю ответа, что очень даже возможно хД Просто сортировка и таблица и вообще все все все, это и есть 1 страничка )

[antiso]

Therapyx: Ответ отсюда https://msdn.microsoft.com/en-us/library/vstudio/b...

[antiso]

Therapyx: Кстати, как это "всё одна страничка"? А если там миллион записей? Я так понял смысл именно в пэйджинге.

[Therapyx]

antiso: получается скип энд лимит должны решить эту проблему? К примеру допишу я его на лимит 100 записей к своей сторед процедуре, сделаю пейджинг по 20 записей(на 1 страницу) и если я перескочу 5ую страницу, то данные все равно будут выдаваться как следует? И так же селект с доп. параметрами сортировки будет работать как я выше описал?

[Therapyx]

antiso: Мне уже стыдно становится хД что-то эта ночь совсем не продуктивная хД

[antiso]

Therapyx: Именно. Для перескока на 5-ю страницу "select * from Purchase
where .... order by p_date desc SKIP 4*20 LIMIT 20"

[antiso]

Ну или :"select * from Purchase where .... order by p_date desc SKIP @page*@limit LIMIT @limit"

[Therapyx]

antiso: Тогда то что надо. Я как-то логически думал, что если я изначально делаю запрос на 100 строк. То столько у меня будет в таблице, учитывая пейджинг и делая потом какие-либо дальнейшие обработки, он бы брал данные так же основывая на первых 100 записей, а не всей таблицы. Спасибо, сегодня уже оставлю это дело, продолжу потом)) Завтра отпишусь по итогам :)

[antiso]

Therapyx: Это именно ограничения на количество возвращаемых строк. Прошерстит он все данные.

[Therapyx]

antiso: Великолепно, значит я просто "точно" не правильно понял смысл этих параметров.

Answer 2

[Night]

Не пытайтесь проверить "скорость" работы на дев-машине, т.к. прод-машины в разы быстрее.

По моему опыту, MySQL на 30 миллионной таблице легко справляется с поиском по маске (LIKE 'abc%') за 0.5 - 1 сек.

Comments

[Therapyx]

Ну даже локалка у меня не из слабых, очень не слабых. Я бы даже сказал хай ендовый игровой )) Но да, до сервера это конечно не дотянет. Но кстати, а если учитывать еще и инет соединение весь трейсрут итд? Это не сильно влияет и даже при этом запросы на прод-машины быстрее ежели на локалке?