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

Откуда:
Сообщений: 3236
можно ли создать запрос вместо


select 1 as 1 union
select 2 as 2 union
.
.
.
select n as n
26 авг 14, 16:09    [16495043]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
мигель1,

Имя колонке UNION задаёт первый SELECT.
Можно использовать рекурсивное CTE, а можно пронумеровать ROW_NUMBER()ом
записи запроса из достаточно большой таблицы.
26 авг 14, 16:12    [16495072]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
WITH N100 AS 
( -- цифры от 0 до 99
	SELECT 0 AS N
	UNION ALL 
	SELECT N100.N + 1 FROM N100
	WHERE N100.N < 99
), -- цифры от 0 до 9999
N10000 AS 
(
	SELECT N = N1.N * 100 + N2.N FROM N100 N1, N100 N2
)
SELECT N FROM N10000
ORDER BY N
26 авг 14, 16:21    [16495145]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
a_voronin,

Пользуйся
CREATE function [dbo].[ftcorNum](@N int)
returns table 
as    
return ( 
        WITH e1(n) AS
        (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        ), -- 10
        e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), 
        e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS b), 
        e4(n) AS (SELECT 1 FROM e3 CROSS JOIN e3 AS b),  
        e5(n) AS (SELECT 1 FROM e4 CROSS JOIN e4 AS b)
          SELECT top(@N) n = ROW_NUMBER() OVER (ORDER BY n) FROM e4 ORDER BY n
       ) 
GO
26 авг 14, 16:23    [16495156]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
a_voronin,

вместо "цифры" лучше написать "числа"
26 авг 14, 16:42    [16495307]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
ROLpogo
a_voronin,

вместо "цифры" лучше написать "числа"


Поехали придираться.
26 авг 14, 16:45    [16495336]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
--__Александр__--,

Давайте немного писькомерством позанимаемся. Мой быстрее. Включите план, статистику или профайлер, и посмотрите.

WITH N100 AS 
( -- цифры от 0 до 99
	SELECT 0 AS N
	UNION ALL 
	SELECT N100.N + 1 FROM N100
	WHERE N100.N < 99
), -- цифры от 0 до 9999
N1000000 AS 
(
	SELECT N = (N1.N * 100 + N2.N) * 100 + N3.N FROM N100 N1, N100 N2, N100 N3
)
SELECT N FROM N1000000
ORDER BY N


go
        WITH e1(n) AS
        (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        ), -- 10
        e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), 
        e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS b), 
        e4(n) AS (SELECT 1 FROM e3 CROSS JOIN e3 AS b),  
        e5(n) AS (SELECT 1 FROM e4 CROSS JOIN e4 AS b)
          SELECT top(1000000) n = ROW_NUMBER() OVER (ORDER BY n) FROM e4 ORDER BY n

go
26 авг 14, 16:46    [16495350]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
предлагаю сверхбыстрый вариант SQL_2014

create table dbo.t1
   (c1 int not null primary key nonclustered)
with (memory_optimized=on)
go

create procedure dbo.native_sp
with native_compilation, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level=snapshot, language=N'us_english')

  declare @i int = 1000
  while @i > 0
  begin
    set @i -= 1
    insert dbo.t1 values (@i)
  end


  SELECT N = a1.c1 + a2.c1 * 1000 FROM dbo.t1 a1, dbo.t1 a2
  ORDER BY a2.c1, a1.c1

end
go
exec dbo.native_sp
go

DROP procedure dbo.native_sp

DROP TABLE dbo.t1
GO 
26 авг 14, 16:57    [16495429]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
мигель1
Member

Откуда:
Сообщений: 3236
Спасибо, но какие то сложные запросы у вас ))
26 авг 14, 17:15    [16495597]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
мигель1
Спасибо, но какие то сложные запросы у вас ))
Потому что нужно всего лишь ОДИН РАЗ завести себе таблицу натуральных чисел нужного размера и пользоваться ею в запросах.
А поделия от a.voronin и --__Александр__-- можно пользовать только в тестовых целях.
26 авг 14, 17:18    [16495622]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
a_voronin
--__Александр__--,

Давайте немного писькомерством позанимаемся. Мой быстрее. Включите план, статистику или профайлер, и посмотрите.

WITH N100 AS 
( -- цифры от 0 до 99
	SELECT 0 AS N
	UNION ALL 
	SELECT N100.N + 1 FROM N100
	WHERE N100.N < 99
), -- цифры от 0 до 9999
N1000000 AS 
(
	SELECT N = (N1.N * 100 + N2.N) * 100 + N3.N FROM N100 N1, N100 N2, N100 N3
)
SELECT N FROM N1000000
ORDER BY N


go
        WITH e1(n) AS
        (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        ), -- 10
        e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), 
        e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS b), 
        e4(n) AS (SELECT 1 FROM e3 CROSS JOIN e3 AS b),  
        e5(n) AS (SELECT 1 FROM e4 CROSS JOIN e4 AS b)
          SELECT top(1000000) n = ROW_NUMBER() OVER (ORDER BY n) FROM e4 ORDER BY n

go


Нашел чем мериться.
А теперь сравни производительность, для получения 100 символов.
Ну то есть мой запрос
select * from [dbo].[ftcorNum](100)

и твой
... select N FROM N1000000 where N <= 100


Оберни писькомерелку хотя бы в функцию, что бы ей было пользоваться удобно и будем сравнивать производительность на N = 10 , 100, 1 000 000.
26 авг 14, 18:04    [16495970]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
invm
мигель1
Спасибо, но какие то сложные запросы у вас ))
Потому что нужно всего лишь ОДИН РАЗ завести себе таблицу натуральных чисел нужного размера и пользоваться ею в запросах.
А поделия от a.voronin и --__Александр__-- можно пользовать только в тестовых целях.


Мы на всех проектах пользуем именно эту табличную функу. С проблемами еще ни разу не сталкивались.
26 авг 14, 18:07    [16495981]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
--__Александр__--
Мы на всех проектах пользуем именно эту табличную функу.
Зачем каждый раз тратить ресурсы на вычисление того, что может быть один раз вычислено, а потом многократно использовано?
Только прежде чем возражать, прикиньте сколько будет весить такая таблица на 1000000 строк и сколько будет отожрано из буферного пула при типовом использовании.
26 авг 14, 19:41    [16496418]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
Exproment
Member

Откуда:
Сообщений: 416
--__Александр__--
С проблемами еще ни разу не сталкивались.

Вы хотите сказать, что:
1. Получаете оценку кардинальности лучше, чем при использовании перманентной индексированной таблицы ?
2. Вычислять одно и то же миллионы раз лучше чем вычислить один раз ?
26 авг 14, 20:01    [16496540]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
a_voronin
Давайте немного писькомерством позанимаемся. Мой быстрее. Включите план, статистику или профайлер, и посмотрите.
Вот именно, - включите и посмотрите. Ваш не может быть быстрее в принципе.

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

+ Тест
use tempdb;
go

create table dbo.Numbers (n int not null primary key);

insert into dbo.Numbers
select top (1000000)
 row_number() over (order by (select 1))
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

create function dbo.[fnNumbers (by a.voronin, modified)]
(
 @n int
)
returns table as
return (
WITH N100 AS 
( -- цифры от 0 до 99
	SELECT 0 AS N
	UNION ALL 
	SELECT N100.N + 1 FROM N100
	WHERE N100.N < 99
) -- цифры от 0 до 9999
select top (@n) row_number() over (order by (select 1)) as n FROM N100 N1, N100 N2, N100 N3
);
go

create function dbo.[fnNumbers (by --__Александр__-- )]
(
 @n int
)
returns table as
return (
        WITH e1(n) AS
        (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        ), -- 10
        e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), 
        e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS b), 
        e4(n) AS (SELECT 1 FROM e3 CROSS JOIN e3 AS b),  
        e5(n) AS (SELECT 1 FROM e4 CROSS JOIN e4 AS b)
          SELECT top(@n) n = ROW_NUMBER() OVER (ORDER BY n) FROM e4 ORDER BY n
);
go

create table #t (id int primary key, q int not null);

insert into #t
select top (10000)
 n, rand(checksum(newid())) * 1000
from
 dbo.Numbers;
go

set statistics /*xml, */io, time on;

select
 count(n.n)
from
 #t t cross apply
 dbo.[fnNumbers (by a.voronin, modified)](t.q) n;

select
 count(n.n)
from
 #t t cross apply
 dbo.[fnNumbers (by --__Александр__-- )](t.q) n;

select
 count(n.n)
from
 #t t join
 dbo.Numbers n on n.n <= t.q

set statistics /*xml, */io, time off;
go

drop function dbo.[fnNumbers (by a.voronin, modified)], dbo.[fnNumbers (by --__Александр__-- )];
drop table dbo.Numbers, #t;
go

Результат:
fnNumbers (by a.voronin, modified)Время ЦП = 687 мс, затраченное время = 681 мс.
fnNumbers (by --__Александр__-- )Время ЦП = 686 мс, затраченное время = 682 мс.
Таблица dbo.NumbersВремя ЦП = 328 мс, затраченное время = 341 мс.
26 авг 14, 23:41    [16497324]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Exproment, invm

Спорить тут нечего. Понятно что, перманентная таблица по определению эффективнее любого метода генерации.
При существующих дисках, хранить не проблема и миллиардную таблицу.

Но на определенных типовых задачах - распарсить строку, нагенерить даты за интервал и тд можно пользоваться и виртуальной таблицей. Надо просто понимать, где можно пользоваться генерилкой, а где нет.
27 авг 14, 10:24    [16498467]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
aleks2
Guest
--__Александр__--
Но на определенных типовых задачах - распарсить строку, нагенерить даты за интервал и тд можно пользоваться и виртуальной таблицей. Надо просто понимать, где можно пользоваться генерилкой, а где нет.

Интересуюся.
Если затраты на генерацию всегда выше, а код гораздо более громоздкий.
Зачем? И в чем цимес?
27 авг 14, 10:30    [16498507]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
ееее.... сделайте 1 раз такую таблицу и юзайтее ее..нахрена ее каждый раз генерить то ?
27 авг 14, 10:45    [16498620]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
o-o
Guest
а может, просто жмоты?
о, мама дорогая, сколько же таблица чисел оттяпает на диске-то, жуууть
27 авг 14, 10:55    [16498712]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
Exproment
Member

Откуда:
Сообщений: 416
--__Александр__--
Но на определенных типовых задачах - распарсить строку, нагенерить даты за интервал и тд можно пользоваться и виртуальной таблицей. Надо просто понимать, где можно пользоваться генерилкой, а где нет.

Не очевиден смысл использования ментода, который не по одному критерию не превосходит другое решение. Но в целом да, нужно признавать, что для небольших объемов вид решения не существенен в подавляющем большинстве случаев. Не потому что "лучше", а потому что "не важно".
27 авг 14, 11:16    [16498874]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ место на диске под 1 000 000 чисел

create table rn(n int primary key clustered);
go

;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over (order by (select 0)) from l5 t1, l5 t2)
insert into rn(n)
select top(1000000)
	n
from
	rt;
go

select total_pages * 8.0 / 1024.0 as [SizeMb]
from
	sys.partitions p
		inner join
	sys.allocation_units au on p.hobt_id /* так как только один AU IN_ROW_DATA */ = au.container_id
where
	p.object_id = object_id(N'rn')
go

27 авг 14, 11:18    [16498905]     Ответить | Цитировать Сообщить модератору
 Re: нумерованный список  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
o-o
а может, просто жмоты?
Именно.
Самое забавное, - такие жмоты обычно мало задумываются над тем, что пара-тройка столбцов с неверно выбранным типом отожрут гораздо больше...
27 авг 14, 11:48    [16499162]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить