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

Откуда:
Сообщений: 10
Здравствуйте.

Собственно, сабж. MSQL. Есть исходная таблица с полями hash, id, value, date. Хочу заполнить две другие таблицы -

1. counter, value, date (counter - ключ)
2. counter, id, hash, date.

Для заполнения использую следующую процедуру:
1. Беру из исходной таблицы каждую строчку.
2. Проверяю есть ли в 1 табл. такое значение, если да, то обновить значение date, если нет, то вставить в 1 табл. и выполнить вставку во вторую табл. counter, id, hash, date, если таких значений нет, если же есть, то обновить date.
3. Удаляю строчку.

Данная процедура работает со скоростью 40-50 строчек в секунду. В исходную таблицу попадает по 12-15 тыс. строк. Примерное время работы 5-7 минут.

Есть индекса для всех таблиц. Как можно ускорить выполнение?
9 дек 16, 11:18    [19983730]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
divirgent,

/*полючть кличи первой вставки*/
merge (insert)
    output 

/*добавить во вторую*/
insert table 
   select запрос
9 дек 16, 11:28    [19983786]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
Владислав Колосов,

немного непонятно. Можно ли на разобрать на моем примере?
9 дек 16, 12:29    [19984198]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
divirgent
Можно ли на разобрать на моем примере?

Можно, но по правилам форума нужно указать вилку оплаты.

Или же выкладывайте ваш код, который делает то, что описываете.
9 дек 16, 12:38    [19984250]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
divirgent,

читайте справку об output выражении.
9 дек 16, 12:49    [19984330]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
Руслан Дамирович,

вы имеете в виду изначальный код? или уже с использованием merge? Мне не понятно как я буду его применять для таблиц с разным числом столбцом и как получать ключевое значение для вставки в другую таблицу.
9 дек 16, 13:21    [19984595]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
divirgent
Руслан Дамирович,

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

Да, мы хотим изначальную структуру в виде CREATE TABLE со всеми индексами и ключами. И еще мы хотим код процедуры. И все это нужно, чтобы понять, как вам помочь, и в силу доброты и малой загруженности, даже написать вам готовый код.

В противном случае, ответ вам уже дали - читайте на MSDN описание MERGE (вставка и обновление) и OUTPUT (вывод данных в другие таблицы, в том числе во временные) - и думайте, как вам использовать это в своей процедуре... самостоятельно.

:SMILE:
9 дек 16, 13:28    [19984653]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3756
divirgent
Владислав Колосов,

немного непонятно. Можно ли на разобрать на моем примере?


не видно вашего примера
9 дек 16, 13:43    [19984749]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
Руслан Дамирович,

Понял сейчас выложу)
9 дек 16, 15:29    [19985285]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
+
CREATE TABLE [dbo].[Elements](
	[element_counter] [int] IDENTITY(1,1) NOT NULL,
	[element_value] [bigint] NOT NULL,
	[date_reg] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_Elements] PRIMARY KEY CLUSTERED 
(
	[element_counter] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ElementsLinks](
	[element_counter] [bigint] NOT NULL,
	[element_id] [int] NOT NULL,
	[hash] [nvarchar](50) NOT NULL,
	[date_reg] [smalldatetime] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Temp](
	[hash] [varchar](50) NOT NULL,
	[element_id] [int] NOT NULL,
	[element_value] [bigint] NOT NULL,
	[date_reg] [smalldatetime] NOT NULL
) ON [PRIMARY]

GO

CREATE procedure dbo.Import as
declare @elem_cnt bigint,
        @elem_val bigint,
        @elem_id int,
        @date_reg smalldatetime,
        @hash varchar(50)
begin
	while (select top(1) [element_value] from dbo.Temp) > 0
	begin
	
		SELECT top(1) @elem_val = [element_value], 
		       @date_reg = [date_reg],
		       @hash = [hash],
		       @elem_id = [element_id] 
		FROM [dbo].[Temp]
			
		if not exists
		(
			select * from dbo.Elements
			where [element_value] = @elem_val and
			      [date_reg] = @date_reg
		)
			begin
				insert into dbo.Elements
				values (@elem_val, @date_reg)
			end
		else
			begin
				update dbo.Elements
				set date_reg = @date_reg
				where [element_value] = @elem_val
			end
		
		select @elem_cnt = SCOPE_IDENTITY() from dbo.Elements
		
		if not exists
		(
			select * from dbo.ElementsLinks
			where [element_counter] = @elem_cnt and
			      [date_reg] = @date_reg and 
			      [element_id] = @elem_id and
			      [hash] = @hash
		)
			begin
				insert into dbo.ElementsLinks
				values (@elem_cnt, @elem_id, @hash, @date_reg)
			end
		else
		begin
			update dbo.ElementsLinks
				set date_reg = @date_reg
				where [element_counter] = @elem_cnt
				and  [element_id] = @elem_id and
			    [hash] = @hash
		end
		
		delete from dbo.Temp
		where [element_value] = @elem_val and
		      [date_reg] = @date_reg and
		      [hash]  =  @hash and
		      [element_id] = @elem_id
	end
end


Сообщение было отредактировано: 9 дек 16, 15:43
9 дек 16, 15:30    [19985288]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
CREATE NONCLUSTERED INDEX [ind] ON [dbo].[Elements] 
(
	[element_value] ASC,
	[date_reg] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


CREATE CLUSTERED INDEX [ind_clust] ON [dbo].[ElementsLinks] 
(
	[element_counter] ASC,
	[element_id] ASC,
	[hash] ASC,
	[date_reg] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


Сообщение было отредактировано: 9 дек 16, 15:43
9 дек 16, 15:42    [19985336]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
		if not exists
		(
			select * from dbo.Elements
			where [element_value] = @elem_val and
			      [date_reg] = @date_reg
		)
			begin
				insert into dbo.Elements
				values (@elem_val, @date_reg)
			end
		else
			begin
				update dbo.Elements
				set date_reg = @date_reg
				where [element_value] = @elem_val
			end

Уже вот это вызывает сомнения. UPDATE не нужен, потому что по условию в EXISTS, там уже и так лежат эти значения, и операция абсурдна.
9 дек 16, 16:00    [19985444]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Далее вообще фееричный :facepalm:
select @elem_cnt = SCOPE_IDENTITY() from dbo.Elements

Читаем описание на SCOPE_IDENTITY на MSDN, где английским по белому сказано, что функция return values that are inserted into identity columns, то бишь при апдейте, она вернет вам некое значение, не относящееся к апдейту, а последнее вставленное в рамках текущей сессии значение в любое поле identity в любой таблице.
9 дек 16, 16:06    [19985485]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Продолжим разбирать фееричный стейтмент...
select @elem_cnt = SCOPE_IDENTITY() from dbo.Elements

SCOPE_IDENTITY() конечно вернет только 1 значение. Но эта конструкция вызовет присвоение переменной аж столько раз, сколько значений в таблице Elements... Это зачем?
SET @elem_id = SCOPE_IDENTITY()

Если хочется ID только из таблицы Elements, то
SET @elem_id = IDENT_CURRENT( 'dbo.Elements' )
9 дек 16, 16:11    [19985525]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
Руслан Дамирович,

Там ошибка. Проверяется только на наличие такого-то element_value, без даты.
9 дек 16, 16:17    [19985560]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
Руслан Дамирович,

Мда.. Фэйл. Я подумал, что оно возвращает последнее значение element_counter.
9 дек 16, 16:20    [19985574]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
MERGE 
INTO [dbo].[Elements] WITH ( TABLOCKX ) t
USING [dbo].[Temp] s 
ON (
      s.[element_value] = t.[element_value] )
WHEN MATCHED THEN
  UPDATE SET
    t.[date_reg] = s.[date_reg]
WHEN NOT MATCHED BY TARGET THEN
  INSERT (
    [element_value],
    [date_reg]
  )
  VALUES (
    s.[element_value],
    s.[date_reg]
  )
;
MERGE 
INTO [dbo].[ElementsLinks] WITH ( TABLOCKX ) t
USING (
  SELECT
    e.[element_counter],
    s.*
  FROM
    [dbo].[Temp] s
    INNER JOIN [dbo].[Elements] e ON (
          e.[element_value] = s.[element_value] )
) s ON (
      t.[element_counter] = s.[element_counter]
  AND t.[element_id] = s.[element_id]
  AND t.[hash] = s.[hash] )
WHEN MATCHED THEN
  UPDATE SET
    t.[date_reg] = s.[date_reg]
WHEN NOT MATCHED BY TARGET THEN
  INSERT (
    [element_counter],
    [element_id],
    [hash],
    [date_reg]
  )
  VALUES (
    s.[element_counter],
    s.[element_id],
    s.[hash],
    s.[date_reg]
  )
;
9 дек 16, 16:22    [19985586]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
Руслан Дамирович,

приношу свои извинения, не ту версию отправил. Вот последняя рабочая.

CREATE procedure dbo.Import as
declare @elem_cnt bigint,
        @elem_val bigint,
        @elem_id int,
        @date_reg smalldatetime,
        @hash varchar(50)
begin
	while (select top(1) [element_value] from dbo.Temp) > 0
	begin
	
		SELECT top(1) @elem_val = [element_value], 
		       @date_reg = [date_reg],
		       @hash = [hash],
		       @elem_id = [element_id] 
		FROM [dbo].[Temp]
			
		if not exists
		(
			select * from dbo.Elements
			where [element_value] = @elem_val			      
		)
			begin
				insert into dbo.Elements
				values (@elem_val, @date_reg)
				
				select @elem_cnt = SCOPE_IDENTITY() from dbo.Elements
		
				if not exists
				(
					select * from dbo.ElementsLinks
					where [element_counter] = @elem_cnt and
						  [date_reg] = @date_reg and 
						  [element_id] = @elem_id and
						  [hash] = @hash
				)
					begin
						insert into dbo.ElementsLinks
						values (@elem_cnt, @elem_id, @hash, @date_reg)
					end
				else
				begin
					update dbo.ElementsLinks
						set date_reg = @date_reg
						where [element_counter] = @elem_cnt
						and  [element_id] = @elem_id and
						[hash] = @hash
				end
			end
		else
			begin
				update dbo.Elements
				set date_reg = @date_reg
				where [element_value] = @elem_val
			end
				
		delete from dbo.Temp
		where [element_value] = @elem_val and
		      [date_reg] = @date_reg and
		      [hash]  =  @hash and
		      [element_id] = @elem_id
	end
end


Сообщение было отредактировано: 9 дек 16, 17:05
9 дек 16, 16:26    [19985603]     Ответить | Цитировать Сообщить модератору
 Re: Как можно ускорить выполнение процедуры?  [new]
divirgent
Member

Откуда:
Сообщений: 10
Руслан Дамирович,

А, вот оно как! Сначала все все заливается (обновляется) в Elements, потом уже, с использованием объединения двух таблиц, заливается(обновляется) ElementLinks.

Спасибо!

А вы можете пояснить что имелось в виду в этом сообщении:
Владислав Колосов
/*полючть кличи первой вставки*/
merge (insert)
output

/*добавить во вторую*/
insert table
select запрос
9 дек 16, 16:35    [19985646]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить