If transaction within date range, then return customer name (and not all the transactions!)

Question

This code is taking a significant amount of time to run. It's returning every single transaction within the date range but I just need to know if the customer has had at least one transaction, then include the CustomerID, CustomerName, Type, Sign, ReportingName.

I think I need to GROUP BY 'CustomerID' but again only if there was a transaction within the date range. And of course, I'm sure there is an optimal way to execute the below TSQL because it's quite slow at present.

Thanks in advance for any help!

SELECT [ABC].[dbo].[vwPrimary].[RelatedNameId] AS CustomerID
      ,[ABC].[dbo].[vwPrimary].[RelatedName] AS CustomerName
      ,[AFGPurchase].[IvL].[TaxTreatment].[ParticluarType] AS Type
      ,[AFGPurchase].[IvL].[Product].[Sign] AS [Sign]
      ,[AFGPurchase].[IvL].[Product].[ReportingName] AS ReportingName 
      ,[AFGPurchase].[IvL].[Transaction].[EffectiveDate] AS 'Date'
FROM (((([AFGPurchase].[IvL].[Account]
    INNER JOIN [AFGPurchase].[IvL].[Position] ON [AFGPurchase].[IvL].[Account].[AccountId] = [AFGPurchase].[IvL].[Position].[AccountId])
    INNER JOIN [AFGPurchase].[IvL].[Product] ON [AFGPurchase].[IvL].[Position].[ProductID] = [AFGPurchase].[IvL].[Product].[ProductId])
    INNER JOIN [ABC].[dbo].[vwPrimary] ON [AFGPurchase].[IvL].[Account].[ReportingEntityId] = [ABC].[dbo].[vwPrimary].[RelatedNameId])
    INNER JOIN [AFGPurchase].[IvL].[TaxTreatment] ON [AFGPurchase].[IvL].[Account].[TaxTreatmentId] = [AFGPurchase].[IvL].[TaxTreatment].[TaxTreatmentId])
    INNER JOIN [AFGPurchase].[IvL].[Transaction] ON [AFGPurchase].[IvL].[Position].[PositionId]  = [AFGPurchase].[IvL].[Transaction].[PositionId] 
WHERE ((([AFGPurchase].[IvL].[TaxTreatment].[RegistrationType]) LIKE 'NON%') 
    AND (([AFGPurchase].[IvL].[Product].[Sign])='XYZ2') 
    AND (([AFGPurchase].[IvL].[Position].[Quantity])<>0) 
    AND (([AFGPurchase].[IvL].[Transaction].[EffectiveDate]) between '2021-12-31' and '2022-12-31'))

score 0 · Answer 1 · answered Jan 06 '23 at 15:09

Check your indexes on fragmentation, to speed up your query. And make sure you have indexes.

If you just need one result, just TOP 1

SELECT TOP 1 [ABC].[dbo].[vwPrimary].[RelatedNameId] AS CustomerID
      ,[ABC].[dbo].[vwPrimary].[RelatedName] AS CustomerName
      ,[AFGPurchase].[IvL].[TaxTreatment].[ParticluarType] AS Type
      ,[AFGPurchase].[IvL].[Product].[Sign] AS [Sign]
      ,[AFGPurchase].[IvL].[Product].[ReportingName] AS ReportingName 
      ,[AFGPurchase].[IvL].[Transaction].[EffectiveDate] AS 'Date'
FROM (((([AFGPurchase].[IvL].[Account]
    INNER JOIN [AFGPurchase].[IvL].[Position] ON [AFGPurchase].[IvL].[Account].[AccountId] = [AFGPurchase].[IvL].[Position].[AccountId])
    INNER JOIN [AFGPurchase].[IvL].[Product] ON [AFGPurchase].[IvL].[Position].[ProductID] = [AFGPurchase].[IvL].[Product].[ProductId])
    INNER JOIN [ABC].[dbo].[vwPrimary] ON [AFGPurchase].[IvL].[Account].[ReportingEntityId] = [ABC].[dbo].[vwPrimary].[RelatedNameId])
    INNER JOIN [AFGPurchase].[IvL].[TaxTreatment] ON [AFGPurchase].[IvL].[Account].[TaxTreatmentId] = [AFGPurchase].[IvL].[TaxTreatment].[TaxTreatmentId])
    INNER JOIN [AFGPurchase].[IvL].[Transaction] ON [AFGPurchase].[IvL].[Position].[PositionId]  = [AFGPurchase].[IvL].[Transaction].[PositionId] 
WHERE ((([AFGPurchase].[IvL].[TaxTreatment].[RegistrationType]) LIKE 'NON%') 
    AND (([AFGPurchase].[IvL].[Product].[Sign])='XYZ2') 
    AND (([AFGPurchase].[IvL].[Position].[Quantity])<>0) 
    AND (([AFGPurchase].[IvL].[Transaction].[EffectiveDate]) between '2021-12-31' and '2022-12-31'))

This only produces 1 result. I need the Top 1 for each CustomerID — Ben, Jan 06 '23 at 15:12
then please have a look at https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group there are also performance stats included — user1737853, Jan 06 '23 at 15:25

score 0 · Answer 2 · answered Jan 06 '23 at 15:58

If you only need to check for the existence of a row, and not actually get any data from it then use EXISTS() rather than INNER JOIN, e.g.

SELECT  vpr.[RelatedNameId] AS CustomerID
       ,vpr.[RelatedName] AS CustomerName
       ,tt.[ParticluarType] AS Type
       ,prd.[Sign]
       ,prd.ReportingName 
       ,tr.[EffectiveDate] AS [Date]
FROM    [AFGPurchase].[IvL].[Account] AS acc
        INNER JOIN [AFGPurchase].[IvL].[Position] AS pos ON acc.[AccountId] = pos.[AccountId]
        INNER JOIN [AFGPurchase].[IvL].[Product] AS prd ON pos.[ProductID] = prd.[ProductId]
        INNER JOIN [ABC].[dbo].[vwPrimary] AS vpr ON acc.[ReportingEntityId] = vpr.[RelatedNameId]
        INNER JOIN [AFGPurchase].[IvL].[TaxTreatment] AS tt ON acc.[TaxTreatmentId] = tt.[TaxTreatmentId]
WHERE   tt.[RegistrationType] LIKE 'NON%'
AND     prd.[Sign]='XYZ2'
AND     pos.[Quantity]<>0
AND     EXISTS
        (   SELECT  1
            FROM    [AFGPurchase].[IvL].[Transaction] AS tr 
            WHERE   tr.[PositionId]  = pos.[PositionId]
            AND     tr.[EffectiveDate] BETWEEN '2021-12-31' AND '2022-12-31'
        );

N.B. I have added in table aliases and removed all the unnecessary parentheses for readability - you may disagree that it is more readable, but I would expect that most people would agree

This may not offer any performance benefits over simply grouping by the columns you are selecting and keeping your joins as they are - SQL is after all a declarative language where you tell the engine what you want, not how to get it. So you may find that the two plans are the same because you are requesting the same result. Using EXISTS does have the advance of being more semantically tied to what you are trying to do though, so gives the optimiser the best chance of getting to the right plan. If you are still having performance issues, then you may need to inspect the execution plan, and see if it suggests any indexes.

Finally, if you are really still using SQL Server 2008 then you really need to start thinking about your upgrade path. It has been completely unsupported for over 3 years now.

If transaction within date range, then return customer name (and not all the transactions!)

2 Answers2