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.SizeIdwhere     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;endThe 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?
Normalizing Invoices
sql;sql server;join
Have you tried replacing the CTE? A temp table or subquery may be faster. While CTE's are good for readability they are typically outpaced by alternative options. Here is a temp table solution.SELECTsrc.f2 AS InvoiceNumber,CAST(src.f5 AS INT) AS InvoiceLine,src.f23 AS SizeRangeCode,src.f19 AS UnitsPerSizeINTO #sizedInvoicesFROM Staging.dbo.[SourceTable] srcWHERE src.f1 = '01'--INSERT INTO [DestinationDatabase].dbo.InvoiceDetailSizes (InvoiceDetailId, SizeId, Units, DateInserted)SELECT    detail.Id InvoiceDetailId,    sz.Id SizeId,    buckets.Units,    GETDATE()FROM #sizedInvoices srcINNER JOIN [DestinationDatabase].dbo.InvoiceHeaders header     ON src.InvoiceNumber = header.NumberINNER JOIN [DestinationDatabase].dbo.InvoiceDetails detail     ON src.InvoiceLine = detail.LineNumber     AND detail.InvoiceHeaderId = header.IdINNER JOIN [DestinationDatabase].dbo.SizeRanges ranges     ON src.SizeRangeCode = ranges.CodeCROSS APPLY (    SELECT          id SizeIndex,         CAST(item AS INT) Units     FROM [DestinationDatabase].dbo.BucketString(src.UnitsPerSize, 5, 1, 1)) bucketsINNER JOIN [DestinationDatabase].dbo.Sizes sz     ON buckets.SizeIndex = sz.SizeRangeIndex     AND sz.SizeRangeId = ranges.IdLEFT JOIN [DestinationDatabase].dbo.InvoiceDetailSizes dst    ON detail.Id = dst.InvoiceDetailId    AND sz.Id = dst.SizeIdWHERE buckets.Units <> 0AND dst.id is null;DROP TABLE #sizedInvoicesHere is a subquery solution. --insert into [DestinationDatabase].dbo.InvoiceDetailSizes (InvoiceDetailId, SizeId, Units, DateInserted)SELECT    detail.Id InvoiceDetailId,    sz.Id SizeId,    buckets.Units,    GETDATE()FROM (    SELECT         src.f2 AS InvoiceNumber,    CAST(src.f5 AS INT) AS InvoiceLine,    src.f23 AS SizeRangeCode,    src.f19 AS UnitsPerSize     FROM Staging.dbo.[SourceTable] src     WHERE src.f1 = '01') srcINNER JOIN [DestinationDatabase].dbo.InvoiceHeaders header     ON src.InvoiceNumber = header.NumberINNER JOIN [DestinationDatabase].dbo.InvoiceDetails detail     ON src.InvoiceLine = detail.LineNumber     AND detail.InvoiceHeaderId = header.IdINNER JOIN [DestinationDatabase].dbo.SizeRanges ranges     ON src.SizeRangeCode = ranges.CodeCROSS APPLY (    SELECT          id SizeIndex,     CAST(item AS INT) Units      FROM [DestinationDatabase].dbo.BucketString(src.UnitsPerSize, 5, 1, 1)) bucketsINNER JOIN [DestinationDatabase].dbo.Sizes sz     ON buckets.SizeIndex = sz.SizeRangeIndex     AND sz.SizeRangeId = ranges.IdLEFT JOIN [DestinationDatabase].dbo.InvoiceDetailSizes dst    ON detail.Id = dst.InvoiceDetailId    AND sz.Id = dst.SizeIdWHERE buckets.Units <> 0AND dst.id is null;In this situation, I would prefer the temp table solution. The answers to this question explain better than I ever could, but basically temp tables will work better with a large number of records. Other wise, it looks like generally well written SQL. I took the liberty of capitalizing keywords in these queries, as is conventional.