SSRS Expiration Periods with Table Variable

advertisements

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]


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.