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

Откуда: Москва
Сообщений: 78
Доброго всем дня!
Пожалуйста помогите оптимизировать запрос
Есть в базе 3 большие таблицы, задача - отсечь старые неактуальные данные в третью базу, забэкапить ее и положить на полку
Разработчик программы предлагает в документации такой скрипт для удаления неактуальных данных
declare @NumDays int
/**********************************************************/
/*установка  числа дней от текущей даты до даты, по которую будут удаляться записи */
set @NumDays = 100
/**********************************************************/
alter table KRN_SYS_TRACE_LOG DISABLE TRIGGER ALL
alter table KRN_SYS_TRACE DISABLE TRIGGER ALL
alter table KRN_SYS_DELETE_TRACE DISABLE TRIGGER ALL

delete from KRN_SYS_TRACE_LOG where DateDiff(d,DATE_CHANGE,GetDate())>= @NumDays
delete from KRN_SYS_TRACE where DateDiff(d,DATE_CHANGE,GetDate()) >= @NumDays
delete from KRN_SYS_DELETE_TRACE where DateDiff(d,DATE_DELETE,GetDate())>=@NumDays

alter table KRN_SYS_TRACE_LOG ENABLE TRIGGER ALL
alter table KRN_SYS_TRACE ENABLE TRIGGER ALL
alter table KRN_SYS_DELETE_TRACE ENABLE TRIGGER ALL


Чтобы данные все же оставить на всякий случай, добавил перед 'delete'
INSERT INTO KRN_SYS.dbo.KRN_SYS_TRACE_LOG select * from KRN_SYS_TRACE_LOG
where DateDiff(d,DATE_CHANGE,GetDate())>= @NumDays
INSERT INTO KRN_SYS.dbo.KRN_SYS_TRACE select * from KRN_SYS_TRACE
where DateDiff(d,DATE_CHANGE,GetDate())>= @NumDays
INSERT INTO KRN_SYS_DELETE_TRACE select * from KRN_SYS.dbo.KRN_SYS_DELETE_TRACE
where DateDiff(d,DATE_DELETE,GetDate())>= @NumDays


Если все это запустить, висит это больше суток
для справки таблица KRN_SYS_TRACE_LOG содержит 5181546 строк и занимает 8352 Мб
10 июл 14, 10:26    [16284994]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Tdmitry
where DateDiff(d,DATE_CHANGE,GetDate())>= @NumDays

высчитайте сразу @NumDays как дату и сравнивайте.. без-ф-ции
10 июл 14, 10:28    [16285006]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Maxx
Tdmitry
where DateDiff(d,DATE_CHANGE,GetDate())>= @NumDays


высчитайте сразу @NumDays как дату и сравнивайте.. без-ф-ции
и индекс на дату нужен
10 июл 14, 10:30    [16285010]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
iap
и индекс на дату нужен

ето уже след вапрос
10 июл 14, 10:36    [16285046]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Maxx
iap
и индекс на дату нужен

ето уже след вапрос
Иначе можно и DATEDIFF() ограничивать!
10 июл 14, 10:47    [16285083]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Tdmitry
Member

Откуда: Москва
Сообщений: 78
iap, Maxx, спасибо! Переменную переделал, индексы на даты есть
Ограничил 1 месяцем, запрос выполнился за 15 минут. Если это умножить примерно на 30 мес, получится 7.5 часов
Есть ли еще способы ускорить процесс?
Подскажите, ускоряет ли процесс разбивка на периоды поменьше?
10 июл 14, 11:30    [16285284]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
Tdmitry
Ограничил 1 месяцем, запрос выполнился за 15 минут. Если это умножить примерно на 30 мес, получится 7.5 часов
Есть ли еще способы ускорить процесс?
Сколько данных выбирается?
Покажите актуальный запрос, параметры выполнения (чтения, записи, ЦПУ и т.д.), и его план.
Покажите таблицу (скрипт создания).
10 июл 14, 11:39    [16285338]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Tdmitry
iap, Maxx, спасибо! Переменную переделал, индексы на даты есть
Ограничил 1 месяцем, запрос выполнился за 15 минут. Если это умножить примерно на 30 мес, получится 7.5 часов
Есть ли еще способы ускорить процесс?
Подскажите, ускоряет ли процесс разбивка на периоды поменьше?


Ускоряет процесс анализ плана, а не случайные изменения параметров\текста запроса.
10 июл 14, 11:54    [16285463]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8584
8Гб - не так много. Чего-то Вы не договариваете.
10 июл 14, 11:57    [16285493]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
declare @DateTimeX datetime = dateadd( day, -@NumDays, GetDate());

delete 
from KRN_SYS_TRACE_LOG 
output * into KRN_SYS.dbo.KRN_SYS_TRACE_LOG
where DATE_CHANGE >= @DateTimeX;
10 июл 14, 12:06    [16285583]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Tdmitry
Member

Откуда: Москва
Сообщений: 78
alexeyvg,
declare 
@NumDays datetime,
@MaxDays datetime
set @NumDays = '2014-01-04 00:00:00.000'
set @MaxDays = '2014-01-03 00:00:00.000'

alter table KRN_SYS_TRACE_LOG DISABLE TRIGGER ALL
alter table KRN_SYS_TRACE DISABLE TRIGGER ALL
alter table KRN_SYS_DELETE_TRACE DISABLE TRIGGER ALL

WHILE @NumDays>'2014-01-01' 
     BEGIN 

INSERT INTO KRN_SYS.dbo.KRN_SYS_TRACE_LOG select * from KRN_SYS_TRACE_LOG
where DATE_CHANGE<@NumDays
and DATE_CHANGE>=@MaxDays

delete from KRN_SYS_TRACE_LOG 
where DATE_CHANGE<@NumDays
and DATE_CHANGE>=@MaxDays

print ('Архивирование KRN_SYS_TRACE_LOG за период '+CAST(@Numdays as varchar(100))+'-'+CAST(@Maxdays as varchar(100))+' выполнено')

INSERT INTO KRN_SYS.dbo.KRN_SYS_TRACE select * from KRN_SYS_TRACE
where DATE_CHANGE<@NumDays
and DATE_CHANGE>=@MaxDays

delete from KRN_SYS_TRACE 
where DATE_CHANGE<@NumDays
and DATE_CHANGE>=@MaxDays

print ('Архивирование KRN_SYS_TRACE за период '+CAST(@Numdays as varchar(100))+'-'+CAST(@Maxdays as varchar(100))+' выполнено')

INSERT INTO KRN_SYS_DELETE_TRACE select * from KRN_SYS.dbo.KRN_SYS_DELETE_TRACE
where DATE_DELETE<@NumDays
and DATE_DELETE>=@MaxDays

delete from KRN_SYS_DELETE_TRACE 
where DATE_DELETE<@NumDays
and DATE_DELETE>=@MaxDays

print ('Архивирование KRN_SYS_DELETE_TRACE за период '+CAST(@Numdays as varchar(100))+'-'+ CAST(@Maxdays as varchar(100))+' выполнено')

set @NumDays=DATEADD(MONTH,-1,@NumDays)
set @MaxDays=DATEADD(MONTH,-1,@MaxDays)
DBCC SHRINKFILE (N'MEDIALOG_Log' , 0, TRUNCATEONLY)
END

alter table KRN_SYS_TRACE_LOG ENABLE TRIGGER ALL
alter table KRN_SYS_TRACE ENABLE TRIGGER ALL
alter table KRN_SYS_DELETE_TRACE ENABLE TRIGGER ALL


Скрипт создания KRN_SYS_TRACE_LOG
/****** Object:  Table [dbo].[KRN_SYS_TRACE_LOG]    Script Date: 07/10/2014 11:58:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[KRN_SYS_TRACE_LOG](
	[TABLE_NAME] [varchar](30) NOT NULL,
	[REC_ID] [int] NOT NULL,
	[USER_ID] [int] NOT NULL,
	[DATE_CHANGE] [datetime] NOT NULL,
	[ACTION] [varchar](1) NOT NULL,
	[LOG] [text] NOT NULL,
	[LOG_DATA] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[KRN_SYS_TRACE_LOG] ADD [HOST_NAME] [varchar](100) NULL
ALTER TABLE [dbo].[KRN_SYS_TRACE_LOG] ADD [EXTERNAL_USER_ID] [int] NULL
ALTER TABLE [dbo].[KRN_SYS_TRACE_LOG] ADD [KRN_GUID] [varchar](36) NULL
/****** Object:  Index [PK__KRN_SYS_TRACE_LO__43D6DE8E]    Script Date: 07/10/2014 11:58:12 ******/
ALTER TABLE [dbo].[KRN_SYS_TRACE_LOG] ADD PRIMARY KEY CLUSTERED 
(
	[TABLE_NAME] ASC,
	[REC_ID] ASC,
	[USER_ID] ASC,
	[DATE_CHANGE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[KRN_SYS_TRACE_LOG] ADD  CONSTRAINT [DF__KRN_SYS_T__USER___6D6D25A7]  DEFAULT (user_id()) FOR [USER_ID]
GO

ALTER TABLE [dbo].[KRN_SYS_TRACE_LOG] ADD  CONSTRAINT [DF__KRN_SYS_T__DATE___6E6149E0]  DEFAULT (getdate()) FOR [DATE_CHANGE]
GO

ALTER TABLE [dbo].[KRN_SYS_TRACE_LOG] ADD  DEFAULT (host_name()) FOR [HOST_NAME]
GO


План выполнения не могу сохранить в формате sqlplan, вылетает ssms. Читабелен ли xml?

К сообщению приложен файл (План выполнения.rar - 8Kb) cкачать
10 июл 14, 12:20    [16285723]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
и где обещаные индексы то ?
а сие DBCC SHRINKFILE (N'MEDIALOG_Log' , 0, TRUNCATEONLY)
задлянафига ?
10 июл 14, 12:31    [16285829]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Tdmitry
Member

Откуда: Москва
Сообщений: 78
Maxx, индекс добавил, в обозревателе объектов индексы по дате есть. Не знаю, почему в скрипте его нет... Попробую конечно удалить, пересоздать.
шринк, чтоб не рос файл, а то 20 гб стал, а места на диске маловато
10 июл 14, 13:37    [16286405]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Tdmitry
а места на диске маловато
Может, в этом всё дело?
На каком диске-то?
А там, где tempdb живёт, много свободного места?
10 июл 14, 13:58    [16286627]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Tdmitry,

Я обычно подобные вещи делаю подобным образом (может поможет):

while 1=1
begin
	delete top(4000) from KRN_SYS_TRACE_LOG
	output deleted.* into KRN_SYS.dbo.KRN_SYS_TRACE_LOG
	where DATE_CHANCE <= dateadd(dd,-@num,getdate())

	if @@rowcount = 0 break
end
10 июл 14, 17:00    [16288252]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить