-2

This stored procedure is part of a report that im working on. So it runs very slow runs very slow 48 min to be exact. I got it to run in 6 sec by taking out the or operator in the join you see below. Now it returns not duplicates but data from both tables which adds more rows to my result set. the issue is the two joins that are FinancialTransactions ft ,FinancialTransactions ftRec

I have tried to use CTE. I have split the joins up and union All them. I am not able to add index to the query or create any views.I have also join to a different table as well as a different view which returned more row then needed. 

                                   Before Fix



  SELECT DISTINCT 
             1 as SourceId,
             l.LotID as 'LotId',
             r.Id as 'RecoveryId',
             acc.AccountName as 'Account',
             r.BillingCode as BillingCode,
             r.ProjectCode as ProjectCode,
             a.AssetID as 'SSE Number',
             ' ' as 'ParentSSENumber',
             COALESCE(ft.InvoiceNumber,ftrec.InvoiceNumber) as InvoiceNumber, 
             COALESCE(ft.InvoicedOn,ftrec.InvoicedOn) as InvoiceDate, 
             a.Class AS Category,
             a.Manufacturer,
             a.Product AS Model, 
             a.ProductNumber AS ModelNum,
             a.SerialNumber AS SerialNum, 
             a.CustomerAssetNumber AS CustAssetTag, 
             a.Weight, 
             @WeightMeasure AS WeightMeasurement,
            CASE a.ClassID --a.LockedStatus 
                WHEN 919
                    THEN CASE hdd.Disposition 
                         WHEN 'Inventory' 
                         THEN
                             CASE  
                             WHEN -ft.Rate > 0 
                                THEN 'Resale'
                                ELSE 'Active' END 
                          ELSE hdd.Disposition END
                ELSE 
                    CASE --ISNULL(A.LockedStatus, - 1) 
                        WHEN A.LockedStatus = -1  THEN 'InProduction' 
                        WHEN A.LockedStatus = 1 THEN 'Active' 
                        WHEN A.LockedStatus = 2 THEN 'On Hold' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 7 THEN 'Redeployment' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 12 THEN 'Donation' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 11 THEN 'Legal Hold Release' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType NOT IN (7,11,12) THEN 'Sold' 
                        WHEN A.LockedStatus = 4 THEN 'Upgrade' 
                        WHEN A.LockedStatus = 5 THEN 'Recycled' 
                        WHEN A.LockedStatus = 6 THEN 'Sold Off-site' 
                        ELSE '' 
                    END
                 END
                AS Disposition,
            CASE WHEN a.LockedStatus = 5
                 THEN ftrec.CreatedOn
                 ELSE iph_gen.shipDate END AS DispositionDate,               
            '' AS Comments, --REPLACE(r.AdditionalNotes , CHAR(10), '') as Comments,
            isnull(GTu.Name, 'N/A') AS FinalGrade,
              ' ', ' ',
              ISNULL(CASE ftRec.RateTypeID WHEN 1 THEN a.Weight ELSE 1 END * ftRec.Rate,0)  AS RecyclingFees,
            --  ISNULL(ft.RemarketingPercentage,0),
              isnull(ft.Rate,0) AS RemarketingCredit,
              --isnull(gt.Name, 'N/A') AS RegisteredGrade,
              --' ', 
             0.00 as LeaseReturnFee,
             CASE WHEN iph_gen.PurchaseType = 7 THEN ISNULL(ipds_gen.Price,0)
                 ELSE 0.00
                 END AS RedploymentFee,
             CASE WHEN ccsg.GroupName LIKE '%Donation%' THEN
                 CASE WHEN iph_gen.PurchaseType = 12 THEN ISNULL(ipds_gen.Price,0)
                 ELSE 0.00
                 END ELSE 0.00
                 END AS DonationFee

        FROM vAssetSimpleView a 
        INNER JOIN itTransactionProcess AS tp ON tp.TransactionID = a.OrigTransactionID 
        INNER JOIN itLots AS l ON l.LotID = tp.LotNumber 
        LEFT JOIN [10.10.1.188,13151].Adviser.dbo.Recovery r on l.LotID = r.MRMLotId
        --LEFT JOIN [54.84.218.110,13151].Adviser.dbo.Account acc on r.AccountId = acc.Id
        LEFT OUTER JOIN AccountLocationOrderNo AS alon ON alon.LotID = l.LotID 
        LEFT JOIN AccountLocation AS al ON al.LocationID = l.LocationID 
        LEFT JOIN Account acc ON al.accountId = acc.AccountID
        LEFT JOIN itDocks AS d ON d.DockID = l.DockScheduledOn 
        LEFT OUTER JOIN Contact AS c ON al.Contact_Onsite = c.ContactID 
        LEFT OUTER JOIN itGradeTypes AS g ON g.RecID = a.Grade
        --JOINS FOR ORDERS
        LEFT JOIN itPurchaseDetails ipd_gen on ipd_gen.assetid = a.assetid
        LEFT JOIN itPurchaseDetailService ipds_gen on ipd_gen.RecID = ipds_gen.PurchaseDetailRecId
        LEFT JOIN ContractCustomServiceGroups ccsg on ccsg.recid = ipds_gen.ServiceGroupId
                        --AND ipds_donation.ServiceGroupId IN (SELECT  RecID FROM [ContractCustomServiceGroups]  WHERE GroupName Like '%Donation%')
        LEFT JOIN itPurchaseHeader iph_gen on ipd_gen.PurchaseHeaderRecID = iph_gen.RecID
                        --AND iph_donation.PurchaseType = 12
        --END JOINS FOR ORDERS
        LEFT OUTER JOIN dbo.Reporting_Inventory_Harddrives hdd ON hdd.AssetID = a.AssetID
        --LEFT OUTER JOIN vAssetServicesByType vast ON a.AssetID = vast.AssetRecId
        --OUTER APPLY
        --(
        --  SELECT ph.RecID AS OrderID, v.VendorName, ph.OrderDate,ph.shipdate, cup.FirstName + ' ' + cup.LastName AS SalesRep, pd.LineTotal AS ResalePrice
        --  FROM dbo.itPurchaseDetails pd
        --  INNER JOIN dbo.itPurchaseHeader ph ON ph.RecID = pd.PurchaseHeaderRecID
        --  INNER JOIN dbo.itVendorLocation vl ON vl.LocationID = ph.VendorLocationID
        --  INNER JOIN dbo.itVendor v ON v.VendorID = vl.VendorID
        --  INNER JOIN dbo.aspnet_custom_UserProfiles cup ON cup.UserID = ph.CreatedBy
        --  WHERE ph.Status > 1 
        --  AND pd.AssetID = a.AssetID
        --) AS do
        LEFT OUTER JOIN FinancialTransactions ft ON ((ft.FinancialTypeID = 9 AND ft.AssetID = a.AssetID) OR (ft.FinancialTypeID = 8 AND ft.FKeyID = a.OrigTransactionID AND ft.AssetID = 0))
        LEFT OUTER JOIN FinancialTransactions ftRec ON ((ftRec.AssetID = a.AssetID) OR (ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0)) AND ftRec.FinancialTypeID = 1
        --OUTER APPLY 
        --(
        --  SELECT ISNULL(SUM(csa.ServicePriceTotal),0) AS total
        --  FROM ContractServicesApplied csa 
        --  WHERE csa.AssetID = a.AssetID 
        --  AND csa.Completed = 1
        --) AS csa
        --CROSS APPLY(SELECT ISNULL(SUM(T.PartValue),0) AS UpgradeValue
        --FROM dbo.itAssetComponentTransactions AS T
        --INNER JOIN dbo.itAssetComponents AS AC
        --ON AC.RecID = T.AssetComponentID WHERE
        --(T.AssetID = A.AssetId)
        --AND (T.TransactionTypeID = 1) AND (AC.DateRemoved IS NULL)) as uv
        --LEFT JOIN [dbo].[Reporting_Inventory_Harddrives] rih ON a.AssetId = rih.AssetId
        --Tony Added For Recycling Fee Column
        LEFT JOIN (SELECT tp.AssetId, -1*ft.Total AS RecyclingTotal
            FROM itTransactionProcess tp
            INNER JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
            LEFT OUTER JOIN FinancialTransactions ft ON ft.FKeyID = tp.TransactionID
            INNER JOIN itLots il on tp.LotNumber = il.LotID
            INNER JOIN AccountLocation al on al.LocationID = il.LocationID
            INNER JOIN Account acc on al.AccountID = acc.accountId
            WHERE acc.AccountID = @AccountId
            AND ps.IsRecyclingProcess = 1
            AND ps.IsEndProcess = 1 ) As Recycling ON a.AssetID = Recycling.AssetID
        --End Recycling Join

        --Tony Added to include Parts section in Asset Detail body of reports
        LEFT JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
        --End Parts Join
        --Tony Added for Grade/Condition data
        LEFT OUTER JOIN dbo.itGradeTypes GT ON GT.RecID = a.Grade
        INNER JOIN itAsset ia ON a.AssetID = ia.AssetRecId
        LEFT OUTER JOIN dbo.itGradeTypes GTu ON GTu.RecID = ia.Upgrade_Grade  --Join to asset because vAssetSimpleView does not have upgrade_grade
        --End Grade Join
        OUTER APPLY
        (
            SELECT TOP 1 CASE WHEN ps.IsRecyclingProcess = 1 THEN 'Recycled' WHEN ps.ProcessStepID = 25 THEN 'Resale' ELSE ps.Name END AS Disposition
            FROM itTransactionProcess tp 
            INNER JOIN itProcessSteps ps on ps.ProcessStepID = tp.CurrentProcessStepID 
            WHERE tp.AssetID = a.AssetID 
            AND ps.IsEndProcess = 1
            --AND a.AssetTypeID = 1
        ) AS assetDisp
        WHERE acc.accountid = @AccountId
        AND ((iph_gen.shipDate >= @StartDate AND iph_gen.ShipDate <= @EndDate) OR (a.LockedStatus = 5 AND ftrec.CreatedOn >= @StartDate AND ftrec.CreatedOn <= @EndDate))
        AND ((a.AssetTypeID =1) OR (a.AssetTypeID = 2 and a.ClassID = 919) OR (a.AssetTypeID = 3 and a.ClassID = 919))
         --make sure hard drives are part of the asset report

    LEFT OUTER JOIN FinancialTransactions ftRec ON ((ftRec.AssetID = a.AssetID) OR (ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0)) AND ftRec.FinancialTypeID = 1
            --OUTER APPLY 

                             After FIX
SELECT DISTINCT


 1 as SourceId,
             l.LotID as 'LotId',
             r.Id as 'RecoveryId',
             acc.AccountName as 'Account',
             r.BillingCode as BillingCode,
             r.ProjectCode as ProjectCode,
             a.AssetID as 'SSE Number',
             ' ' as 'ParentSSENumber',
             COALESCE(ft.InvoiceNumber,Ftrec.InvoiceNumber) as InvoiceNumber,
             COALESCE(ft.InvoicedOn,Ftrec.Invoicedon) as InvoiceDate, 
             a.Class AS Category,
             a.Manufacturer,
             a.Product AS Model, 
             a.ProductNumber AS ModelNum,
             a.SerialNumber AS SerialNum, 
             a.CustomerAssetNumber AS CustAssetTag, 
             a.Weight, 
             @WeightMeasure AS WeightMeasurement,
            CASE a.ClassID --a.LockedStatus 
                WHEN 919
                    THEN CASE hdd.Disposition 
                         WHEN 'Inventory' 
                         THEN
                             CASE  
                             WHEN -ft.Rate > 0 
                                THEN 'Resale'
                                ELSE 'Active' END 
                          ELSE hdd.Disposition END
                ELSE 
                    CASE --ISNULL(A.LockedStatus, - 1) 
                        WHEN A.LockedStatus = -1  THEN 'InProduction' 
                        WHEN A.LockedStatus = 1 THEN 'Active' 
                        WHEN A.LockedStatus = 2 THEN 'On Hold' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 7 THEN 'Redeployment' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 12 THEN 'Donation' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType = 11 THEN 'Legal Hold Release' 
                        WHEN A.LockedStatus = 3 AND iph_gen.PurchaseType NOT IN (7,11,12) THEN 'Sold' 
                        WHEN A.LockedStatus = 4 THEN 'Upgrade' 
                        WHEN A.LockedStatus = 5 THEN 'Recycled' 
                        WHEN A.LockedStatus = 6 THEN 'Sold Off-site' 
                        ELSE '' 
                    END
                 END
                AS Disposition,
            CASE WHEN a.LockedStatus = 5
                 THEN ftRec.CreatedOn
                 ELSE iph_gen.shipDate END AS DispositionDate,               
            '' AS Comments, --REPLACE(r.AdditionalNotes , CHAR(10), '') as Comments,
            isnull(GTu.Name, 'N/A') AS FinalGrade,
              ' ', ' ',
              ISNULL(CASE ftRec.RateTypeID WHEN 1 THEN a.Weight ELSE 1 END * ftRec.Rate,0)  AS RecyclingFees,
            --  ISNULL(ft.RemarketingPercentage,0),
              isnull(ft.Rate,0) AS RemarketingCredit,
              --isnull(gt.Name, 'N/A') AS RegisteredGrade,
              --' ', 
             0.00 as LeaseReturnFee,
             CASE WHEN iph_gen.PurchaseType = 7 THEN ISNULL(ipds_gen.Price,0)
                 ELSE 0.00
                 END AS RedploymentFee,
             CASE WHEN ccsg.GroupName LIKE '%Donation%' THEN
                 CASE WHEN iph_gen.PurchaseType = 12 THEN ISNULL(ipds_gen.Price,0)
                 ELSE 0.00
                 END ELSE 0.00
                 END AS DonationFee
                 --Rank () over
                 --         (
              --              PARTITION by [InviovceDate]
                    --      Order by [SSE Number] DESC
                    --      ) as rank


        FROM vAssetSimpleView a      
        INNER JOIN itTransactionProcess AS tp ON tp.TransactionID = a.OrigTransactionID 
        INNER JOIN itLots AS l ON l.LotID = tp.LotNumber 
        LEFT JOIN [10.10.1.188,13151].Adviser.dbo.Recovery r on l.LotID = r.MRMLotId
        --LEFT JOIN [54.84.218.110,13151].Adviser.dbo.Account acc on r.AccountId = acc.Id
        LEFT OUTER JOIN AccountLocationOrderNo AS alon ON alon.LotID = l.LotID 
        LEFT JOIN AccountLocation AS al ON al.LocationID = l.LocationID 
        LEFT JOIN Account acc ON al.accountId = acc.AccountID
        LEFT JOIN itDocks AS d ON d.DockID = l.DockScheduledOn 
        LEFT OUTER JOIN Contact AS c ON al.Contact_Onsite = c.ContactID 
        LEFT OUTER JOIN itGradeTypes AS g ON g.RecID = a.Grade
        --JOINS FOR ORDERS
        LEFT JOIN itPurchaseDetails ipd_gen on ipd_gen.assetid = a.assetid
        LEFT JOIN itPurchaseDetailService ipds_gen on ipd_gen.RecID = ipds_gen.PurchaseDetailRecId
        LEFT JOIN ContractCustomServiceGroups ccsg on ccsg.recid = ipds_gen.ServiceGroupId
                        --AND ipds_donation.ServiceGroupId IN (SELECT  RecID FROM [ContractCustomServiceGroups]  WHERE GroupName Like '%Donation%')
        LEFT JOIN itPurchaseHeader iph_gen on ipd_gen.PurchaseHeaderRecID = iph_gen.RecID
                        --AND iph_donation.PurchaseType = 12
        --END JOINS FOR ORDERS
        LEFT OUTER JOIN dbo.Reporting_Inventory_Harddrives hdd ON hdd.AssetID = a.AssetID
        --LEFT OUTER JOIN vAssetServicesByType vast ON a.AssetID = vast.AssetRecId
        --OUTER APPLY
        --(
        --  SELECT ph.RecID AS OrderID, v.VendorName, ph.OrderDate,ph.shipdate, cup.FirstName + ' ' + cup.LastName AS SalesRep, pd.LineTotal AS ResalePrice
        --  FROM dbo.itPurchaseDetails pd
        --  INNER JOIN dbo.itPurchaseHeader ph ON ph.RecID = pd.PurchaseHeaderRecID
        --  INNER JOIN dbo.itVendorLocation vl ON vl.LocationID = ph.VendorLocationID
        --  INNER JOIN dbo.itVendor v ON v.VendorID = vl.VendorID
        --  INNER JOIN dbo.aspnet_custom_UserProfiles cup ON cup.UserID = ph.CreatedBy
        --  WHERE ph.Status > 1 
        --  AND pd.AssetID = a.AssetID
        --) AS do
        LEFT OUTER JOIN FinancialTransactions ft ON ft.FinancialTypeID = 9 AND ft.AssetID = a.AssetID  
        LEFT OUTER JOIN   FinancialTransactions ft1 ON ft.FinancialTypeID = 8 AND ft.FKeyID = a.OrigTransactionID AND ft.AssetID = 0 
        LEFT OUTER JOIN FinancialTransactions ftRec ON ftRec.AssetID = a.AssetID 
        LEFT OUTER JOIN  FinancialTransactions ftRec1 ON ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0  AND ftRec.FinancialTypeID = 1

        --LEFT OUTER JOIN FinancialTransactions ft ON ((ft.FinancialTypeID = 9 AND ft.AssetID = a.AssetID and (ft.FinancialTypeID = 8 AND ft.FKeyID = a.OrigTransactionID AND ft.AssetID = 0))
        --LEFT OUTER JOIN FinancialTransactions ftRec ON ftRec.AssetID = a.AssetID and ftRec.FKeyID = a.OrigTransactionID and ftRec.AssetID = 0 AND ftRec.FinancialTypeID = 1
        --OUTER APPLY 
        --(
        --  SELECT ISNULL(SUM(csa.ServicePriceTotal),0) AS total
        --  FROM ContractServicesApplied csa 
        --  WHERE csa.AssetID = a.AssetID 
        --  AND csa.Completed = 1
        --) AS csa
        --CROSS APPLY(SELECT ISNULL(SUM(T.PartValue),0) AS UpgradeValue
        --FROM dbo.itAssetComponentTransactions AS T
        --INNER JOIN dbo.itAssetComponents AS AC
        --ON AC.RecID = T.AssetComponentID WHERE
        --(T.AssetID = A.AssetId)
        --AND (T.TransactionTypeID = 1) AND (AC.DateRemoved IS NULL)) as uv
        --LEFT JOIN [dbo].[Reporting_Inventory_Harddrives] rih ON a.AssetId = rih.AssetId
        --Tony Added For Recycling Fee Column
        LEFT JOIN  (SELECT  tp.AssetId, -1*ft.Total AS RecyclingTotal
            FROM itTransactionProcess tp
            INNER JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
            LEFT OUTER JOIN FinancialTransactions ft ON ft.FKeyID = tp.TransactionID
            INNER JOIN itLots il on tp.LotNumber = il.LotID
            INNER JOIN AccountLocation al on al.LocationID = il.LocationID
            INNER JOIN Account acc on al.AccountID = acc.accountId
            WHERE acc.AccountID = @AccountId 
            AND ps.IsRecyclingProcess = 1
            AND ps.IsEndProcess = 1 ) As Recycling ON a.AssetID = Recycling.AssetID
        --End Recycling Join

        --Tony Added to include Parts section in Asset Detail body of reports
        LEFT JOIN itProcessSteps ps ON tp.NextProcessStepID = ps.ProcessStepID
        --End Parts Join
        --Tony Added for Grade/Condition data
        LEFT OUTER JOIN dbo.itGradeTypes GT ON GT.RecID = a.Grade
        INNER JOIN itAsset ia ON a.AssetID = ia.AssetRecId
        LEFT OUTER JOIN dbo.itGradeTypes GTu ON GTu.RecID = ia.Upgrade_Grade  --Join to asset because vAssetSimpleView does not have upgrade_grade
        --End Grade Join
        OUTER APPLY
        (
            SELECT TOP 1 CASE WHEN ps.IsRecyclingProcess = 1 THEN 'Recycled' WHEN ps.ProcessStepID = 25 THEN 'Resale' ELSE ps.Name END AS Disposition
            FROM itTransactionProcess tp 
            INNER JOIN itProcessSteps ps on ps.ProcessStepID = tp.CurrentProcessStepID 
            WHERE tp.AssetID = a.AssetID 
            AND ps.IsEndProcess = 1
            --AND a.AssetTypeID = 1
        ) AS assetDisp 
        WHERE acc.accountid = @AccountId
        AND ((iph_gen.shipDate >= @StartDate AND iph_gen.ShipDate <= @EndDate) OR (a.LockedStatus = 5 AND ftRec.CreatedOn >= @StartDate AND ftRec.CreatedOn <= @EndDate))
        AND ((a.AssetTypeID =1) OR (a.AssetTypeID = 2 and a.ClassID = 919) OR (a.AssetTypeID = 3 and a.ClassID = 919))

I know that this particular pieces of the proc is slowing down performance. I would like to be able to write these join in a way that keeps that speed but also removes that extra data. I tried to add the execution plan but was not able I have them if you could give some insight on how to do that that would be helpful.

King Love
  • 7
  • 4
  • 3
    Hi and welcome to SO. You are not likely going to get much help here. All you did was plaster a couple hundred lines of sql and asked for others to make it faster. We have no idea what is slow, the architecture of the tables, indexes, or what you are trying to accomplish. I would argue that if you are not allowed to create indexes there is a problem at your work place. You can't be told to improve performance of a query and prevented from doing the things needed to be successful. – Sean Lange Jan 29 '19 at 15:39
  • There are entire books dedicated to performance tuning and having a query this big makes it very hard to troubleshoot. Try to isolate the problem and come back with a more bounded problem. – EzLo Jan 29 '19 at 15:40
  • Thanks for the reply. There are major problems here. I know I have lived with this Proc for weeks now Im just reaching out to see if there is some type of wizard out there that can put a spell on this thing or something. This is the updated version where the problem is – King Love Jan 29 '19 at 15:53
  • You're not really giving us enough information to work with. Questions asking for performance tuning or debugging help should a Minimal, Complete and Verifiable Example (https://stackoverflow.com/help/mcve). Show us your table structures, give us some sample data, and show that you've made efforts to tackle the problem on your own, and then you will probably get better assistance. – digital.aaron Jan 29 '19 at 16:02
  • Please provide CONSUMALBLE Testdata - see if you can create some kind of Testable with examples where the problem is reproducable. Basing on this testdata, provide CONSUMALBLE example code of the WHOLE query instead of the joins only. – Tyron78 Jan 29 '19 at 16:32
  • Wow! I just had the whole query here then was told to just add the place where the issue was. Wow – King Love Jan 29 '19 at 16:41
  • I recommend providing a single query that is producing the expected results as well as table structure and sample data. I'm having a difficult time diffing the two versions and that is making it harder to help. Also, post the execution plan generated by the slow SQL. – UnhandledExcepSean Jan 29 '19 at 16:52

1 Answers1

0

Assuming that you are correct on the ft and ftrec you can try the following:

Add the code on top:

DROP TABLE IF EXISTS #ftrec

SELECT * 
INTO #ftrec
FROM FinancialTransactions 
where AssetID in (select AssetID from vAssetSimpleView where AssetID is not null)

CREATE CLUSTERED INDEX IX_ftrec on #ftrec(AssetID)

and replace the

LEFT OUTER JOIN FinancialTransactions ft ON ft.FinancialTypeID = 9
                                             AND ft.AssetID = a.AssetID


LEFT OUTER JOIN FinancialTransactions ftRec ON ftRec.AssetID = a.AssetID

with

LEFT OUTER JOIN #ftrec ft ON ft.FinancialTypeID = 9
                                             AND ft.AssetID = a.AssetID

LEFT OUTER JOIN #ftrec ftRec ON ftRec.AssetID = a.AssetID

This assumes that the FinancialTransactions is a bit small. You can replace the * with only the columns you actually need for ftRec. The performance hit may be on the initial query population the #ftrec. This solution may not be viable if the data on FinancialTransactions is huge.

Earl
  • 420
  • 5
  • 16