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

Откуда:
Сообщений: 11
Здравствуйте!
Дана таблица вида:
CREATE TABLE t1 (
	orderid int NOT NULL ,
	groupid bigint NOT NULL ,
	expn int NOT NULL 
)
Данные упорядочены:
orderid, groupid идут строго по возрастанию (1..n) и без разрывов!
Всегда expn<=orderid
orderid  groupid  expn
...
5	1	5
6	1	6
6	2	6
7	1	7
8	1	8
8	2	4
8	3	4
8	4	4
8	5	2
9	1	9
9	2	3
10	1	10
10	2	10
...
Количество записей в таблице более 500 млн.
Производится поиск expn по orderid и groupid - запросы через сайт в интернете.
Есть повторяющиеся expn (иногда до 10 млн. подряд).
Как сократить размер таблицы без серьезного замедления в поиске?
Как построить новую таблицу на основе старой?

ЗЫ: Были предложены варинат хранить только первую запись
из серии повторов в expn и:
1) производить поиск по orderid = @orderid and max(groupid)<=@groupid
2) добавить столбец с колич. повторений repeatcount и искать по orderid=@orderid and groupid<=@groupid and groupid+repeatcount>=@groupid
(проблема с построением)
3) хранить первую и последнюю записи среди повторяющ. и искать через BETWEEN

Заранее спасибо за помощь.
25 май 05, 01:39    [1569297]     Ответить | Цитировать Сообщить модератору
 Re: как правильно сократить размер таблицы?  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
У Вас все записи в таблице уникальны.
Какие на таблице имеются индексы?
Если не все данные Вам нужны - удалите лишние
25 май 05, 06:27    [1569340]     Ответить | Цитировать Сообщить модератору
 Re: как правильно сократить размер таблицы?  [new]
vikont
Member

Откуда:
Сообщений: 11
VladRUS.ca: Да все записи уникальны. Индексов нет.

Для каждого отдельно взятого orderid, значения groupid изменяются
по порядку 1..n, значит для одинаковых expn можно восстановить
любую запись по предыдущей - это позволяет уменьшить количество
записей более чем в 3000 раз.
Вопрос как лучше это сделать?
25 май 05, 11:09    [1569981]     Ответить | Цитировать Сообщить модератору
 Re: как правильно сократить размер таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31623
vikont
VladRUS.ca: Да все записи уникальны. Индексов нет.

Для каждого отдельно взятого orderid, значения groupid изменяются
по порядку 1..n, значит для одинаковых expn можно восстановить
любую запись по предыдущей - это позволяет уменьшить количество
записей более чем в 3000 раз.
Вопрос как лучше это сделать?
Вообще лучьше сделать кластерный ПК на orderid+groupid, и скорость поиска в 500 млн. записей будет как в 500 записей.

Если записи добавляются в порядке возрастания orderid+groupid, то на скорость вставки это не повлияет.

В противном случае, без индекса, даже если уменьшить количество записей более чем в 3000 раз, их останется 150 тыщ, и поиск сканированием будет всё равно небыстрым.
25 май 05, 11:33    [1570047]     Ответить | Цитировать Сообщить модератору
 Re: как правильно сократить размер таблицы?  [new]
vikont
Member

Откуда:
Сообщений: 11
alexeyvg: кластерный ПК это здорово, только наш проект не комерческий
и все, что мы можем себе позволить это 10$ в месяц на хостинг для БД в размере 50Мб. Вот я и пытаюсь сократить обьем с 11Гб на 50Мб :((

И все же как отставить 150 тыс. с максимально быстрым поиском?
25 май 05, 23:28    [1571215]     Ответить | Цитировать Сообщить модератору
 Re: как правильно сократить размер таблицы?  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
Попробуйте так:
-- drop table t1
create table t1 (
	orderid int NOT NULL ,
	min_groupid bigint NOT NULL primary key(orderid, min_groupid),
	max_groupid bigint NOT NULL ,
	expn int NOT NULL 
)
go

-- заполнение таблицы сушествующими данными
insert into t1(orderid, min_groupid, max_groupid, expn)
    select orderid, min(groupid), max(groupid), expn
    from (
        select 5 as orderid, 1 as groupid, 5 as expn union
        select 6,1,6 union
        select 6,2,6 union
        select 7,1,7 union
        select 8,1,8 union
        select 8,2,4 union
        select 8,3,4 union
        select 8,4,4 union
        select 8,5,2 union
        select 9,1,9 union
        select 9,2,3 union
        select 10,1,10 union
        select 10,2,10 ) t
    group by orderid, expn
go

select * from t1
go

-- дальнешая работа с таблицей сводится в основном к обновлению max_groupid 
declare @orderid int
set @orderid = 8

update t1 set max_groupid = a.max_groupid + 1
from t1 a inner join (
    select orderid, max(min_groupid) as min_groupid from t1 where orderid = @orderid group by orderid) b on
    a.orderid = b.orderid and a.min_groupid = b.min_groupid

go

-- полечение результата
declare @orderid int, @groupid int
select @orderid = 8, @groupid = 5

select * from t1 where orderid = @orderid and @groupid between min_groupid and max_groupid
go
26 май 05, 00:02    [1571239]     Ответить | Цитировать Сообщить модератору
 Re: как правильно сократить размер таблицы?  [new]
vikont
Member

Откуда:
Сообщений: 11
VladRUS.ca: Огромное спасибо за помощь!!!

group by orderid, expn не сработало в случаях когда expn повторялось в разрывом, например
...
15	1	15
16	1	16
16	2	4
16	3	4
16	4	4
16	5	8
16	6	8
16	7	8
16	8	8
16	9	8
16	10	4
16	11	4
16	12	4
16	13	4
16	14	2
17	1	17
...
выдало:
...
15	1	1	15
16	1	1	16
16	2	13	4   -- ошибка
16	5	9	8
16	14	14	2
17	1	1	17
...
но в любом деле главное идея
и вот результат:
create table exp1 (
	orderid int not null,
	startgroupid bigint not null primary key(orderid, startgroupid),
	length bigint not null,
	expn int not null
)
GO

declare expn_cur cursor for
select orderid, groupid, expn 
from t1 
order by orderid, groupid

open expn_cur

declare @ord int, @grp bigint, @expn int, 
@lastord int, @lastgrp bigint, @lastexpn int, 
@count_rep bigint

fetch next from expn_cur into @lastord, @lastgrp, @lastexpn
select @count_rep=1

while @@fetch_status = 0
begin
  fetch next from expn_cur into @ord, @grp, @expn
  if @ord=@lastord and @expn=@lastexpn
    select @count_rep=@count_rep+1
  else begin
    insert into exp1 values (@lastord, @lastgrp, @count_rep, @lastexpn)
    select @count_rep=1, @lastord=@ord, @lastgrp=@grp, @lastexpn=@expn
  end
end

close expn_cur
deallocate expn_cur
GO

create function dbo.exponent (@ord int, @grp bigint)
returns int
as
begin
  return (select expn
          from exp1 
          where orderid=@ord and startgroupid<=@grp and @grp<startgroupid+length)
end
GO

-- получение ответа
select 8, 5, dbo.exponent(8, 5)

Еще раз большое спасибо всем за помощь
26 май 05, 02:49    [1571340]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить