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

Откуда:
Сообщений: 79
Коллеги, проблема.
Есть процедура, которая циклом вставляет данные в таблицу MSSQL 2014 Developer. За одну итерацию 12-40к строк.
Всего процедура обрабатывает ~90 млн строк за 48 минут. В таблице для вставки нет индексов, у базы модель восстановления Simple.
В плане запроса ~80% ресурсов приходитcя на этот insert.

Уже делал:
1). Секционирование таблицы, в которую вставляются данные, по годам с параллельным запуском этой процедуры с разными диапазонами дат.
2). Перевод этой таблицы в InMemory.
Помогло слабо.
Что еще можно попробовать?
26 ноя 18, 11:38    [21744646]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ondorsal,

Референсы есть?
26 ноя 18, 11:41    [21744654]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
ondorsal
у базы модель восстановления Simple.
ondorsal
Что еще можно попробовать?
insert с минимальным журналированием.
26 ноя 18, 11:55    [21744684]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
ondorsal
Member

Откуда:
Сообщений: 79
TaPaK, текст процедуры. Reference в ней нет.
ALTER PROCEDURE [dbo].[Load] 

@ss as datetime,
@ss2 as datetime
as

while @ss<=@ss2
begin
WITH CTE AS(SELECT @ss as YMD
       ,T.[ProductID]
      ,T.[SkladId]
      ,T.[DocGroupId]
, Sum(T.InOut)as EndRest
	    FROM [DB].[dbo].[TempFact] as T with(nolock)
  where T.[YMD] <= @ss 
  group by T.[ProductID]
      ,T.[SkladId]
      ,T.[DocGroupId])

insert into DoNotTouch
select L.YMD,L.ProductId,L.SkladId,L.DocGroupId, (L.EndRest-isnull(R.InOut,0)) as BegRest, isnull(R.InOut,0) as InOut,L.EndRest
from(
SELECT @ss as YMD
      ,T.[ProductID]
      ,T.[SkladId]
      ,T.[DocGroupId]
      ,T.EndRest
	    FROM CTE as T with(nolock)
  where T.[YMD]<=@ss) as L left outer join [DB].[dbo].[TempFact] as R with(nolock)
	   on L.ProductId=R.ProductId
	 and L.YMD=R.[YMD]
     and L.SkladId=R.SkladId 
     and L.DocGroupId=R.DocGroupId
	 where abs(isnull(R.InOut,0))>0 

union all

select L.YMD,L.ProductId,L.SkladId,570060 as DocGroupId,
sum((L.EndRest-isnull(R.InOut,0))) as BegRest, Sum(isnull(R.InOut,0)) as InOut, sum(L.EndRest) as EndRest
from(
SELECT @ss as YMD
       ,T.[ProductID]
      ,T.[SkladId]
      ,T.[DocGroupId]
      ,T.EndRest
	    FROM CTE as T with(nolock)
  where T.[YMD]<=@ss) as L left outer join [DB].[dbo].[TempFact] as R with(nolock)
	   on L.ProductId=R.ProductId
	 and L.YMD=R.[YMD]
     and L.SkladId=R.SkladId 
     and L.DocGroupId=R.DocGroupId
	 where isnull(R.InOut,0)=0  and  (abs(isnull(R.InOut,0)) + abs(L.EndRest))>0
	 group by L.YMD,L.ProductId,L.SkladId
having abs(sum(isnull(R.InOut,0)))+abs(sum(L.EndRest))>0

option (maxdop 0)
  set @ss=@ss+1
  end	
  
GO
26 ноя 18, 12:07    [21744710]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4734
ondorsal
Коллеги, проблема.
Есть процедура, которая циклом вставляет данные в таблицу MSSQL 2014 Developer. За одну итерацию 12-40к строк.
Всего процедура обрабатывает ~90 млн строк за 48 минут. В таблице для вставки нет индексов, у базы модель восстановления Simple.
В плане запроса ~80% ресурсов приходитcя на этот insert.

Уже делал:
1). Секционирование таблицы, в которую вставляются данные, по годам с параллельным запуском этой процедуры с разными диапазонами дат.
2). Перевод этой таблицы в InMemory.
Помогло слабо.
Что еще можно попробовать?



Загружайте в InMemory несколькими параллельными потоками. На одном потоке выигрыша не увидите.
26 ноя 18, 12:13    [21744719]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ondorsal,

автор
текст процедуры

действительно, дело во вставке...
26 ноя 18, 12:17    [21744731]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Попробуйте что-то такое:

ALTER PROCEDURE [dbo].[Load]
(
      @ss AS DATETIME
    , @ss2 AS DATETIME
)
AS BEGIN

    WHILE @ss <= @ss2 BEGIN
        
        IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
            DROP TABLE #temp

        SELECT @ss AS YMD
             , T.[ProductID]
             , T.[SkladId]
             , T.[DocGroupId]
             , SUM(T.InOut) AS EndRest
        INTO #temp
        FROM [DB].[dbo].[TempFact] AS T WITH (NOLOCK)
        WHERE T.[YMD] <= @ss
        GROUP BY T.[ProductID]
               , T.[SkladId]
               , T.[DocGroupId]

        IF OBJECT_ID('tempdb.dbo.#temp2') IS NOT NULL
            DROP TABLE #temp2

        SELECT R.ProductId
             , R.[YMD]
             , R.SkladId
             , R.DocGroupId
             , R.InOut
        INTO #temp2
        FROM [DB].[dbo].[TempFact] AS R WITH (NOLOCK)
        WHERE R.[YMD] = @ss

        INSERT INTO DoNotTouch
        SELECT L.YMD
             , L.ProductId
             , L.SkladId
             , L.DocGroupId
             , (L.EndRest - ISNULL(R.InOut, 0)) AS BegRest
             , ISNULL(R.InOut, 0) AS InOut
             , L.EndRest
        FROM #temp AS L
        LEFT JOIN #temp2 ON L.ProductId = R.ProductId AND L.SkladId = R.SkladId AND L.DocGroupId = R.DocGroupId
        WHERE ABS(ISNULL(R.InOut, 0)) > 0

        UNION ALL

        SELECT L.YMD
             , L.ProductId
             , L.SkladId
             , 570060 AS DocGroupId
             , SUM((L.EndRest - ISNULL(R.InOut, 0))) AS BegRest
             , SUM(ISNULL(R.InOut, 0)) AS InOut
             , SUM(L.EndRest) AS EndRest
        FROM #temp AS L
        LEFT JOIN #temp2 ON L.ProductId = R.ProductId AND L.SkladId = R.SkladId AND L.DocGroupId = R.DocGroupId
        WHERE ISNULL(R.InOut, 0) = 0
            AND (ABS(ISNULL(R.InOut, 0)) + ABS(L.EndRest)) > 0
        GROUP BY L.YMD
               , L.ProductId
               , L.SkladId
        HAVING ABS(SUM(ISNULL(R.InOut, 0))) + ABS(SUM(L.EndRest)) > 0
        OPTION (MAXDOP 0)

        SET @ss = @ss + 1

    END

END

Плюс вечный вопрос: какие индексы на той таблице из которой вы данные выгребаете? По хорошему должен быть индекс по полю YMD на первом месте и остальное как часть кластерного индекса либо в INCLUDED
26 ноя 18, 13:34    [21744904]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
ondorsal
Member

Откуда:
Сообщений: 79
AlanDenton, индексы такие
CREATE CLUSTERED INDEX [IX] ON [dbo].[TempFact]
(
	[ProductID] ASC,
	[YMD] ASC,
	[SkladId] ASC,
	[DocGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED COLUMNSTORE INDEX [IXCOL] ON [dbo].[TempFact]
(
	[InOut]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO
26 ноя 18, 14:23    [21744999]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
dvim
Member

Откуда: Санкт Петербург
Сообщений: 679
ondorsal,
Я бы шел в сторону bcp или Tabled параметров.
Быстрее этого в природе ничего нет.
26 ноя 18, 14:29    [21745008]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
[ProductID] ASC,
	[YMD] ASC,

местами поменяйте и радуйтесь) ибо предикейт пушдаун работать не будет + почитайте за TraceFlag 610 (хотя мне кажется что проблема у вас как раз из-за лишних IndexScan-ов на TempFact)
26 ноя 18, 14:30    [21745011]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение insert в таблицу  [new]
Androgen1985
Member

Откуда:
Сообщений: 55
dvim
ondorsal,
Я бы шел в сторону bcp или Tabled параметров.
Быстрее этого в природе ничего нет.


А bcp может работать с таблицами?
27 ноя 18, 07:43    [21745656]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить