How to speed up query from 2 tables with 400 million rows each

Question

I have 2 tables, trans_details_sell and trans_details_buy, and both have 400 millions rows each. These 2 table are linked up with a unique column call cdr_id.

A 3rd table is deal_master, which is the master table only have about 300 master records.

My query will be using deal_master to link up trans_details_sell to get the revenue and at the mean time using trans_details_sell to link up trans_details_buy to get the cost (with cdr_id).

• deal_master is using lcr_zone and customer_interconnect to link with trans_details_sell and its columns, lcr_zone and sig_netgroup.

• trans_details_sell has clustered primary key (lcr_zone, sig_netgroup, cdr_id)

• trans_details_buy has clustered primary key (lcr_zone, sig_netgroup, cdr_id)

Both tables have the same data structure but 1 for sell records one for buy records, and also both having CDR_ID as non-clustered unique index.

My main query, when only involve 2 table which is deal_master and trans_details_sell, the speed is ok (to get the revenue), but when add in trans_details_buy to get the cost, it will be extremely slow.

My SQL looks like this:

SELECT 
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    SUBSTRING(CAST(m.start_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 7, 2) start_date, 
    SUBSTRING(CAST(m.end_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 7, 2) end_date, 
    m.target_minutes, m.target_sell_rate, m.target_buy_rate, 
    m.target_sales, m.target_cost, m.target_profit, 
    SUM(s.quantized_duration) / 60 DG_minute, 
    SUM(s.charge) DG_sales, SUM(b.charge) DG_cost
FROM 
    deal_master m, trans_details_sell s, trans_details_buy b
WHERE
    m.lcr_zone = s.lcr_zone 
    AND m.customer_interconnect = s.sig_netgroup 
    AND m.swap_commitment = 'Sell' 
    AND s.cdr_id = b.cdr_id 
    AND s.start_position BETWEEN m.start_pos AND m.end_pos
GROUP BY
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    m.start_pos, m.end_pos, m.target_minutes, m.target_sell_rate, 
    m.target_buy_rate, m.target_sales, m.target_cost, m.target_profit
ORDER BY 
    1

deal_master :

CREATE TABLE [dbo].[deal_master]
(
    [agreement_no] [nchar](10) NOT NULL,
    [status] [nvarchar](20) NOT NULL,
    [sales_person] [nvarchar](50) NOT NULL,
    [swap_carrier] [nvarchar](100) NOT NULL,
    [start_pos] [numeric](18, 0) NOT NULL,
    [end_pos] [numeric](18, 0) NOT NULL,
    [swap_commitment] [nvarchar](10) NOT NULL,
    [zone] [nvarchar](200) NOT NULL,
    [target_minutes] [numeric](10, 0) NULL,
    [target_sell_rate] [decimal](13, 11) NULL,
    [target_buy_rate] [decimal](13, 11) NULL,
    [supplier_interconnect] [nvarchar](200) NOT NULL,
    [customer_interconnect] [nvarchar](200) NOT NULL,
    [target_sales] [numeric](10, 2) NULL,
    [target_cost] [numeric](10, 2) NULL,
    [target_profit] [numeric](10, 2) NULL,
    [partner] [nvarchar](50) NULL,
    [lcr_zone] [nvarchar](100) NOT NULL,
 CONSTRAINT [pk_deal_master] PRIMARY KEY CLUSTERED 
(
    [lcr_zone] ASC,
    [customer_interconnect] ASC,
    [supplier_interconnect] ASC,
    [start_pos] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

trans_details_sell:

CREATE TABLE [dbo].[trans_details_sell]
(
    [cdr_id] [nchar](12) NOT NULL,
    [rate] [nvarchar](50) NOT NULL,
    [zone] [nvarchar](50) NOT NULL,
    [charge] [decimal](13, 11) NOT NULL,
    [quantized_duration] [numeric](8, 0) NOT NULL,
    [sig_carrier_group] [nvarchar](50) NOT NULL,
    [sig_netgroup] [nvarchar](50) NOT NULL,
    [lcr] [nvarchar](100) NOT NULL,
    [lcr_zone] [nvarchar](50) NOT NULL,
    [per_min_chg] [decimal](13, 11) NOT NULL,
    [trans_type] [nvarchar](10) NOT NULL,
    [start_position] [numeric](18, 0) NOT NULL,
    [end_position] [numeric](18, 0) NOT NULL,
    [filename] [nvarchar](50) NOT NULL,
 CONSTRAINT [pk_trans_details_sell] PRIMARY KEY CLUSTERED 
(
    [lcr_zone] ASC,
    [sig_netgroup] ASC,
    [cdr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

trans_details_buy:

CREATE TABLE [dbo].[trans_details_sell]
(
    [cdr_id] [nchar](12) NOT NULL,
    [rate] [nvarchar](50) NOT NULL,
    [zone] [nvarchar](50) NOT NULL,
    [charge] [decimal](13, 11) NOT NULL,
    [quantized_duration] [numeric](8, 0) NOT NULL,
    [sig_carrier_group] [nvarchar](50) NOT NULL,
    [sig_netgroup] [nvarchar](50) NOT NULL,
    [lcr] [nvarchar](100) NOT NULL,
    [lcr_zone] [nvarchar](50) NOT NULL,
    [per_min_chg] [decimal](13, 11) NOT NULL,
    [trans_type] [nvarchar](10) NOT NULL,
    [start_position] [numeric](18, 0) NOT NULL,
    [end_position] [numeric](18, 0) NOT NULL,
    [filename] [nvarchar](50) NOT NULL,
 CONSTRAINT [pk_trans_details_sell] PRIMARY KEY CLUSTERED 
(
    [lcr_zone] ASC,
    [sig_netgroup] ASC,
    [cdr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

The query only needs charge from trans_detail_buy, so add it as an included column on the unique index on clr_id

create unique index ix_clr_id 
 on trans_details_buy(clr_id) 
 include (charge)

Otherwise the clr_id is used to look up the row locator (lcr_zone, sig_netgroup, cdr_id) which is used to seek the clustered index to find the charge.

Or if they _buy and _sell always have the same lcr_zone and sig_netgroup for each clr_id, then join trans_details_buy on all three columns to bypass the index on clr_id.