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

Откуда: из-за границы
Сообщений: 2113
SQL 2008

использую подзапрос для получения постраничного вывода
с неким условием
в добавок получаю общее кол-во записей

SELECT TOP 100 X.IDUser,  X.Total , X.rowid
FROM  
(            
	SELECT             
	A.idUser,  
	ROW_NUMBER()OVER(ORDER BY  (SELECT NULL) ) rowid, 
	COUNT(*) OVER () AS Total FROM TTEST A 
	WHERE (UDF1=1) AND  (UDF4 between 7 and 7)
) X 
WHERE X.rowid > 0



из-за того что кол-во записей в результате очень большое 300-400 тыс
то COUNT(*) OVER () AS Total отрабатывает очень долго до 20 секунд
без COUNT отрабатывает 1-2 секунды

собственно вопрос
как ускорить запрос + получение общего кол-ва записей?
6 апр 15, 14:23    [17478954]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
sys.partiitons
6 апр 15, 14:26    [17478974]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
michael R
SQL 2008

использую подзапрос для получения постраничного вывода
с неким условием
в добавок получаю общее кол-во записей

SELECT TOP 100 X.IDUser,  X.Total , X.rowid
FROM  
(            
	SELECT             
	A.idUser,  
	ROW_NUMBER()OVER(ORDER BY  (SELECT NULL) ) rowid, 
	COUNT(*) OVER () AS Total FROM TTEST A 
	WHERE (UDF1=1) AND  (UDF4 between 7 and 7)
) X 
WHERE X.rowid > 0



из-за того что кол-во записей в результате очень большое 300-400 тыс
то COUNT(*) OVER () AS Total отрабатывает очень долго до 20 секунд
без COUNT отрабатывает 1-2 секунды

собственно вопрос
как ускорить запрос + получение общего кол-ва записей?


У вас скорее всего COUNT(*) OVER () AS Total тормозит. Вообще на индексы надо посмотреть, на план, и ORDER BY (SELECT NULL) вызывает сомнения.
6 апр 15, 14:27    [17478979]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
michael R,

Вы полагаете, что ROW_NUMBER() может быть <1 ??
6 апр 15, 14:28    [17478992]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
a_voronin

SELECT NULL запрос динамический выбор поля меняется
COUNT(*) тормозит без него работает быстро
и это подзапрос
6 апр 15, 14:29    [17479007]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin
ORDER BY (SELECT NULL) вызывает сомнения
Почему?
Этот запрос всё равно не обеспечивает пейджинг.
6 апр 15, 14:29    [17479008]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
iap
это результат диманического запроса
>0 это первая страница
SELECT NULL меняется если есть поле для сортировки
6 апр 15, 14:33    [17479043]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
michael R
>0 это первая страница
А я думаю, что это "все страницы!"
6 апр 15, 14:36    [17479067]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
SELECT TOP 100 X.IDUser,  X.Total , X.rowid
FROM  
(            
	SELECT             
	A.idUser,  
	ROW_NUMBER()OVER(ORDER BY  UDF1 ) ) rowid, 
	COUNT(*) OVER () AS Total FROM TTEST A 
	WHERE (UDF1=1) AND  (UDF4 between 7 and 7)
) X 
WHERE X.rowid > 100

вторая страница 100 записей
6 апр 15, 14:41    [17479113]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
zamatay
Member

Откуда:
Сообщений: 6
michael R,

Я бы разбил на два запроса с начало вычислил total, потом бы делал выборку.
6 апр 15, 15:56    [17479668]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
zamatay

не помогает
6 апр 15, 16:28    [17479876]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 265
michael R
zamatay

не помогает

А как вы считаете количество? Через count()..group by или over? На таблице в 220 тысяч статистика такая:

row_number без COUNT(*) OVER ()
 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.

(строк обработано: 10)
Таблица "CALLS". Число просмотров 1, логических чтений 25, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 1 мс.


row_number с COUNT(*) OVER ()

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.

(строк обработано: 10)
Таблица "Worktable". Число просмотров 3, логических чтений 654241, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "CALLS". Число просмотров 1, логических чтений 5510, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 1453 мс, затраченное время = 1454 мс.


count..group by

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.

(строк обработано: 1)
Таблица "CALLS". Число просмотров 1, логических чтений 1022, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 31 мс, затраченное время = 19 мс.
6 апр 15, 16:53    [17480025]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
michael R
SELECT TOP 100 X.IDUser,  X.Total , X.rowid
FROM  
(            
	SELECT             
	A.idUser,  
	ROW_NUMBER()OVER(ORDER BY  UDF1 ) ) rowid, 
	COUNT(*) OVER () AS Total FROM TTEST A 
	WHERE (UDF1=1) AND  (UDF4 between 7 and 7)
) X 
WHERE X.rowid > 100


вторая страница 100 записей
Только вот X.rowid не обязательно <=200.
Так что нумерация в данном запросе, вообще говоря, - фикция.
6 апр 15, 17:26    [17480183]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
автор
SQL 2008

использую подзапрос для получения постраничного вывода
с неким условием
в добавок получаю общее кол-во записей


В вашем случае только так:
DECLARE
	@Count INT
	
	SET @Count = (SELECT COUNT(*) FROM TTEST)

SELECT TOP 100 X.IDUser,  X.Total , X.rowid
FROM  
(            
	SELECT             
	A.idUser,  
	ROW_NUMBER()OVER(ORDER BY  (SELECT NULL) ) rowid, 
	@Count AS Total FROM TTEST A 
	WHERE (UDF1=1) AND  (UDF4 between 7 and 7)
) X 
WHERE X.rowid > 0
6 апр 15, 19:13    [17480581]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
само собой добавив условия
WHERE (UDF1=1) AND (UDF4 between 7 and 7)
6 апр 15, 19:16    [17480583]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
X-Cite
Проблема что сам Count() занимает больше всего времени
В таблице где то 2 млн записей,но выбираются не все а по условию
ковыряюсь с индексами....
6 апр 15, 19:22    [17480592]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
X-Cite
Проблема что сам Count() занимает больше всего времени
В таблице где то 2 млн записей,но выбираются не все а по условию
ковыряюсь с индексами....
6 апр 15, 19:23    [17480595]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
michael R,

У меня Count в таблице из 17 997 951 записей работает меньше 2 секунд используя Clustered Index Scan.

А если Count + RowNumber использовать в оконной функции то 70% времени уходит на Соединение Lazy Spoolов между собой. И работает тоже довольно долго.

Если вы закомментируете Count() Over() то получите совершенно другой план.

А что там ковыряться с индексами, если у вас четко видны предикаты по которым требуется индекс...
6 апр 15, 19:33    [17480616]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
X-Cite

да получилось
вынес COUNT() наружу
800 тыс таблица
результат 290 тыс
работает меньше секунды
спасибо
7 апр 15, 09:13    [17481874]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
Crimean
Member

Откуда:
Сообщений: 13148
michael R,

это временная победа, не переживайте. скоро опять все станет плохо
7 апр 15, 09:29    [17481945]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
Crimean
michael R,
это временная победа, не переживайте. скоро опять все станет плохо


I'll be back
7 апр 15, 09:40    [17482002]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
michael R
Crimean
michael R,
это временная победа, не переживайте. скоро опять все станет плохо


I'll be back
А что, в Вашем окончательном запросе по-прежнему TOP(100) без ORDER BY?
Это называется "говнокод". Работает по-разному в разное время. Я Вам уже намекал.
7 апр 15, 09:53    [17482049]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
iap
Спасибо
конечно добавил

DECLARE @ICOUNT INT; 
 SELECT @ICOUNT=(COUNT(IDROW)) 
 FROM TTEST  
 WHERE (UDF2 >= 0) AND  (UDF1=1) AND  (UDF3 between 1 and 10) AND  (UDF4 between 7 and 14);
 SELECT TOP 100 
 X.idUser, X.rowid, X.Total   
 FROM  
 (            
	SELECT             
		A.idUser,  
		ROW_NUMBER()OVER(ORDER BY  UDF2 DESC ) rowid, 
		@ICOUNT  AS Total 
		FROM TTEST A 
		WHERE (UDF2 >= 0) AND  (UDF1=1) AND  (UDF3 between 1 and 10) AND  (UDF4 between 7 and 14)
) X 
WHERE X.rowid > 0 
ORDER BY X.rowid ASC;
7 апр 15, 10:02    [17482086]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
iap
michael R
пропущено...


I'll be back
А что, в Вашем окончательном запросе по-прежнему TOP(100) без ORDER BY?
Это называется "говнокод". Работает по-разному в разное время. Я Вам уже намекал.


Это не совсем "говнокод". Зависит от контекста.

SELECT TOP 100 * FROM AAA
ORDER BY
  (SELECT NULL)

Если надо 100 случайных записей, то лучше писать так. Тогда другие разработчики будут понимать что вы хотите именно 100 случайных записей, а не забыли ORDER BY.

Если эти 100 записей всегда отображаются на одной странице (на клиенте), то такой вариант допускается. Если существует вероятность попадания записей по разным страницам, тогда надо рассматривать хотелку.
Например на известных сайтах типа магазина на яндексе, листая по страницам выбранного по критериям товара иногда можно встретить один и тот же товар, а можно какой-то и не встретить. Видимо используется TOP 20 без сортировки и при загрузке каждой страницы пересчитывается по разному. Но тут вопрос, а может так и задумано?
7 апр 15, 10:07    [17482105]     Ответить | Цитировать Сообщить модератору
 Re: CTE Paging + общее кол-во записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
X-Cite
iap
пропущено...
А что, в Вашем окончательном запросе по-прежнему TOP(100) без ORDER BY?
Это называется "говнокод". Работает по-разному в разное время. Я Вам уже намекал.


Это не совсем "говнокод". Зависит от контекста.

SELECT TOP 100 * FROM AAA
ORDER BY
  (SELECT NULL)


Если надо 100 случайных записей, то лучше писать так. Тогда другие разработчики будут понимать что вы хотите именно 100 случайных записей, а не забыли ORDER BY.

Если эти 100 записей всегда отображаются на одной странице (на клиенте), то такой вариант допускается. Если существует вероятность попадания записей по разным страницам, тогда надо рассматривать хотелку.
Например на известных сайтах типа магазина на яндексе, листая по страницам выбранного по критериям товара иногда можно встретить один и тот же товар, а можно какой-то и не встретить. Видимо используется TOP 20 без сортировки и при загрузке каждой страницы пересчитывается по разному. Но тут вопрос, а может так и задумано?
Нет слов!
7 апр 15, 10:12    [17482127]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить