ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужное количество строк без использования таблицы
25 сообщений из 59, страница 2 из 3
  2  все
Нужное количество строк без использования таблицы
    #40095892
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
declare @v int, @n int = 1000000;

set statistics time on;

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
@v = a0.n + isNull(a1.n,0)*10 + isNull(a2.n,0)*100 + isNull(a3.n,0)*1000 + isNull(a4.n,0)*10000 + isNull(a5.n,0)*100000
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
@v = row_number() over (order by 1/0)
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

set statistics time off;



Код: plaintext
1.
2.
3.
4.
5.
 SQL Server Execution Times:
   CPU time = 531 ms,  elapsed time = 536 ms.

 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 259 ms.
...
Рейтинг: 0 / 0
08.09.2021, 10:45
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095898
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
[quot HandKot#22369108]
uaggster
Хренасе флэшбэки через, без малого, 15 лет!
:-)

видел на просторах инета еще такой вариант
Код: sql
1.
2.
select row_number() over (order by (select 1/0)) n
from string_split(REPLICATE(cast(' ' as varchar(max)), @n-1), ' ') x



еще для краткости кода (select 1/0) можно заменить на 1/0
...
Рейтинг: 0 / 0
08.09.2021, 11:02
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095899
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
HandKot, класс какой!
Возьму в копилку.
У меня время немного другое получилось

(199999 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 440 ms, elapsed time = 983 ms.

(199999 rows affected)

SQL Server Execution Times:
CPU time = 438 ms, elapsed time = 1187 ms.

Но всё равно - огонь. Жаль, работает с 2017, тогда, кажется STRING_SPLIT появилось.

Кстати, возможно это самый быстрый и наименее ресурсоемкий вариант генерации последовательности 1...N
...
Рейтинг: 0 / 0
08.09.2021, 11:03
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095901
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invm, там, кстати, и isNull лишние. И я даже знаю, откуда они взялись :-)
Изначально там так было:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
a0.n + isNull(a1.n,0)*10 + isNull(a2.n,0)*100 + isNull(a3.n,0)*1000 + isNull(a4.n,0)*10000 + isNull(a5.n,0)*100000 N
from a a0
left join a a1 on @n / 10 > 0
left join a a2 on @n / 100 > 0
left join a a3 on @n / 1000 > 0
left join a a4 on @n / 10000 > 0
left join a a5 on @n / 100000 > 0
Order by 1 ASC



Но "улучшение", очевидно, бессмысленное, о чем я в свое время, и сказал разрабам. Низ поправили, а верх - нет :-)
...
Рейтинг: 0 / 0
08.09.2021, 11:12
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095903
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Кстати, если их убрать - получается не все так однозначно:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
declare @n int = 1000000;

set statistics time on;

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n) a0.n + a1.n*10 + a2.n*100 + a3.n*1000 + a4.n*10000 + a5.n*100000
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5
Order by 1 asc

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n) row_number() over (order by 1/0)
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

set statistics time off;


(1000000 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1077 ms, elapsed time = 4423 ms.

(1000000 rows affected)

SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 5521 ms.
...
Рейтинг: 0 / 0
08.09.2021, 11:26
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095904
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
uaggster


Кстати, возможно это самый быстрый и наименее ресурсоемкий вариант генерации последовательности 1...N


Примерно до 1 млн, а далее намного быстрее ваш код
...
Рейтинг: 0 / 0
08.09.2021, 11:31
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095911
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggster
получается не все так однозначно
Потому что данные клиенту передаются.
Я не просто так в переменную складываю.
...
Рейтинг: 0 / 0
08.09.2021, 11:49
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095921
Uridian
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Uridian
Гость
Все придумано до нас.
...
Рейтинг: 0 / 0
08.09.2021, 12:14
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095933
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
Владислав Колосов,

Ага и каждый раз, для каждого пользователя/запроса запущенного ещё добавить операций чтения (а может ещё и записи в случае вставки в табличную переменную мульти-стейтмент функции). А если ещё эта функция вызывала в

Код: sql
1.
2.
3.
4.
5.
select
  from ... t1
 ...
 outer
 apply dbo.fnSequenceGet ( t2.Val)



или в чём-то подобном. Или если эта функция использована ещё в чём (в той же функции возврата списка дат по периоду) ...
...
Рейтинг: 0 / 0
08.09.2021, 12:36
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095936
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
uaggster,

Думаете надо было дать "настояться" до 15ти :) ?!

Просто очередной холивар по работе возник сейчас. Оппонент как раз за мой старый вариант с мульти-стейтмент функцией и вставкой во временную таблицу топит по мотивам "что бы options не надо было писать". А я предпочитаю даже перед вызовом функции каждый раз коммент поставить.

Так и в обычном CTE разрабочики могут забывать/не знать/ про это ограничение.

Было бы лучше если бы МС дало бы встроенную (и не CLR) функцию для этого дела.

На фоне остальных вариантов предпочитаю именно рекурсивный CTE (с учётом быстродействия, чтений-записей и т. п.).
...
Рейтинг: 0 / 0
08.09.2021, 12:40
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095939
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
[quot Oleg_SQL#22369138]
HandKot
пропущено...

видел на просторах инета еще такой вариант
Код: sql
1.
2.
select row_number() over (order by (select 1/0)) n
from string_split(REPLICATE(cast(' ' as varchar(max)), @n-1), ' ') x



еще для краткости кода (select 1/0) можно заменить на 1/0


Ну и СплитСтринш не всегда был (даже .nodes для XML раньше появился вроде), но сомнительно что это быстрее.

(select 1/0) вряд ли убрать получится из оконной функции, скорее можно просто написать (select 1)
...
Рейтинг: 0 / 0
08.09.2021, 12:42
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095950
DaniilSeryi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DaniilSeryi
Участник
NIIIK,

А вариант с циклом Вам не подойдёт?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
  declare @d date='2021-01-01' --начальная дата, первая строка в создаваемой таблице
  declare @t table(i int,d date) --временная таблица, куда сохраняем записи
  declare @i integer=0 --счётчик строк
  while @i<400 --цикл
  begin
	insert into @t(i,d) 
	select @i+1, DATEADD(DAY,@i,@d) -- плюс 1, чтобы номера строк начинались с 1, а не с 0
	set @i=@i+1
  end
--выводим записи
  select * 
  from @t
  order by i
...
Рейтинг: 0 / 0
08.09.2021, 12:49
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40095959
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
Oleg_SQL,

Отличный вариант, спасибо, по чтениям и записям и CPU выигрывает

Чуть код отформатировал

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
alter function [scratch].[fnSequenceGet_StringSplit](@MaxVal INT)
RETURNS TABLE
RETURN
(
select row_number() over (order by (t.f)) RowNum
  from (select 1 f) t 
  cross
  apply string_split(replicate(cast(' ' as varchar(max)), @MaxVal - 1), ' ') s
)
...
Рейтинг: 0 / 0
08.09.2021, 12:56
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096002
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
NIIIK,

производительность запроса не оценивается количеством чтений.
В случае использования таблицы чисел получить список дат очень легко:

авторавторselect dateadd(day, numbers.nn, @StartDate) dd from dbo.numbers

при том таблица числе может быть сформирована как угодно - неделя, месяц, квартал и так далее.
...
Рейтинг: 0 / 0
08.09.2021, 14:36
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096012
DaniilSeryi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DaniilSeryi
Участник
Владислав Колосов
NIIIK,

производительность запроса не оценивается количеством чтений.
В случае использования таблицы чисел получить список дат очень легко:

авторпропущено...


при том таблица числе может быть сформирована как угодно - неделя, месяц, квартал и так далее.

Владислав, этому посту 13 лет!
...
Рейтинг: 0 / 0
08.09.2021, 15:03
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096015
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
NIIIK
по чтениям и записям и CPU выигрывает
Даже стало интересно, где тут чтение и запись?
И какова методика тестирования, показавшая выигрыш по CPU?
...
Рейтинг: 0 / 0
08.09.2021, 15:05
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096022
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
invm,

https://gyazo.com/30ccc507f43addcdcf874ee23c137020
Такая устроит?

А если хотите видеть запись сделайте мульти-стейтмент.
...
Рейтинг: 0 / 0
08.09.2021, 15:32
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096024
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
Владислав Колосов,

Производительность запроса оценивается операциями I/O, особенно если борешься именно с этим у убираешь циклы на Т-SQL и пишешь один запрос и легаси код меняешь из кучи вложенных multi-statement функций которые на каждый чих делают вставку в табличную переменную.
...
Рейтинг: 0 / 0
08.09.2021, 15:34
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096026
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
Кстати,
бесит MS, уже бы сделал готовую функцию, как тот же string_split сделал (не прошло и 10+++ лет).

Новый метод по производительности хорош, однако "в недрах string_split" всё равно идёт создание отельной строки для каждого случая которые могут быть ограничены внешними условиями и из 10к вернуться могут пару (или ноль) строк.
Но всё равно этот костыль лучший, даже "продвинутый протез", просто надо иметь маленькую последовательность вызовов встроенных функций (а не постоянные итерации в рекурсии).
...
Рейтинг: 0 / 0
08.09.2021, 15:38
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096060
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
NIIIK
Кстати,
бесит MS, уже бы сделал готовую функцию, как тот же string_split сделал (не прошло и 10+++ лет).


В "правильной" базе такая функция не нужна. Равно как и string_split.
...
Рейтинг: 0 / 0
08.09.2021, 16:40
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096065
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
aleks222,

В правильной базе нет операций, часто связанных с внешними данными:

- когда надо сгенерить отдельную запись для каждой даты из периода (причём для каждой записи отдельно)
- когда надо заменить цикл на Т-SQL на обычный запрос
- ....

Вот, пример "от балды", стоит у вас, например, какая-то настройка у пользователя/продукта/... в духе "по умолчанию создавать 10 записей при таком-то действии" - вы как, одним insert-оператором вставляющем 10 строк напишите?
Или будите в T-SQL цикле крутить?
Или вообще, в цикле на клиенте?
...
Рейтинг: 0 / 0
08.09.2021, 16:52
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096075
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
NIIIK
Такая устроит?
Замечательный пример как выбрать наилучший вариант из худших возможных.
...
Рейтинг: 0 / 0
08.09.2021, 17:09
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096079
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
NIIIK
aleks222,

В правильной базе нет операций, часто связанных с внешними данными:

- 1) когда надо сгенерить отдельную запись для каждой даты из периода (причём для каждой записи отдельно)
- когда надо заменить цикл на Т-SQL на обычный запрос
- ....

Вот, пример "от балды", стоит у вас, например, какая-то настройка у пользователя/продукта/... в духе "по умолчанию создавать 10 записей при таком-то действии" - вы как, одним insert-оператором вставляющем 2) 10 строк напишите?
Или будите в T-SQL цикле крутить?
Или вообще, в цикле на клиенте?


1) 100 лет = 30000 и, в большинстве случаев (101% таковых), не нужно,
2) про 10 я помолчу.

Это не стоит функции - таблица справляется лучше и проще.
...
Рейтинг: 0 / 0
08.09.2021, 17:12
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096091
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
invm,

ага, набросил на вентилятор без внятного ответа и всё... я уже и начал забывать типичный СКЛ.Ру
...
Рейтинг: 0 / 0
08.09.2021, 17:30
| Ответить | Цитировать | Написать  
Нужное количество строк без использования таблицы
    #40096094
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NIIIK
Участник
aleks222,

какие ещё 100лет ?!
Вы к тест-кейсу с 10к значений?
Так это простой тест-кейс. А объёмы берутся когда у тебя ЕТЛ-операции да и обычные запросы пользовательские по списку каких-то сущностей и для каждой из них до сделать какое-то перемножение.

Чем "лучше" и "проще" использовать таблицу фейковую тупо ради строк ?!

Вот надо вам будет расписание для списка каких-то сотрудников вывести (естественно колонки для ГУИ будут строками в вашем дата-сете) - вы что бы получить нужное количество строк будите делать выборки из реальной таблицы?
А если такие расписания просматриваются одновременно многими пользователями в ГУИ ?
...
Рейтинг: 0 / 0
08.09.2021, 17:33
| Ответить | Цитировать | Написать  
25 сообщений из 59, страница 2 из 3
  2  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужное количество строк без использования таблицы
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40095921&tid=1684317]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 143ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 58ms
get tp. blocked users:
 1ms
others: 283ms
total:  527ms
созд. / загр.: 527ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]