Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Разбивка БД по годам / 16 сообщений из 16, страница 1 из 1
19.06.2007, 10:12
    #34603719
karafuto
karafuto
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
Добрый день. Есть такая проблема.
При проектировании БД (MS SQL 2000) не учли фактор разрастания БД.
MDF стал занимать больше 2 Гиг. Запросы стали выполняться очень медленно.
Старые данные нельзя бэкапнуть и удалить - они могут понадобиться в любой момент(такова задача). Также нет возможности ускорить железо.
Ищу способ поудачнее разбить базу по годам. Вроде бы очевидный способ - сделать отдельные БД для каждого года, но тогда придётся править много клиентского софта, да и данные из двух(или более) разных БД получать напряжно.
Знатоки, поделитесь опытом. Наверняка есть какие-то шаблоны - задача по сути типовая.
Может посоветуете, где почитать на эту тему.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 10:35
    #34603806
Нахлобуч
Нахлобуч
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
Издеваетесь что ли? Разбивать базу размером в 2Гб -- это сильно. Всяко проще запросы пооптимизировать. Индексы-то нужные есть?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 11:12
    #34603993
karafuto
karafuto
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
2Нахлобуч
2 Гиг за год, а дальше - больше будет
Оптимизацией занимаемся, но не особенно помогает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 11:51
    #34604191
softwarer
softwarer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
karafutoMDF стал занимать больше 2 Гиг. Запросы стали выполняться очень медленно.
Также нет возможности ускорить железо.
Суммарно эти фразы создают не очень вероятную картину. Я могу представить себе пару вариантов, когда действительно нельзя, но.....

karafutoИщу способ поудачнее разбить базу по годам. Вроде бы очевидный способ - сделать отдельные БД для каждого года, но тогда придётся править много клиентского софта, да и данные из двух(или более) разных БД получать напряжно.
Если Вы собираетесь именно разбивать, то "много править" и "напряжно" никуда не денется во-первых, и далеко не факт, что поможет - во-вторых.

karafutoЗнатоки, поделитесь опытом. Наверняка есть какие-то шаблоны - задача по сути типовая.
Типовой ответ - желание разбить (логически+физически) объекты чаще всего неоправданно. Только физическое разбиение (партиционирование) - бывает полезно, но обычно при данных на пару порядков объемнее.

Технически можно попробовать сохранить логическую модель, обновив или внедрив слой абстракции - скажем, разбить таблицу T на таблицы T1, T2, ... T5, а также сделав представление T, объединяющее данные этих таблиц. Однако, обычно это не лучшим образом сказывается на скорости.

karafutoМожет посоветуете, где почитать на эту тему.
Думаю, Вам таки стоит:

1. Описать свою ситуацию с железом - будет чертовски глупо, если на переделку софта уйдет больше средств, чем на апгрейд железа

2. Написать в форум MSSQL просьбу подсказать по оптимизации ваших запросов

И только если в ответе на второй пункт выползет необходимость разбиения - там же поднять эту тему.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 12:50
    #34604474
Naf
Naf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
Размер базы вашей не велик, прямо скажу. Оптимизация по скорости, это написание "правильных" запросов и использование индексов, там где они нужны.
А по поводу "разбиения по годам", в этом есть смысл. Только при этом оптимизация по скорости будет в противовес размеру базы. Грубо говоря принцип такой: не хотим долго считать - храним излишние данные.
Например, для определения остатка на складе можно пробежаться запросом по всей таблице движений получаю расход и приход. А можно считать данные, хранящиеся на конец периода (года, месяца) и начать бежать оттуда
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 15:18
    #34605143
locky
locky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
Как вариант "из пушки по воробьям" - перейти на 2005 и заюзать "всю его
мощу" - включая партиционирование.

Как мини-вариант - сделать мини-бэкап базы с минимумом данных, написать
список "типовых вызовов" и сказать мне - где это всё лежит :)
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 15:44
    #34605253
karafuto
karafuto
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
locky
Как мини-вариант - сделать мини-бэкап базы с минимумом данных, написать
список "типовых вызовов" и сказать мне - где это всё лежит :)


А ето Вам зачем ;-)) ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 15:47
    #34605266
locky
locky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
karafuto wrote:
> А ето Вам зачем ;-)) ?
Посижу, поковыряюсь... Скушно.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 15:56
    #34605309
karafuto
karafuto
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
locky
Посижу, поковыряюсь... Скушно.

Спасибо за заботу. Боюсь не получится.Но за совет про 2005 - спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 16:01
    #34605327
locky
locky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
karafuto wrote:
> Спасибо за заботу. Боюсь не получится.Но за совет про 2005 - спасибо.
Не забудьте только про ремарку - "из пушки по воробьям" :)
А так - в профильный форум с "тяжелыми запросами".
Не забудьте только почитать "правила оформления постов" - дабы облЕгчить
жизнь людям, и дабы не послали читать "правила оформления постов".

Также можно посмотреть Read80Trace с сайта MS
http://support.microsoft.com/kb/887057

Description of the SQL Server Performance Analysis Utilities Read80Trace
and OSTRESS
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 18:59
    #34606004
PVP
PVP
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
В общем то вопрос актуальный. Если отбросить шутки в сторону, то наверное задумываться над объемом базы, точнее над количеством записей в таблицах, приходится многим. У нас этот вопрос также стал уже остро. Пока в планах разделить таблицу операций на две части - оперативную и архивную. На архивную можно нацеплять кучу индексов для различных целей выборки - т.к. insert и update по ней практически не будет проходить, то это не повлияет на производительность (перенос в архив не считается).

softwarerТолько физическое разбиение (партиционирование) - бывает полезно, но обычно при данных на пару порядков объемнее. Может Вы в курсе, можно ли одну таблицу в SQL 2000 разбить на несколько физических частей? Смысл - транспортировка, архивы, восстановление по частям. И обязателен ли при этом метод full recovery?

softwarerТехнически можно попробовать сохранить логическую модель, обновив или внедрив слой абстракции - скажем, разбить таблицу T на таблицы T1, T2, ... T5, а также сделав представление T, объединяющее данные этих таблиц. Однако, обычно это не лучшим образом сказывается на скорости.. Если разбивка некоторой большой таблицы осуществляется по времени, а параметром вермени является некоторое поле таблицы, то объединение данных требутся по вертикали. При модификации запроса на выборку данных "в лоб" написал бы вместо одного запроса несколько ченез Union. Интересно, как сделать в таком случае представление?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 19:39
    #34606062
softwarer
softwarer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
PVPУ нас этот вопрос также стал уже остро.
Переходите на Oracle ;-)

PVPНа архивную можно нацеплять кучу индексов для различных целей выборки ....
Может, стоит сразу думать о перетаскивании данных в хранилище с аналитикой?

PVP softwarerТолько физическое разбиение (партиционирование) - бывает полезно, но обычно при данных на пару порядков объемнее. Может Вы в курсе, можно ли одну таблицу в SQL 2000 разбить на несколько физических частей? Смысл - транспортировка, архивы, восстановление по частям. И обязателен ли при этом метод full recovery?
Не буду врать, лучше спросить у людей "оттуда".

PVPПри модификации запроса на выборку данных "в лоб" написал бы вместо одного запроса несколько ченез Union. Интересно, как сделать в таком случае представление?
А что есть представление, как не запрос "вместо"? :) Тут основной интерес - чтобы представление не трогало всех таблиц, если запрос идет только к одной. То есть что-то типа

Код: plaintext
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.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
Connected to Oracle Database 10g Enterprise Edition Release  10 . 1 . 0 . 5 . 0  
Connected as test

SQL> create table t1 (id integer, data varchar2( 100 ));

Table created

SQL> create table t2 (id integer, data varchar2( 100 ));

Table created

SQL> alter table t1 add constraint t1_pk primary key (id);

Table altered

SQL> alter table t2 add constraint t2_pk primary key (id);

Table altered

SQL> create view t as
   2   select /*+ index (t1 t1_pk)*/ '1' n#, t1.* from t1
   3   union all
   4   select /*+ index (t2 t2_pk)*/ '2' n#, t2.* from t2;

View created

SQL> insert into t1 select rownum, 'abcde' from dual connect by level <=  1000 ;

 1000  rows inserted

SQL> insert into t2 select rownum, 'abcde' from dual connect by level <=  1000000 ;

 1000000  rows inserted

SQL> exec dbms_stats.gather_schema_stats (ownname => user);

PL/SQL procedure successfully completed

SQL> select count(*) from t where n# = '1';

  COUNT(*)
----------
       1000 

Executed in  0  seconds

SQL> select count(*) from t where n# = '2';

  COUNT(*)
----------
    1000000 

Executed in  0 , 172  seconds

SQL> select count(*) from t where n# = '1';

  COUNT(*)
----------
       1000 

Executed in  0 , 015  seconds

SQL> select count(*) from t where n# = '2';

  COUNT(*)
----------
    1000000 

Executed in  0 , 172  seconds

SQL> select count(*) from t where n# = '1';

  COUNT(*)
----------
       1000 

Executed in  0  seconds

SQL> select count(*) from t where n# = '2';

  COUNT(*)
----------
    1000000 

Executed in  0 , 188  seconds

SQL> explain plan for select * from t where n# = '1' ;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:  1656609947 
--------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT               |       |   1001  |  68068  |      6    ( 0 )|  00 
|    1  |  VIEW                          | T     |   1001  |  68068  |      6    ( 0 )|  00 
|    2  |   UNION-ALL                    |       |       |       |            |
|    3  |    TABLE ACCESS BY INDEX ROWID | T1    |   1000  |   9000  |      6    ( 0 )|  00 
|    4  |     INDEX FULL SCAN            | T1_PK |   1000  |       |      3    ( 0 )|  00 
|*   5  |    FILTER                      |       |       |       |            |
|    6  |     TABLE ACCESS BY INDEX ROWID| T2    |  1000K|  9772K|   4108    ( 1 )|  00 
|    7  |      INDEX FULL SCAN           | T2_PK |  1000K|       |   1894    ( 1 )|  00 
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    5  - filter('2'='1')

 19  rows selected

SQL> explain plan for select * from t where n# = '2' ;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:  3390822454 
--------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT               |       |  1000K|    64M|   4108    ( 1 )|  00 
|    1  |  VIEW                          | T     |  1000K|    64M|   4108    ( 1 )|  00 
|    2  |   UNION-ALL                    |       |       |       |            |
|*   3  |    FILTER                      |       |       |       |            |
|    4  |     TABLE ACCESS BY INDEX ROWID| T1    |   1000  |   9000  |      6    ( 0 )|  00 
|    5  |      INDEX FULL SCAN           | T1_PK |   1000  |       |      3    ( 0 )|  00 
|    6  |    TABLE ACCESS BY INDEX ROWID | T2    |  1000K|  9772K|   4108    ( 1 )|  00 
|    7  |     INDEX FULL SCAN            | T2_PK |  1000K|       |   1894    ( 1 )|  00 
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    3  - filter('1'='2')

 19  rows selected
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 21:10
    #34606180
locky
locky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
softwarer wrote:
Для SQL2k5 - тоже самое, т.е. идет обращение только к одной таблице.
К сожалению, не умю смотреть в оракле
что будет для примерно такого запроса?
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
declare
  s varchar2( 128 );
  i integer;
begin
  s := '1';
  select count(*) into i from t where n# = s ;
end;

Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 21:12
    #34606184
locky
locky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
В SQL2k5 получаем

Код: plaintext
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.
30.
31.
32.
33.
34.
35.
StmtText
-----------------------------------------
select count(*) from t where n# = '2'

( 1  row(s) affected)

StmtText
----------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1014],0)))
       |--Stream Aggregate(DEFINE:([Expr1014]=Count(*)))
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t2].[t2_pk]))

( 3  row(s) affected)

StmtText
----------------------------------------

declare @s varchar( 512 )
set @s = '1'

select count(*) from t where n# = @s

( 2  row(s) affected)

StmtText
------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1014],0)))
       |--Stream Aggregate(DEFINE:([Expr1014]=Count(*)))
            |--Concatenation
                 |--Filter(WHERE:(STARTUP EXPR('1'=[@s])))
                 |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t1].[t1_pk]))
                 |--Filter(WHERE:(STARTUP EXPR('2'=[@s])))
                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t2].[t2_pk]))

( 7  row(s) affected)

т.е. при использовании переменной - план куда хуже.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2007, 23:34
    #34606288
softwarer
softwarer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
lockyВ SQL2k5 получаем
.....
т.е. при использовании переменной - план куда хуже.
Глядя на этот план, я бы не сказал, что он наверняка хуже. Это зависит от того, как именно он будет выполняться - отсечет ли FILTER ненужную ветку или таки "сначала выполнит, потом отсечет".

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

Если смотреть план оторванно, сам по себе, он будет в целом таким же, как у меня выше - только с двумя выражениями FILTER, поскольку сервер не сможет раскрыть одно из них в тождественное true.

Вычислится он также нормально. В рантайме одно из условий filter даст true, другое false, соответственно будет сделан только один full scan (я не ткну пальцем в соответствующую цитату из документации, но проверил по времени выполнения при том и другом значении переменной).

Вообще, интересная тема, всплывающая не в этом случае, но по соседству - так называемая bind variable peeking; оптимизатор может "подсмотреть" значение из переменной и строить план под него. Но это отдельный большой рассказ, к которому я не очень готов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2007, 12:23
    #34607306
locky
locky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Разбивка БД по годам
softwarer wrote:
> Вообще, интересная тема, всплывающая не в этом случае, но по соседству -
> так называемая bind variable peeking; оптимизатор может "подсмотреть"
> значение из переменной и строить план под него. Но это отдельный большой
> рассказ, к которому я не очень готов.
В 2005 появился новых хинт для стейтмента, recompile, который позволяет
перекомпилировать конкретный стейтмент С УЧЕТОМ актуальных значений
переменных и содержимого временных таблиц/table vars. Достаточно
забавная штука - сочетает в себе прелести как динамического так и
статического SQL - без прочих их недостатков :)
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Разбивка БД по годам / 16 сообщений из 16, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 392ms
Закрыть
start [/forum/topic.php?fid=32&tablet=1&tid=1544443]:
 0ms
get settings:
 10ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 43ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 59ms
get tp. blocked users:
 1ms
others: 244ms
total:  392ms
x
x
Закрыть


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