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

Откуда: Киев, Украина
Сообщений: 11
Для заполнения гридов используется примерно такой синтаксис

                SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber
				,*
			FROM
			(
                            <Filter>  -- отфильтрованный список со всеми полями которые могут участвовать в сортировке
			) AS FilteredResult -- нумеруем согласно запросу сортировки 
		) AS SortedResult -- вырезаем "окно" согласно пейджингу в отсортированном списке
		WHERE
			RowNumber BETWEEN 11 AND 20 
		ORDER BY <Sorting request> 

		SELECT count(*) as Count FROM (<Filter SELECT>) as FilteredResult 

В итоге <Filter SELECT>(а это может быть несколько джойнов достаточно сложной структуры с табличными функциями и учетом прав пользователя) используется два раза.

Второй раз используется только для вычисления общего количества записей прошедших фильтр на что идет 30-40% времени от всего запроса.

Если есть у кого идеи как оптимизировать данную общую схему.
27 май 09, 18:46    [7236679]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
                SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber
				, ROW_NUMBER() OVER (ORDER BY <Sorting request> Desc) as RowNumberDesc
				,*
			FROM
			(
                            <Filter>  -- отфильтрованный список со всеми полями которые могут участвовать в сортировке
			) AS FilteredResult -- нумеруем согласно запросу сортировки 
		) AS SortedResult -- вырезаем "окно" согласно пейджингу в отсортированном списке
		WHERE
			RowNumber BETWEEN 11 AND 20 or RowNumberDesc = 1
		ORDER BY case when RowNumberDesc = 1 then 0 else 0 end, <Sorting request> 


для спящего время бодрствования равносильно сну
27 май 09, 18:53    [7236707]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Получите число записей в отдельном столбце того же запроса:
...
SELECT
 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber,
 count(*) over() cou, *
FROM
...
27 май 09, 18:55    [7236718]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
Anddros
Получите число записей в отдельном столбце того же запроса:
...
SELECT
 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber,
 count(*) over() cou, *
FROM
...


Попробовал
В итоге если раньше
Reads = 1629 Duration = 7
Теперь
Reads = 2152 Duration = 18
27 май 09, 19:34    [7236816]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
Алексей2003
                SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber
				, ROW_NUMBER() OVER (ORDER BY <Sorting request> Desc) as RowNumberDesc
				,*
			FROM
			(
                            <Filter>  -- отфильтрованный список со всеми полями которые могут участвовать в сортировке
			) AS FilteredResult -- нумеруем согласно запросу сортировки 
		) AS SortedResult -- вырезаем "окно" согласно пейджингу в отсортированном списке
		WHERE
			RowNumber BETWEEN 11 AND 20 or RowNumberDesc = 1
		ORDER BY case when RowNumberDesc = 1 then 0 else 0 end, <Sorting request> 


для спящего время бодрствования равносильно сну


Попробовал
В итоге если раньше
Reads = 1629 Duration = 7
Теперь
Reads = 1869 Duration = 16

В общем как то не получается быстрее и не затратней
27 май 09, 19:40    [7236832]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Ищущий истину
Anddros
Получите число записей в отдельном столбце того же запроса:
...
SELECT
 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber,
 count(*) over() cou, *
FROM
...


Попробовал
В итоге если раньше
Reads = 1629 Duration = 7
Теперь
Reads = 2152 Duration = 18
Что с чем сравнивается?
Два исходных запроса с одним предложенным или
только Ваш первый с ROW_NUMBER()OVER()+COUNT(*)OVER()?
27 май 09, 19:45    [7236848]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
insert into #ttt
и т.д.

для спящего время бодрствования равносильно сну
27 май 09, 19:48    [7236858]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
по варианту Anndros
                SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber,
                                 count() over() as cnt
				,*
			FROM
			(
                            <Filter>  -- отфильтрованный список со всеми полями которые могут участвовать в сортировке
			) AS FilteredResult -- нумеруем согласно запросу сортировки 
		) AS SortedResult -- вырезаем "окно" согласно пейджингу в отсортированном списке
		WHERE
			RowNumber BETWEEN 11 AND 20 
		ORDER BY <Sorting request> 


для спящего время бодрствования равносильно сну
27 май 09, 19:51    [7236862]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
iap
Ищущий истину
Anddros
Получите число записей в отдельном столбце того же запроса:
...
SELECT
 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber,
 count(*) over() cou, *
FROM
...


Попробовал
В итоге если раньше
Reads = 1629 Duration = 7
Теперь
Reads = 2152 Duration = 18
Что с чем сравнивается?
Два исходных запроса с одним предложенным или
только Ваш первый с ROW_NUMBER()OVER()+COUNT(*)OVER()?


Убрал свой второй запрос и каждый вариант проверял отдельно
Я и сам удивился что дольше - yj в ексекюшен план появился новый блок сорт 12% (Estimated Opertor Cost - 0,011365). То есть сейчас два блока сортировки в одну сторону и другую (оба 12% и то же самое EOC).
27 май 09, 20:12    [7236908]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
Алексей2003
insert into #ttt
и т.д.

для спящего время бодрствования равносильно сну


Временная таблица с одним полем (ID основной сущности) была в самом начале. Потом в джойне соединял с таблицей основной записи (чтобы взять необходимые поля).

Но с ростом количества записей - текущий вариант оказался более быстрым и не таким ресурсоемким. При этом количество записей пока всего лишь несколько сотен. Если будут тысячи или десятки тысяч - вообще тяжело станет. Пока что коиента устраивает. Но есть возможность ревижена кода и пытаюсь найти более оптимальное решение чем есть сейчас - дабы потом по ночам не искать :)
27 май 09, 20:20    [7236925]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Ищущий истину
Убрал свой второй запрос и каждый вариант проверял отдельно
Я и сам удивился что дольше - yj в ексекюшен план появился новый блок сорт 12% (Estimated Opertor Cost - 0,011365). То есть сейчас два блока сортировки в одну сторону и другую (оба 12% и то же самое EOC).
Ничего не понял.
27 май 09, 20:21    [7236929]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
Ребят я понял без примера тут никак
Сделала табличку, хотя у моих таблицах ID char(14) но мы попользуем Int

CREATE TABLE TestVK 
(
  id int NOT NULL primary KEY CLUSTERED,
  Name nvarchar(200) NOT NULL,
  Name2 nvarchar(200) NOT NULL,
)

DECLARE @i int
SET @i = 1

DECLARE @max int
SET @max = 1000

WHILE @i<@max 
begin
INSERT INTO TestVK (id,Name,Name2)
VALUES ( @i, 'Name'+ cast(@i as nvarchar(4)), 'Name'+ cast((@max-@i) as nvarchar(4)) );
SET @i=@i+1
END

--drop table TestVK

Пробуем

		SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY Name2 ) as RowNumber
				,*
			FROM
			(
				SELECT  
					Id,
					Name,
					Name2
				 FROM TestVk
			) AS FilteredResult 
		) AS SortedFilteredResult
		WHERE
			RowNumber BETWEEN 11 AND 20 
		ORDER BY Name2
		
		SELECT count(*) as Count FROM (SELECT DISTINCT Id FROM TestVk as t ) as p
Во втором селекте специально дистинкт чтобы хоть немного был похож на боевой случай

У меня Reads = 16 Duration = 1

Дальше
		SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY Name2 ) as RowNumber
				 ,count(*) over() as Count
				,*
			FROM
			(
				SELECT  
					Id,
					Name,
					Name2
				 FROM TestVk
			) AS FilteredResult 
		) AS SortedFilteredResult
		WHERE
			RowNumber BETWEEN 11 AND 20 
		ORDER BY Name2

Reads = 2116 Duration = 6
Неясно - наверное проблема в сортировке

Дальше
		SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY Name2 ) as RowNumber
				 ,ROW_NUMBER() OVER (ORDER BY Name2 Desc) as RowNumberDesc
				,*
			FROM
			(
				SELECT  
					Id,
					Name,
					Name2
				 FROM TestVk
			) AS FilteredResult 
		) AS SortedFilteredResult
		WHERE
			RowNumber BETWEEN 11 AND 20 or RowNumberDesc = 1
		ORDER BY case when RowNumberDesc = 1 then 0 else 0 end, Name2

Здесь лучше
Reads = 8 Duration = 3

У меня
Microsoft SQL Server Developer Edition (64-bit) версии 9.0.3077 SP2
на NT AMD64 с 2 процами и 2 Гигами памяти (может здесь где проблемы)

Но с ральным кодом оба метода как я писал оказались хуже
27 май 09, 21:28    [7237072]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
1. добавляем количество строк до 10 000 хотябы.
2. добавляем индексы. без них эта вся карикатура не актуальна.

для спящего время бодрствования равносильно сну
27 май 09, 21:34    [7237092]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
iljy
Guest
Ищущий истину,

IMHO не получится данную схему оптимизировать, ибо для выполнения первого запроса из вашего фильтра реально будет выбрано 20 записей, а при выполнении второго - надо перебрать все. Наверное можно извратится и таки сделать это одним запросом, но скорее всего станет дольше, потому что в первом запросе выбираются данные, т.е. идут обращения к широким индексам либо к таблице в целом, а во втором - могут быть взяты самые узкие индексы, достаточные для ваших join. Можете попробовать оптимизировать на клиенте, т.е. количество записей считать один раз при формировании фильтра, а не для каждой странице. Но такое к сожалению не всегда возможно на реальной обновляемой базе...
27 май 09, 21:35    [7237094]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
Алексей2003
1. добавляем количество строк до 10 000 хотябы.
2. добавляем индексы. без них эта вся карикатура не актуальна.

для спящего время бодрствования равносильно сну


1. Добавил 10 000 сделал ID char(14) как у меня в проекте - примерно тоже самое соотношение.
2. Я не могу покрыть все поля индексами, кроме того у меня есть составные колонки по нескольким полям -хотя это тоже можно решить. Но основная проблема в этом случае у меня размер индексов может оказаться бльше чем размер реальных данных.

Я проанализировал вариант ,count(*) over() as Cnt - для меня он был бы самый оптимальный (если не жрал столько ресурсов). В итоге вижу что с этой строкой добавляется аж 3 Table Spool - что в итоге и тормозит.
28 май 09, 12:57    [7239050]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
iljy
Ищущий истину,

IMHO не получится данную схему оптимизировать, ибо для выполнения первого запроса из вашего фильтра реально будет выбрано 20 записей, а при выполнении второго - надо перебрать все. Наверное можно извратится и таки сделать это одним запросом, но скорее всего станет дольше, потому что в первом запросе выбираются данные, т.е. идут обращения к широким индексам либо к таблице в целом, а во втором - могут быть взяты самые узкие индексы, достаточные для ваших join. Можете попробовать оптимизировать на клиенте, т.е. количество записей считать один раз при формировании фильтра, а не для каждой странице. Но такое к сожалению не всегда возможно на реальной обновляемой базе...


Мне то как раз надо подсчитать количество записей прошедших фильтр. а потом уже со всей этой кучей записей сначало отсортировать а потом вырезать кусок согласно пейженгу. То есть операции сортировки и подсчета количества можно производить одновременно - для них массив записей один и тот же.

Не ясно только почему если используя решение count(*) over() as Cnt, мы имеем аж 3 Table Spool, хотя практическа таже операция (если использовать второй селект) как то обходится без Table Spool.
28 май 09, 13:11    [7239156]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
iljy
Guest
Ищущий истину

Не ясно только почему если используя решение count(*) over() as Cnt, мы имеем аж 3 Table Spool, хотя практическа таже операция (если использовать второй селект) как то обходится без Table Spool.

Да потому что ему надо сначала посчитать значение count(*), чтобы использовать его в каждой результирующей строке.

Попробуйте такие варианты:
;with FilteredResult
as
(   SELECT  
		Id,
		Name,
		Name2
    FROM TestVk where id between 1000 and 50000
)
SELECT RowNumber, id,Name, Name2, null as Cnt
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY Name2 ) as RowNumber
				,Id,Name,Name2
			FROM FilteredResult 
		) AS SortedFilteredResult
		WHERE
			RowNumber BETWEEN 11 AND 20 
union all		
		SELECT null, null, null, null, count(distinct ID) as Count FROM FilteredResult
		ORDER BY Name2

	SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY Name2 ASC) as RowNumber
				 ,ROW_NUMBER() OVER (ORDER BY Name2 DESC) + 10 as RowNumber2
				,*
			FROM
			(
				SELECT  
					Id,
					Name,
					Name2
				 FROM TestVk
				where id between 1000 and 50000
			) AS FilteredResult 
		) AS SortedFilteredResult
		WHERE
			RowNumber BETWEEN 11 AND 20 
		ORDER BY Name2
28 май 09, 14:31    [7239781]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
Ищущий истину
Для заполнения гридов используется примерно такой синтаксис

                SELECT *
		FROM
		(
			SELECT
				 ROW_NUMBER() OVER (ORDER BY <Sorting request>) as RowNumber
				,*
			FROM
			(
                            <Filter>  -- отфильтрованный список со всеми полями которые могут участвовать в сортировке
			) AS FilteredResult -- нумеруем согласно запросу сортировки 
		) AS SortedResult -- вырезаем "окно" согласно пейджингу в отсортированном списке
		WHERE
			RowNumber BETWEEN 11 AND 20 
		ORDER BY <Sorting request> 

		SELECT count(*) as Count FROM (<Filter SELECT>) as FilteredResult 

В итоге <Filter SELECT>(а это может быть несколько джойнов достаточно сложной структуры с табличными функциями и учетом прав пользователя) используется два раза.

Второй раз используется только для вычисления общего количества записей прошедших фильтр на что идет 30-40% времени от всего запроса.

Если есть у кого идеи как оптимизировать данную общую схему.


declare @cnt int
/*твой огромный запрос*/
set @cnt=@@rowcount
select @cnt
28 май 09, 14:41    [7239868]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
iljy
Ищущий истину

Не ясно только почему если используя решение count(*) over() as Cnt, мы имеем аж 3 Table Spool, хотя практическа таже операция (если использовать второй селект) как то обходится без Table Spool.

Да потому что ему надо сначала посчитать значение count(*), чтобы использовать его в каждой результирующей строке.

Вероятно так и есть

Ваши варианты я немного видоизменил (у меня в оригинале ID char(14) и внутренний between там только лишний - нужна конверсация)
В итоге
CPU Reads Writes Duration
Базовый вариант 16 244 0 20
Первый вариант 16 244 0 21(20)
Второй вариант 47 122 0 47

Ваш первый вариант по затратам совпал с базовым и с вариантом что я сам накопал
WITH FilteredResult (Id,Name,Name2) AS
(
	SELECT  Id,Name,Name2 FROM TestVk
)

		SELECT *, (SELECT count(*) FROM FilteredResult) as Cnt
		FROM 
			(SELECT 
				*,
				ROW_NUMBER() OVER (ORDER BY Name2 ) as RowNumber
			FROM FilteredResult
			) AS SortedFilteredResult
		WHERE
			RowNumber BETWEEN 11 AND 20 
		ORDER BY Name2

В общем я еще попробую эти все варианты на боевых данных и напишу позже - возможно что-то изменится в ресурсоемкости
28 май 09, 16:42    [7240923]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
Сид
[declare @cnt int
/*твой огромный запрос*/
set @cnt=@@rowcount
select @cnt[/src]


Проблема в том что мне надо знать количество отфильтрованных записей, а в ваш вариант мне даст количество записей что отдал весь запрос. В данном случае это размер "окна" пейжинга а именно число 10
28 май 09, 16:46    [7240959]     Ответить | Цитировать Сообщить модератору
 Re: Получить количество отфильтрованных записей не делая запрос дважды  [new]
Ищущий истину
Member

Откуда: Киев, Украина
Сообщений: 11
iljy

Попробуйте такие варианты:


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

Результаты на реальных данных (147 записей в основной таблице, + 2 иннер джойна в том числе с одной табличной функцией(до 10 записей), 3 поля вычисляемых (CASE и скалярные функции) )

CPU Reads Writes Duration
Базовый вариант 0 1629 0 7
Первый вариант 0 1629 0 7
Второй вариант 16 1816 0 16
мой вариант 0 1330 0 6

В общем на данный момент самый оптимальный вариант по схеме

;WITH FilteredResult (Id,Name,Name2) AS
(
	SELECT  Id,Name,Name2 FROM TestVk  --  запрос со всеми необходимыми фильтрами
)

		SELECT *, (SELECT count(*) FROM FilteredResult) as Cnt
		FROM 
			(SELECT *, ROW_NUMBER() OVER (ORDER BY Name2 ) as RowNumber
			FROM 
                               (
                               SELECT * -- , здесь можно добавить вычисляемые поля которые потом могут участвовать в сортировке, иначе этот уровень не нужен
                               FROM  FilteredResult                          
                               ) as CalcFilteredResult
			) AS SortedFilteredResult -- отсортируем
		WHERE
			RowNumber BETWEEN 11 AND 20 -- вырезаем окно согласно пейдженга
		ORDER BY Name2

Но все же хотелось бы усовершенствовать и этот вариант - ведь пределу совершенства нет.

Буду рад если кто подкинет еще идею.
Я специально рассписываю подробно варианты и результаты - надеюсь кому то это поможет избежать overtime :) и ошибок при проэктировании
28 май 09, 18:17    [7241472]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить