CODEWITHSUNDEEP
sql-serverreporting-services
Bix Shmix
Bix Shmix

Reputation: 99

SSRS Timeout issues with Table Variable

I am trying to build a report with a query that uses a table variable, but it just results in timeouts. There is a large quantity of data involved, but it still performs correctly in SSMS. I am not sure if the cause is the table variable, or if there are just other performance tweaks that I can do, so I am seeking advice. Essentially, what I am doing is grouping certain G/L accounts so they fall under a category, then using those categories as a column group in SSRS. The rest of that data shows a sum of vendor's payments that hit those G/L categories.

declare @VendorRebateGL table
(
Rebate_type varchar(255)
,GL_Account int
)

INSERT INTO @VendorRebateGL (Rebate_type, GL_Account)
VALUES 
('Category1', '5020'), 
('Category1', '5021'), 
('Category1', '5022'), 
('Category2', '5040'), 
('Category2', '5041'), 
('Category3', '5042'), 
('Category3', '5043'), 


SELECT 
       [Vendor No_]
      ,a.[Posting Date]
      ,CAST(SUM([Purchase (LCY)]) AS BIGINT)
      ,[G_L Account No_]
      ,c.Rebate_type
  FROM [DATABASE].[dbo].[Vendor Ledger] a
  LEFT JOIN [G_L Entry] b 
  ON a.[Vendor No_] = b.[Source No_]
  INNER JOIN @VendorRebateGL c
  ON b.[G_L Account No_] = c.GL_Account
  WHERE a.[Document Type] in (2,3)
  AND a.[Posting Date] BETWEEN '2014-06-01 00:00:00.000' and '2014-06-30 00:00:00.000'

  AND [G_L Account No_] IN (
 5020
,5021
,5022
,5040
,5041
,5042
,5043
)
AND (a.Description like 'Invoice%' OR a.Description like 'Credit%')
GROUP BY
  [Vendor No_]
      ,a.[Posting Date]
      ,[G_L Account No_]
      ,c.Rebate_type
ORDER BY a.[Posting Date]

Upvotes: 0

Views: 340

Answers (2)

Anup Agrawal
Anup Agrawal

Reputation: 6669

There are different ways to achieve it.

Method 1: Use Table Value constructor

SELECT 
       [Vendor No_]
      ,a.[Posting Date]
      ,CAST(SUM([Purchase (LCY)]) AS BIGINT)
      ,[G_L Account No_]
      ,c.Rebate_type
  FROM [DATABASE].[dbo].[Vendor Ledger] a
  LEFT JOIN [G_L Entry] b 
  ON a.[Vendor No_] = b.[Source No_]
  INNER JOIN   
       (VALUES 
          ('Category1', '5020'), 
          ('Category1', '5021'), 
          ('Category1', '5022'), 
          ('Category2', '5040'), 
          ('Category2', '5041'), 
          ('Category3', '5042'), 
          ('Category3', '5043'), 
       ) AS c(Rebate_type, GL_Account)
  ON b.[G_L Account No_] = c.GL_Account
  WHERE a.[Document Type] in (2,3)
  AND a.[Posting Date] BETWEEN '2014-06-01 00:00:00.000' and '2014-06-30 00:00:00.000'

  AND [G_L Account No_] IN (
 5020
,5021
,5022
,5040
,5041
,5042
,5043
)
AND (a.Description like 'Invoice%' OR a.Description like 'Credit%')
GROUP BY
  [Vendor No_]
      ,a.[Posting Date]
      ,[G_L Account No_]
      ,c.Rebate_type
ORDER BY a.[Posting Date]

Method 2: Use Common Table Expression(CTE). You can also use Table Value constructor to build your CTE.

;WITH VendorRebateGL(Rebate_type, GL_Account)
AS
(SELECT 'Category1', '5020' 
UNION ALL
SELECT 'Category1', '5021' 
UNION ALL
SELECT 'Category1', '5022' 
UNION ALL
SELECT 'Category2', '5040' 
UNION ALL
SELECT 'Category2', '5041' 
UNION ALL
SELECT 'Category3', '5042' 
UNION ALL
SELECT 'Category3', '5043' 
)

SELECT 
       [Vendor No_]
      ,a.[Posting Date]
      ,CAST(SUM([Purchase (LCY)]) AS BIGINT)
      ,[G_L Account No_]
      ,c.Rebate_type
  FROM [DATABASE].[dbo].[Vendor Ledger] a
  LEFT JOIN [G_L Entry] b 
  ON a.[Vendor No_] = b.[Source No_]
  INNER JOIN VendorRebateGL c
  ON b.[G_L Account No_] = c.GL_Account
  WHERE a.[Document Type] in (2,3)
  AND a.[Posting Date] BETWEEN '2014-06-01 00:00:00.000' and '2014-06-30 00:00:00.000'

  AND [G_L Account No_] IN (
 5020
,5021
,5022
,5040
,5041
,5042
,5043
)
AND (a.Description like 'Invoice%' OR a.Description like 'Credit%')
GROUP BY
  [Vendor No_]
      ,a.[Posting Date]
      ,[G_L Account No_]
      ,c.Rebate_type
ORDER BY a.[Posting Date]

Method 3: USE CASE Statement if dataset is not large enough.

SELECT 
       [Vendor No_]
      ,a.[Posting Date]
      ,CAST(SUM([Purchase (LCY)]) AS BIGINT)
      ,[G_L Account No_]
     , CASE WHEN [G_L Account No_] IN (5020, 5021, 5022) THEN 'Category1'
            WHEN [G_L Account No_] IN (5040, 5041) THEN 'Category2'
           WHEN [G_L Account No_] IN (5042, 5043) THEN 'Category3'
       END AS Rebate_type
  FROM [DATABASE].[dbo].[Vendor Ledger] a
  LEFT JOIN [G_L Entry] b 
  ON a.[Vendor No_] = b.[Source No_]
  WHERE a.[Document Type] in (2,3)
  AND a.[Posting Date] BETWEEN '2014-06-01 00:00:00.000' and '2014-06-30 00:00:00.000'

  AND [G_L Account No_] IN (
 5020
,5021
,5022
,5040
,5041
,5042
,5043
)
AND (a.Description like 'Invoice%' OR a.Description like 'Credit%')
GROUP BY
  [Vendor No_]
      ,a.[Posting Date]
      ,[G_L Account No_]
      ,c.Rebate_type
ORDER BY a.[Posting Date]

Method 4: Create a physical table for VendorRebateGL. Use that in the query.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

Try replacing the table variable with an actual temp table instead. The problem is that the optimizer has no details on table variables and always assumes they contain 1 row, which can result in some really bad decisions. As an added benefit, you can then create an index on your join column, GL_Account, which should also help performance.

create table #VendorRebateGL
(
Rebate_type varchar(255)
,GL_Account int
)

create index IX_VRGL_GLA on #VendorRebateGL(GL_Account) include (Rebate_type)

INSERT INTO #VendorRebateGL (Rebate_type, GL_Account)
...

Upvotes: 1

Related Questions