Set Based Thinking
Set Based Thinking
Inside the where loop a parent record is inserted into a table with a stored procedure, the id is returned with scope_identity() and then that id is used to insert the child record, those records are deleted from the temp table, parent table and child table and the next record in the temp table is grabbed. Needless to say, this takes far too long to run and seems to be very memory intensive on the initiating machine and on top of that it is in SQL Azure so I cannot create it as a job.
I am trying to find a way to change this over to a set-based function to increase efficiency and decrease run-time, but keep running into roadblocks in my thinking. I was thinking that perhaps I could change the parent insert over to a function and then call that function as a parameter to the child insert stored procedure, but that still has me looping thru a recordset one record at a time. Would I need to ditch the temp table and just write the child table insert and call the function to insert the parent record as an insert value?
Right now it looks like this:
INSERT INTO #CoreProcess SELECT core.CoreRawId ,child.ChildTransactionRawId ,core.TxCode ,child.EntityType_ImportFileTypeId ,child.BundleId FROM dbo.CoreRaw core JOIN dbo.[ChildTransactionRaw] child ON (core.Credit = child.Amount OR core.Debit = child.Amount) AND core.BankISN = child.BankISN JOIN dbo.TransactionCode code ON core.TXCode = code.TxCode and child.EntityType_ImportFileTypeId = code.EntityType_ImportFileTypeID and code.HasChildData = 1 WHERE child.Amount > 0 AND core.BankISN = @BankISN AND ABS(DATEDIFF(DD,CONVERT(DATE, core.PacketDate),CONVERT(DATE, child.TransactionDate)))<5 SET @RowCount = (SELECT COUNT(CoreRawId) FROM #CoreProcess) WHILE(@RowCount > 0) BEGIN SELECT TOP 1 @CurrCoreId = CoreRawId, @CurrChildId = ChildTransactionRawId, @BundleId = BundleId FROM #CoreProcess EXEC @transactId = dbo.CopyCoreToHistoryById @CurrCoreId -- Returns ID of parent insert IF (@BundleId = '' or @BundleId is null) BEGIN EXEC dbo.CopyChildRawToHistoryById @transactId, @CurrChildId DELETE FROM #CoreProcess WHERE CoreRawId = @CurrCoreId DELETE FROM dbo.CoreRaw WHERE CoreRawId = @CurrCoreId DELETE FROM dbo.ChildTransactionRaw WHERE ChildTransactionRawId = @CurrChildId END --if bundle is not null, then need to add those child items as well.. ELSE IF (@BundleId <> '' or @BundleId is not null) BEGIN EXEC dbo.CopyChildRawToHistoryByBundleId @transactId, @BundleId DELETE FROM #CoreProcess WHERE BundleId = @BundleId DELETE FROM dbo.CoreRaw WHERE CoreRawId = @CurrCoreId DELETE FROM dbo.ChildTransactionRaw WHERE BundleId = @BundleId END SET @RowCount = (SELECT COUNT(CoreRawId) FROM #CoreProcess) END
I wonder if perhaps I should do something more like:
INSERT INTO tablename(TransactionId,otherfields) SELECT (newfunctiontoinsertparent parentid),otherfields from (full join information)
It seems to me this *should* achieve my goal of not having to step thru RBAR, but instead do it as a set based insert, but not sure if my thinking is entirely correct as far as using a function call to both insert the parent record and return the id of the inserted record within the calling query AND whether this would likely be faster than the existing WHERE loop.
Thoughts or suggestions?