I need to move data from one database to another, and since I don't have SSIS I'm doing this ETL with T-SQL scripts.
One of the source tables contains invoice details, and features a column that contains the number of units invoiced per size (it's clothing); as I transfer the data over to my database, I'm normalizing this information. The query works fine, the execution plan looks exactly as I expected, doesn't recommend adding any indexes, ...but it takes about 10 minutes to process.
The source table Staging.dbo.[SourceTable]
contains about 780K rows; the query inserts 1.5M rows into the destination table [DestinationDatabase].dbo.InvoiceDetailSizes
.
The query is scheduled to run daily, as part of an overnight process - the 10 minutes don't really matter, but still I want to be sure everything is as efficient as it can be.
with cteSizedInvoices (
InvoiceNumber, InvoiceLine, SizeRangeCode, UnitsPerSize
)
as (select
src.f2,
cast(src.f5 as int),
src.f23,
src.f19
from Staging.dbo.[SourceTable] src
where src.f1 = '01'
)
--insert into [DestinationDatabase].dbo.InvoiceDetailSizes (InvoiceDetailId, SizeId, Units, DateInserted)
select
detail.Id InvoiceDetailId,
sz.Id SizeId,
buckets.Units,
getdate()
from
cteSizedInvoices src
inner join [DestinationDatabase].dbo.InvoiceHeaders header on src.InvoiceNumber = header.Number
inner join [DestinationDatabase].dbo.InvoiceDetails detail on src.InvoiceLine = detail.LineNumber
and detail.InvoiceHeaderId = header.Id
inner join [DestinationDatabase].dbo.SizeRanges ranges on src.SizeRangeCode = ranges.Code
cross apply (select id SizeIndex, cast(item as int) Units from [DestinationDatabase].dbo.BucketString(src.UnitsPerSize, 5, 1, 1)) buckets
inner join [DestinationDatabase].dbo.Sizes sz on buckets.SizeIndex = sz.SizeRangeIndex
and sz.SizeRangeId = ranges.Id
left join [DestinationDatabase].dbo.InvoiceDetailSizes dst
on detail.Id = dst.InvoiceDetailId
and sz.Id = dst.SizeId
where
buckets.Units <> 0
and dst.id is null;
Here is the BucketString
table-valued function that I'm using - it's adapted from this Stack Overflow answer, modified so that I could specify an "offset", because my data doesn't start at position 1:
create function [dbo].[BucketString] (
@values varchar(max),
@bucketSize int,
@bufferSize int = 1,
@offset int = 0)
returns @result table (id int, item varchar(max))
begin
with buckets as
(
select 1 id
union all
select t.id + 1
from buckets t
where id = t.id
and t.id < len(@values)/(@bucketSize+@bufferSize)+1
)
insert into @result
select
id,
substring(@values, @offset + ((id - 1) * (@bucketSize + @bufferSize) + (case when @bufferSize-1 = 0 then @bufferSize else @bufferSize-(@bufferSize-1) end)), @bucketSize) string
from buckets
option (maxrecursion 0)
return;
end
The query will not insert anything if the data already exists in the destination table, but in order to find out if it exists, I need to run the whole thing, so whether I'm inserting 1.5M rows, or 0, it's still ~10 minutes.
Can it be optimized in any way?