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

Откуда: г. Иркутск
Сообщений: 162
 		Требуется создать таблицу, в которой содержались бы числа от @LeftBorder до @RightBorder.
Я нашел 2 способа:
DECLARE @LeftBorder int
DECLARE @RightBorder int
SET @LeftBorder = 150
SET @RightBorder = 2000
-----------------------
---1 способ
SELECT 0 as Dig INTO #Digits UNION
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9 

SELECT @LeftBorder + n0.Dig + n1.Dig*10 + n2.Dig*100 + n3.Dig*1000 as Number 
FROM #Digits n0, #Digits n1, #Digits n2, #Digits n3
WHERE @LeftBorder + n0.Dig + n1.Dig*10 + n2.Dig*100 + n3.Dig*1000 <= @RightBorder
ORDER BY 1
DROP TABLE #Digits

---2 способ
DECLARE @i int
CREATE TABLE #Numbers (Number int)

SET @i = @LeftBorder
WHILE @i <= @RightBorder BEGIN
 INSERT INTO #Numbers (Number) Values (@i)
 SET @i = @i + 1
END

SELECT * FROM #Numbers ORDER BY Number

DROP TABLE #Numbers
1 способ плох тем, что неизвестна заранее разрядность чисел @LeftBorder и @RightBorder... С запасом брать не хочется, это приведет к серьезному замедлению запроса
2 способ плох тем, что уж очень он медленный....
Может кто знает еще какой способ?
26 ноя 04, 07:00    [1137161]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
aleks2
Guest 		Самый быстрый:

1) Создаем постоянную таблицу Numbers с числами от 0 до максимально возможного значения @RightBorder-@LeftBorder.
2)
SELECT Number+@LeftBorder FROM Numbers WHERE Number<=@RightBorder-@LeftBorder

быстрее только создать постоянную таблицу Numbers с числами от 0 до максимально возможного значения @RightBorder.
26 ноя 04, 07:46    [1137199]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
 		Можно так, если нужна небольшая таблица. ( до 100000 значений время исполнения < 500 ms )
if object_id('udf_NumberList')> 0 drop function udf_NumberList
go
create function udf_NumberList (@NumValues int)
returns @Results table(Value int)
as

begin

declare @Numbers table (Value int)

declare @i int, @NextNumValues int
set @i = 0

while @i < 10
    begin            
        insert into @Numbers
            select @i * power(10, @@nestlevel-1) 

        set @i = @i + 1 
    end

if len(@NumValues) = 1 
    insert into @Results select Value from @Numbers where Value >= power(0, @@nestlevel - 1)  
else
    begin
        set @NextNumValues =  replicate(1, len(@NumValues) - 1)
        
        insert into @Results
            select t1.Value + t2.Value + power(0, @@nestlevel - 1) as Value
            from @Numbers t1 cross join udf_NumberList(@NextNumValues) t2
            where (t1.Value + t2.Value) * power(0, @@nestlevel - 1) < @NumValues
    end

return

end
go 

-- Fill table
declare @LeftBorder int, @RightBorder int, @NumValues int
set @LeftBorder = 150
set @RightBorder = 2000
set @NumValues = @RightBorder - @LeftBorder + 1

if object_id('tempdb..#Numbers ') > 0 drop table #Numbers 
declare @dt datetime set @dt = getdate()
select (@LeftBorder - 1) + Value as Number into #Numbers  from udf_NumberList(@NumValues)
print datediff(ms, @dt, getdate())
go

-- Test result
select * from #Numbers
26 ноя 04, 07:50    [1137201]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
Philin
Member

Откуда: г. Иркутск
Сообщений: 162
 		to aleks2
> 1) Создаем постоянную таблицу Numbers с числами от 0 до максимально возможного значения @RightBorder-@LeftBorder.

как создаем-то? это же и есть мой вопрос...

to VladRUS.ca
> поставил @RightBorder = 20000
и ваша фнкция работала 17 секунд.... при этом скрипт, приведенный мною во 2 способе отработал менее чем за 10 секунд... что все равно чересчур МЕДЛЕННО
26 ноя 04, 08:09    [1137220]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
 		Это только первый раз медлено, потомучто фунция должна откомпелироваться
26 ноя 04, 08:13    [1137226]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
 		У меня на 20000 уходит 250 ms
26 ноя 04, 08:17    [1137235]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23903
Philin
to aleks2
> 1) Создаем постоянную таблицу Numbers с числами от 0 до максимально возможного значения @RightBorder-@LeftBorder.

как создаем-то? это же и есть мой вопрос...
А этот способ и есть самый самый быстрый - сливать в создаваемую таблицу диапазон чисел из уже заранее созданной и залитой таблицы.
26 ноя 04, 08:31    [1137254]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
sti
Member

Откуда:
Сообщений: 769
автор
как создаем-то? это же и есть мой вопрос...

Какая разница, 17 или 10 сек, если надо ее только 1 раз создать?
На обсуждение куда больше времени ушло :)
26 ноя 04, 08:45    [1137270]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
Лео
Member

Откуда: Москва
Сообщений: 207
 		Вот:
DECLARE @LeftBorder int
DECLARE @RightBorder int
SET @LeftBorder = 150000
SET @RightBorder = 200000
-----------------------

declare @NumLen as int
declare @i as int 
declare @cmd1 as varchar(8000)
declare @cmd2 as varchar(8000)
declare @cmd as varchar(8000)
set @i=0
set @NumLen=len(cast(@RightBorder as varchar(38)))
set @cmd1=''
set @cmd2=''


SELECT 0 as Dig INTO #Digits UNION
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9 


while @i<@NumLen
begin
	set @cmd1=@cmd1 +'+n'+cast(@i as varchar(2))+'.Dig*1'+replicate('0',@i)
	set @cmd2=@cmd2+' #Digits n'++cast(@i as varchar(2))+','
	set @i=@i+1
end
set @cmd='SELECT '+cast(@LeftBorder as varchar(38))+@cmd1+' as Number FROM'+ left(@cmd2,len(@cmd2)-1) + ' WHERE '+cast(@LeftBorder as varchar(38))+@cmd1+' <= '+cast(@RightBorder as varchar(38))+' ORDER BY 1'
exec(@cmd)
DROP TABLE #Digits
26 ноя 04, 09:00    [1137312]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
 		To Leo:

Проверте скорость на генерацию 50000 значений. Ваша работает около 3800 ms моя 360 ms
...
declare @dt datetime set @dt = getdate()
exec(@cmd)
print datediff(ms, @dt, getdate())
...
26 ноя 04, 09:12    [1137327]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
Philin
Member

Откуда: г. Иркутск
Сообщений: 162
 		Всем спасибо...
насчет готовой таблицы натуральных чисел от 0 до 1 000 000 000 в базе я как-то даже не подумал...

to VladRUS.ca
действительно все работает быстро.... разбираюсь в вашей функции...
вам отдельное за нее спасибо!
26 ноя 04, 09:17    [1137339]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
Лео
Member

Откуда: Москва
Сообщений: 207
 		Результаты измерений.
Метод 1. готовая таблица на 1000000
--SET @LeftBorder = 150000
--SET @RightBorder = 200000
--время 2173
--SET @LeftBorder = 10000
--SET @RightBorder = 60000
--время 2233
причем при росте таблицы время должно расти
Метод 2. исполнение текстовой строки.
--SET @LeftBorder = 150000
--SET @RightBorder = 200000
--время 2173
--SET @LeftBorder = 10000
--SET @RightBorder = 60000
--время 263
Метод 3. функция
--SET @LeftBorder = 150000
--SET @RightBorder = 200000
--время 423
--SET @LeftBorder = 10000
--SET @RightBorder = 60000
--время 423
В общем все зависит от задачи, но ф-ция по моему изящнее.
26 ноя 04, 09:31    [1137382]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
saint
Member

Откуда: Архангельск
Сообщений: 2265
 		to VladRUS.ca

Смею заметить, на Ваша функция не работает по крайней мере на одном промежутке 155 - 160.
А вот эта конструкция: 
power(0, @@nestlevel - 1)
выглядит совсем как-то мистически.
26 ноя 04, 09:57    [1137474]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
aleks2
Guest 		А индекс на таблицу?
Сканировать нечестно...
26 ноя 04, 09:59    [1137488]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
saint
to VladRUS.ca

Смею заметить, на Ваша функция не работает по крайней мере на одном промежутке 155 - 160.
А вот эта конструкция: 
power(0, @@nestlevel - 1)
выглядит совсем как-то мистически.


Я ее не тестировал но похоже что Вы правы. Она не работает до 9. 
select Value from udf_NumberList(9)
Надо подправить но уже завтра у нас 2 часа ночи.
26 ноя 04, 10:05    [1137513]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
saint
Member

Откуда: Архангельск
Сообщений: 2265
 		Разобрался с мистикой power.
Поздравляю, гениальный код!

Ошибка похоже действительно только в первой десятке.

Однако есть еще другой подводный камень - как интересно будет работать функция, если ее вызвать из SP, допустим.
26 ноя 04, 10:13    [1137544]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
гостьььь
Guest 		Иногда гениальность конечно трактуют как нечто сродни идиотизму...

Рекурсия то там зачем?
26 ноя 04, 10:42    [1137696]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
Valer
Member

Откуда:
Сообщений: 278
 		может просто создать таблицу с
1 колонкой int, сделать ее счетчиком
с нужным начальным значением = @LeftBorder
и в цикле закатать число записей =@RightBorder - @LeftBorder
26 ноя 04, 12:33    [1138350]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
 		Исправленная функция (HotFix1: @NumValues < 10 ):

if object_id('udf_NumberList')> 0 drop function udf_NumberList
go
create function udf_NumberList (@NumValues int)
returns @Results table(Value int)
as

begin

declare @Numbers table (Value int)

declare @i int, @NextNumValues int
set @i = 0

while @i < 10
    begin            
        insert into @Numbers
            select @i * power(10, @@nestlevel-1) 

        set @i = @i + 1 
    end

if len(@NumValues) = 1
    begin
        insert into @Results select Value from @Numbers where Value >= power(0, @@nestlevel - 1)
        if @@nestlevel = 1
            delete @Results where Value > @NumValues
    end
else
    begin
        set @NextNumValues =  replicate(1, len(@NumValues) - 1)
        
        insert into @Results
            select t1.Value + t2.Value + power(0, @@nestlevel - 1) as Value
            from @Numbers t1 cross join udf_NumberList(@NextNumValues) t2
            where (t1.Value + t2.Value) * power(0, @@nestlevel - 1) < @NumValues
    end

return

end
go

To:гостьььь
Рекусионный вызов равен всего:len(@NumValues) - 1

To:saint
>... как интересно будет работать функция, если ее вызвать из SP, допустим.
Правельное замечание. Надо подумать
26 ноя 04, 15:45    [1139473]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
 		Как бы его поизящнее "размножить" записи?
26 ноя 04, 16:52    [1139823]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
 		Вариант учитываюший вызов из SP
if object_id('udf_NumberList')> 0 drop function udf_NumberList
go
create function udf_NumberList (@NumValues int, @nestlevel int = 0)
returns @Results table(Value int)
as

begin

declare @Numbers table (Value int)

declare @i int, @NextNumValues int
set @i = 0

while @i < 10
    begin            
        insert into @Numbers
            select @i * power(10, @@nestlevel - @nestlevel -1) 

        set @i = @i + 1 
    end

if len(@NumValues) = 1
    begin
        insert into @Results select Value from @Numbers where Value >= power(0, @@nestlevel - @nestlevel - 1)
        if @@nestlevel- @nestlevel = 1
            delete @Results where Value > @NumValues
    end
else
    begin
        set @NextNumValues =  replicate(1, len(@NumValues) - 1)
        
        insert into @Results
            select t1.Value + t2.Value + power(0, @@nestlevel - @nestlevel - 1) as Value
            from @Numbers t1 cross join udf_NumberList(@NextNumValues, @nestlevel) t2
            where (t1.Value + t2.Value) * power(0, @@nestlevel - @nestlevel - 1) < @NumValues
    end

return

end
go 


-- Variant With SP
if object_id('spTest_udf_NumberList', 'P') > 0 drop procedure spTest_udf_NumberList
go
create procedure spTest_udf_NumberList(@LeftBorder int, @RightBorder int)
as

set nocount on
declare @NumValues int, @nestlevel int
select @NumValues = @RightBorder - @LeftBorder + 1,  @nestlevel = @@nestlevel

select (@LeftBorder - 1) + Value as Number into #Numbers  from udf_NumberList(@NumValues, @nestlevel)

select Number from #Numbers 
go

-- Test spTest_udf_NumberList
exec spTest_udf_NumberList 155, 160
exec spTest_udf_NumberList 150, 2000


-- Variant Without SP
declare @LeftBorder int, @RightBorder int, @NumValues int
set @LeftBorder = 150
set @RightBorder = 2000
set @NumValues = @RightBorder - @LeftBorder + 1

if object_id('tempdb..#Numbers ') > 0 drop table #Numbers 
select (@LeftBorder - 1) + Value as Number into #Numbers  from udf_NumberList(@NumValues, 0)
select * from #Numbers 
go
26 ноя 04, 21:19    [1140614]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
Glory
Member

Откуда:
Сообщений: 104764
 		Вот имхо один и самых быстрых запросов. Ибо оптимизирован для того, чтобы серверу удобнее было производить вычисления. Как ни странно это звучит для при виде выражений в where
declare @a int, @b int, @n int
set @a = 10
set @b = 560
set @n = @b-@a

declare @numbers table(val int)
insert @numbers 
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 

select a1.val+a2.val*16+a3.val*256+a4.val*4096+a5.val*65536+@a
from @numbers a1,@numbers a2,@numbers a3,@numbers a4,@numbers a5 
where a5.val<=@n/65536 
	and a4.val<=(@n-a5.val*65536)/4096 
	and a3.val<=(@n-a5.val*65536-a4.val*4096)
	and a1.val+a2.val*16<=@n-(a3.val*256+a4.val*4096+a5.val*65535)
order by a1.val+a2.val*16+a3.val*256+a4.val*4096+a5.val*65536
(с)Палестинец
29 ноя 04, 10:24    [1142669]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
aleks2
Guest 		Даешь ускорение самого быстрого:
select a1.val | a2.val*16 | a3.val*256 | a4.val*4096 | a5.val*65536 + @a
from @numbers a1,@numbers a2,@numbers a3,@numbers a4,@numbers a5 
where a5.val<=@n/65536 
	and a4.val<=(@n-a5.val*65536)/4096 
	and a3.val<=(@n-a5.val*65536-a4.val*4096)
	and a1.val+a2.val*16<=@n-(a3.val*256+a4.val*4096+a5.val*65535)
order by a1.val | a2.val*16 | a3.val*256 | a4.val*4096 | a5.val*65536

нет предела совершенству...

Вы что, ребяты, всерьез считаете, что деление быстрая операция?
29 ноя 04, 12:18    [1143281]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
Glory
Member

Откуда:
Сообщений: 104764
 		Вы что, ребяты, всерьез считаете, что деление быстрая операция?
Деление на степень 2-ки есть сдвиг байта на один разряд вправо.
29 ноя 04, 12:21    [1143295]     Ответить | Цитировать Сообщить модератору
 Re: Создание таблицы натуральных чисел  [new]
aleks2
Guest 		Ну ладно, вот еще быстрее

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

оригинал _nums_1
CPU 3415
Duration 4763

без деления _nums_2
CPU 2534
Duration 4356

-- оригинал
CREATE PROCEDURE _nums_1
AS
SET NOCOUNT ON 
set rowcount 0

declare @a int, @b int, @n int
set @a = 10
set @b = 560000

set @n = @b-@a

declare @numbers table(val int)
insert @numbers 
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 

select a1.val+a2.val*16+a3.val*256+a4.val*4096+a5.val*65536+@a
from @numbers a1,@numbers a2,@numbers a3,@numbers a4,@numbers a5 
where a5.val<=@n/65536 
	and a4.val<=(@n-a5.val*65536)/4096 
	and a3.val<=(@n-a5.val*65536-a4.val*4096)
	and a1.val+a2.val*16<=@n-(a3.val*256+a4.val*4096+a5.val*65535)
RETURN 
----------------------------------

-- элементарное ускорение
CREATE  PROCEDURE _nums_2
AS
SET NOCOUNT ON 
set rowcount 0

declare @a int, @b int, @n int
set @a = 10
set @b = 560000
set @n = @b-@a

declare @numbers table(val int)
insert @numbers 
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 

declare @numbers1 table(val int)
insert @numbers1 select val*16  FROM @numbers
declare @numbers2 table(val int)
insert @numbers2  select val*256 val FROM @numbers
declare @numbers3 table(val int)
insert @numbers3  select val*4096 val FROM @numbers
declare @numbers4 table(val int)
insert @numbers4  select val*65536 val FROM @numbers 

select a1.val | a2.val | a3.val| a4.val| a5.val + @a
from @numbers a1, @numbers1 a2, @numbers2  a3, @numbers3 a4, @numbers4 a5 
where 
	a5.val<=@n 
	and a4.val|a5.val<=@n
	and a3.val|a4.val|a5.val<=@n
	and a1.val|a2.val|a3.val|a4.val|a5.val<=@n
RETURN
29 ноя 04, 13:44    [1143737]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить