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
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?