0

I have a View which is keyed on both ChargeID and CustomerID (Charge can be split between multiple customers). The View consists of 2 tables (heavily simplified here, actual tables have ~40 columns each):

CREATE TABLE tblCharge (ChargeID int NOT NULL, ParentChargeID int)
CREATE TABLE tblChargeShare (ChargeShareID int NOT NULL, ChargeID int NOT NULL, CustomerID int, TotalAmount money, TaxAmount money, DiscountAmount money)

The View just joins these both together:

CREATE VIEW vwBASEChargeChargeShareCustomer AS
Select ParentChargeID, b.* from tblCharge a inner join tblChargeShare b on a.ChargeID = b.ChargeID

I then have a CTE for getting the subcharges for the parent:

WITH RCTE AS
(
SELECT  ParentChargeId, ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount,  ISNULL(TaxAmount, 0) as TaxAmount,  
ISNULL(DiscountAmount, 0) as DiscountAmount, CustomerID, ChargeID as MasterChargeID
FROM vwBASEChargeChargeShareCustomer Where ParentChargeID is NULL

UNION ALL

SELECT rh.ParentChargeID, rh.ChargeID, Lvl+1 AS Lvl, ISNULL(rh.TotalAmount, 0),  ISNULL(rh.TaxAmount, 0),  ISNULL(rh.DiscountAmount, 0) , rh.CustomerID 
, rc.MasterChargeID 
FROM vwBASEChargeChargeShareCustomer rh
INNER JOIN RCTE rc ON rh.PArentChargeID = rc.ChargeID and rh.CustomerID = rc.CustomerID )

Select MasterChargeID, CustomerID, ParentChargeID, ChargeID, TotalAmount, TaxAmount, DiscountAmount , Lvl
FROM  RCTE r 

So the CTE is joining on CustomerID and ChargeID=ParentChargeID

This works well, but does not perform well on large data sets (millions of Charges).

What is the best way to Index the tables (or view) to get the best performance? (SQL 2008R2 and above)

Molloch
  • 2,261
  • 4
  • 29
  • 48
  • What have you tried so far? Obviously you have tried to create indexes on ChargeID, on CustomerID, but you were not satisfied with the performance? Did you try to run Execution Plan? – cha Apr 02 '14 at 05:47
  • Sorry, yes I have separate indexes on tblCharge.ChargeID, tblCharge.ParentChargeID, tblChargeShare.ChargeID and tblChargeShare.CustomerID just on the tables. – Molloch Apr 02 '14 at 06:03
  • What columns are the tables clustered on? – dean Apr 02 '14 at 07:08
  • tblCharge.ChargeID, and tblChargeShare.ChargeShareID – Molloch Apr 02 '14 at 08:05

0 Answers0