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

Откуда:
Сообщений: 8807
Можно сделать так: создать с таблице столбец с номером счетчика, который считает от 1 до 3 циклически (например, sequence).
Ваша программа как-то узнает, что наступил новый день (раз в сутки по расписанию windows) и запрашивает и запоминает в базе новое значение счетчика. При этом она предварительно удаляет все данные, относящиеся к полученному значению счетчика. Для ускорения удаления к таблице создаем фильтрованные индексы для каждого значения счетчика по столбцу счетчика.
Так получите кольцевой буфер.
3 апр 15, 16:08    [17470399]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
o-o
churupaha
пропущено...

о-о точно не юзер.

ой, а кто же я?


не юзер!
3 апр 15, 16:11    [17470416]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
o-o
Guest
Владислав Колосов
(например, sequence).

ivc-prog
SQL Server Express 2008 R2

Владислав Колосов
Ваша программа как-то узнает, что наступил новый день (раз в сутки по расписанию windows) и запрашивает и запоминает

ivc-prog
Записью я не управляю, пишет стороннее ПО.
3 апр 15, 16:14    [17470439]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
o-o,

А что не так? ПО стороннее, это не важно, хоть потустороннее, а программа своя, которая может и без сиквенса формировать последовательность по расписанию, ибо автор силён в С#. Ибо нет в экпрессе системы расписаний. Это придирки к идее, если разобраться.
3 апр 15, 16:34    [17470598]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
o-o
Guest
Владислав Колосов,
тогда пардон.
ТС в первом посте писал, что вроде делал свой C#-скрипт по удалению "старше 3х дней".
а вроде спрашивает, как по-другому.
мне показалось, вы желаете стороннее ПО переделывать, я все еще в сторону SQL думаю, а не C#.
пятничные заморочки :)
3 апр 15, 16:54    [17470773]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
ivc-prog
Member

Откуда:
Сообщений: 14
Стороннее ПО - это система диспетчеризации техпроцесса, SCADA по-другому. Она имеет функцию записи в удаленную SQL базу данных. Так и сделали, пишет параметры. В каком формате пишет - это ее дело, не могу управлять. Но когда база переполняется она писать не может и появляется много ошибок.

А управлять базой я могу, поэтому и думал, как справится с переполнением базы. Написал в C# консольное приложение, в котором смотрится на колонку time и если в этой колонке время записано древнее чем допустим 3 дня то вся строка удаляется. Таким образом в базе не остается старых данных. Приложение данное хотел запускать каждый день.
3 апр 15, 17:01    [17470820]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
ivc-prog
Member

Откуда:
Сообщений: 14
Предпочтительно было бы сделать удаление старых данных на стороне SQL, мне так кажется надежнее. Хотя, подойдет любое надежное решение.
3 апр 15, 17:09    [17470861]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
ivc-prog
Предпочтительно было бы сделать удаление старых данных на стороне SQL, мне так кажется надежнее. Хотя, подойдет любое надежное решение.


ну так написали же, сделайте триггер на вставку, проверяйте кол-во, и удаляйте 500 или сколько нужно самых старых записей.
3 апр 15, 17:21    [17470912]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
ivc-prog
Предпочтительно было бы сделать удаление старых данных на стороне SQL, мне так кажется надежнее. Хотя, подойдет любое надежное решение.



17470203

+ дубль два

-- тестовая схема
use [master];
go

create database db_2015_01;
go
create database db_2015_02;
go
create database db_2015_03;
go

use db_2015_01;
go

create table t1_2015_01
(
	id int,
	ts datetime not null, 
	lon decimal(10,7), 
	lat decimal(9,7),
	evt char(20),

	constraint pk__t1_2015_01__ts primary key(ts, id),
	constraint chk__t1_2015_01__ts check('20150101' <= ts and ts < '20150201')
);
go

use db_2015_02;
go

create table t1_2015_02
(
	id int,
	ts datetime not null, 
	lon decimal(10,7), 
	lat decimal(9,7),
	evt char(20),

	constraint pk__t1_2015_02__ts primary key(ts, id),
	constraint chk__t1_2015_02__ts check('20150201' <= ts and ts < '20150301')
);
go

use db_2015_03;
go

create table t1_2015_03
(
	id int,
	ts datetime not null, 
	lon decimal(10,7), 
	lat decimal(9,7),
	evt char(20),

	constraint pk__t1_2015_03__ts primary key(ts, id),
	constraint chk__t1_2015_03__ts check('20150301' <= ts and ts < '20150401')
);
go

create database db;
go

use db;
go

create view dbo.t1
as
select * from db_2015_01.dbo.t1_2015_01 union all
select * from db_2015_02.dbo.t1_2015_02 union all
select * from db_2015_03.dbo.t1_2015_03
go


-- вставка размазыется по базам. потенциально возможный общий объем 10 Gb * 32767
use db;
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 l4 t1, l4 t2),
evt(e) as 
(
	select 'START' union all
	select 'STOP' union all
	select 'MOVE' union all
	select 'DANGER' union all
	select 'FLY'
)
insert into dbo.t1(id, ts, lon, lat, evt)
select top(1000000)
	n as id,
	dateadd(minute, rand(checksum(newid())) * 100000, '20150101') as ts,
	-180.0 + rand(checksum(newid())) * 360.0 as lon,
	-90.0 + rand(checksum(newid())) * 180.0 as lat,
	t.e as evt
from
	rt
		outer apply
	(
		select top(1)
			e
		from
			evt
		where
			rt.n = rt.n
		order by
			newid()
	) t;
go


-- выборки тянут только необходимое, при наличии колонки секционирования в предикате
set statistics xml, io on;
go

select *
from
	dbo.t1
where
	ts = '20150102';
go

set statistics xml, io off;
go


-- чистить данные так, например...
alter view dbo.t1
as
/* исключаем секцию */
-- select * from db_2015_01.dbo.t1_2015_01 union all 
select * from db_2015_02.dbo.t1_2015_02 union all
select * from db_2015_03.dbo.t1_2015_03
go

backup database db_2015_01 to disk = '...';
go

drop database db_2015_01;
go

4 апр 15, 11:29    [17473098]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Нужно чтобы новые данные переписали старые, например, появилась новая одна запись, она должна переписать самую старую одну запись и так далее. Как это можно сделать?

а ты как филин: мышки, станьте ёжиками...
4 апр 15, 12:41    [17473227]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
ivc-prog
Спасибо!
А стандартных средств нет для самоочистки базы данных?
Или они есть но только не в версии Express?
Неужели я первый человек, у которого переполняется база, решения только в написании скриптов?



нет стандартных средств, потому что бд немножко не для этого применяются.

есть немного более другие СУБ для этого, называются round robin DBMS...
4 апр 15, 12:45    [17473233]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
MasterZiv
Нужно чтобы новые данные переписали старые, например, появилась новая одна запись, она должна переписать самую старую одну запись и так далее. Как это можно сделать?

а ты как филин: мышки, станьте ёжиками...



ivc-prog
С SQL работаю впервые


ты в следующий раз хотя бы смотрел, кому надо.
4 апр 15, 12:47    [17473237]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
ivc-prog
Member

Откуда:
Сообщений: 14
Всем спасибо!
Сделал следующим образом, запускаю в планировщике Windows каждые 5 минут sqlcmd.exe со скриптом:
DECLARE @NumberOfItems INT
SELECT @NumberOfItems = COUNT(*) FROM [Tag_VfiTag].[dbo].[VfiTagNumHistory]
DECLARE @Limit INT = 16600000
IF @NumberOfItems > @Limit
BEGIN
   /**Превышен лимит в базе данных**/
   DELETE 
   FROM [Tag_VfiTag].[dbo].[VfiTagNumHistory]
   WHERE [time] IN
   (SELECT TOP(100000) time
   FROM [Tag_VfiTag].[dbo].[VfiTagNumHistory]
   ORDER BY [time] ASC)
END

Каждые 5 минут буду смотреть на количество элементов в базе и удалять каждый раз по ~100000 элементов, если лимит превышен.
6 апр 15, 11:11    [17477722]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
>>>SELECT @NumberOfItems = COUNT(*) FROM [Tag_VfiTag].[dbo].[VfiTagNumHistory]

как минимум, заменить на просмотр sys.partitions
6 апр 15, 14:25    [17478970]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Knyazev Alexey
как минимум, заменить на просмотр sys.partitions

Там же приблизительное количество строк.
6 апр 15, 14:54    [17479216]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
virtuOS
Knyazev Alexey
как минимум, заменить на просмотр sys.partitions

Там же приблизительное количество строк.

даже чисто теоретическая погрешность в пару записей - это ничто по сравнению с КАУНТом, да и задача у топикстартера не требует точности
6 апр 15, 14:59    [17479261]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
churupaha
MasterZiv
Нужно чтобы новые данные переписали старые, например, появилась новая одна запись, она должна переписать самую старую одну запись и так далее. Как это можно сделать?

а ты как филин: мышки, станьте ёжиками...



ivc-prog
С SQL работаю впервые


ты в следующий раз хотя бы смотрел, кому надо.


Я посмотрел. И как оно ему помогло ?
6 апр 15, 15:02    [17479288]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
ivc-prog
Всем спасибо!
Сделал следующим образом, запускаю в планировщике Windows каждые 5 минут sqlcmd.exe со скриптом:
DECLARE @NumberOfItems INT
SELECT @NumberOfItems = COUNT(*) FROM [Tag_VfiTag].[dbo].[VfiTagNumHistory]
DECLARE @Limit INT = 16600000
IF @NumberOfItems > @Limit
BEGIN
   /**Превышен лимит в базе данных**/
   DELETE 
   FROM [Tag_VfiTag].[dbo].[VfiTagNumHistory]
   WHERE [time] IN
   (SELECT TOP(100000) time
   FROM [Tag_VfiTag].[dbo].[VfiTagNumHistory]
   ORDER BY [time] ASC)
END

Каждые 5 минут буду смотреть на количество элементов в базе и удалять каждый раз по ~100000 элементов, если лимит превышен.


Я бы лучше бежал в курсоре по записям в порядке возрастания ORDER BY [time] ASC и удалял записи из курсора.
Коммит каждые 1000 записей и в конце.
6 апр 15, 15:03    [17479296]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
MasterZiv
Я посмотрел. И как оно ему помогло ?


ну если человек не может сделать Ctrl+C и следом Ctrl+V и не в силах прочесть о Partitioned View's, то наверное да, не помогло. лисапеды с delete милее сердцу.
6 апр 15, 15:13    [17479368]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
MasterZiv
Я бы лучше бежал в курсоре по записям в порядке возрастания ORDER BY [time] ASC и удалял записи из курсора.
Коммит каждые 1000 записей и в конце.


На одной волне с автором.
6 апр 15, 15:16    [17479379]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
ivc-prog
Member

Откуда:
Сообщений: 14
churupaha
MasterZiv
Я посмотрел. И как оно ему помогло ?


ну если человек не может сделать Ctrl+C и следом Ctrl+V и не в силах прочесть о Partitioned View's, то наверное да, не помогло. лисапеды с delete милее сердцу.


Указанный мною способ работает. Проверил при максимальных количествах одновременной записи. Поломается? Чем он так плох? Ресурсы ест?

Забыл сказать, время в колонке time в миллисекундах. Удаление данных смотря на время в днях - не правильно. Пусть удаляется n "самых старых".
6 апр 15, 15:51    [17479620]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
ivc-prog
Указанный мною способ работает. Проверил при максимальных количествах одновременной записи. Поломается? Чем он так плох? Ресурсы ест?


Так можно, но почитай про lock escalation (когда случилась и когда ей что-то мешает), index fragmentation и т. п.. Глядишь, шаг сделаешь адаптивным (по lock_timeout уменьшаем, иначе увеличиваем, чтобы побольше удалять, так как никому не мешаем...)... Хорошо или плохо, зависит от того как эти данные используются у тебя.



ivc-prog
Забыл сказать, время в колонке time в миллисекундах. Удаление данных смотря на время в днях - не правильно. Пусть удаляется n "самых старых".


В моем способе данные можно придержать, до того момента, когда они совсем ,будут не нужны с точки зрения логики. И исключить ненужные куски данных, а потом решить что с ними сделать просто drop или backup/drop. Причем исключение данных - просто metadata operation...

А по поводу миллисекунд и удаление последних N записей - весело, это кто-то с мензуркой будет вымерять? Что удалилось именно N последних... при твоем не контролируемом способе удаления... Обычно требуется по регламенты держать данные не более M времени, а тут отсебятина какая-то...
6 апр 15, 16:10    [17479759]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
churupaha
Member

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

set nocount on;

set lock_timeout 0;

declare
        -- сколько старых записей надо почистить
	@max_cnt int = 100000;


declare
	@min_rows_per_op int = 1000,	
	@act_rows_per_op int = 1000,
	@total_rows int = 0;

declare
	@rows_per_op int = iif(@max_cnt < @min_rows_per_op, @max_cnt, @min_rows_per_op);

while (@act_rows_per_op > 0 and @total_rows < @max_cnt)
begin

	begin try
		-- lock escalation threshold: 5000 locks
		set @rows_per_op = iif(@rows_per_op > 5000, 5000 - 1, @rows_per_op);

		-- нужен индекс create index idx__t1__ts on dbo.t1(ts);
		delete z
		from
			(select row_number() over(order by ts asc) rn from [dbo].[t1_2015_02]) z
		where
			z.rn <= @rows_per_op;

		set @act_rows_per_op = @@rowcount;
		set @total_rows += @act_rows_per_op;

		set @rows_per_op *= 2;
		set @rows_per_op = iif (@rows_per_op > @max_cnt - @total_rows, @max_cnt - @total_rows, @rows_per_op);

	end try
	begin catch

		if (error_number() not in (1222, 1205))
			throw;

		-- Обработка deadlock'ов и lock_timeout
		waitfor delay '00:00:05';

		set @rows_per_op /= 2;
		set @rows_per_op = iif (@rows_per_op < @min_rows_per_op, @min_rows_per_op, @rows_per_op);

	end catch

end;

6 апр 15, 17:06    [17480093]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Не читал всё г..но которое тут написано, но добавлю чуток своего.

Проверка кол-ва строк должна происходить в момент вставки, поэтому нужен триггер, на вставку(after insert) и в нем проверять сколько записей лишних. и лишнее удалять
т.е.

.... где-то в триггере
select @count_del = count(1)-1000 from Table_1

if(@count_del > 0)
delete top (@count_del) from Table_1 order by time asc
....
7 апр 15, 10:28    [17482200]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическая перезапись старых данных в SQL Server Express 2008 R2  [new]
o-o
Guest
Jaffar
Не читал всё г..но которое тут написано, но добавлю чуток своего.

Проверка кол-ва строк должна происходить в момент вставки, поэтому нужен триггер, на вставку(after insert) и в нем проверять сколько записей лишних. и лишнее удалять
т.е.

.... где-то в триггере
select @count_del = count(1)-1000 from Table_1

if(@count_del > 0)
delete top (@count_del) from Table_1 order by time asc
....

не читал, но осуждаю обзываю?
ссылку давайте на delete top с order by
или картинкой подтвердите успешность выполнения сей конструкции
7 апр 15, 10:39    [17482257]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить