SQL Server : improving efficiency of cross joining to create a matrix result

Question

I have an application where the end user may run a report by selecting one or many:

Customers
Time Periods
Metrics

Due to a constraint in my application / reporting platform, I must return a query which contains one row for every combination as selected by the user, even if there is no data for that combination.

I'd like to see if there is any way for me to optimize the query, as I've found it takes quite a lot of time to join the matrix I'm building to the actual financial data to build the data set to be returned.

I've put together a sample as below (SQL Server 2008 R2):

--Average 12 metrics, max 50
declare @Metrics table (MetricID int)
insert into @Metrics values (1000)
insert into @Metrics values (2000)
insert into @Metrics values (3000)

--Average 12 periods, max 24
declare @Periods table (PeriodID int)
insert into @Periods values (201301)
insert into @Periods values (201302)
insert into @Periods values (201303)

--Between 200 and 500 customers
declare @Customers table (CustomerID int)
insert into @Customers values (1)
insert into @Customers values (2)
insert into @Customers values (3)

--Create combined table
declare @IDMatrix table (CustomerID int, PeriodID int, MetricID int)

INSERT INTO @IDMatrix
SELECT x1.CustomerID, 
        x1.PeriodID, 
        m.MetricID
FROM (SELECT c.CustomerID, p.PeriodID
        FROM @Customers c, @Periods p) x1, @Metrics m
ORDER BY x1.customerid, x1.periodid, m.MetricID


declare @Financials table (CustomerID int, MetricID int, PeriodID int, Value float)
insert into @Financials values (1,1000,201301,984.2)
insert into @Financials values (1,1000,201302,245.1)
insert into @Financials values (1,1000,201303,789.1)

SELECT
    i.CustomerID,
    i.PeriodID,
    i.MetricID,
    sum(isnull(f.Value,0))
FROM @IDMatrix i
LEFT JOIN @Financials f ON i.CustomerID = f.CustomerID and i.MetricID = f.MetricID and i.PeriodID = f.PeriodID
GROUP BY
    i.CustomerID,
    i.PeriodID,
    i.MetricID

@bluefeet - the desired result is as per the last select statement. I need a record for each Customer/Period/Metric combination with the Financial data from the Financials temp table if it exists — Tom, Apr 08 '13 at 17:38
Please use the Cross join syntax rather than the implicit syntax. It will be more obvious to someone in ayear who is adjusting this what was meant. — HLGEM, Apr 08 '13 at 18:17
are you really using table variables with insert statements? or is that just a sample script that we can run without needing the base tables? — jhinkley, Apr 08 '13 at 18:19
You might add primary keys to table variables - [see this question](http://stackoverflow.com/questions/1420897/compound-primary-key-in-table-type-variable). — Nikola Markovinović, Apr 08 '13 at 18:27
@jhinkley - the inserts above are just for the purpose of this demonstration. I'm populating the table variables from other tables. — Tom, Apr 08 '13 at 19:23
@HLGEM - funnily enough, I'm actually a victim of exactly what you're explaining. I'm trying to optimize / rewrite something created by someone else. I'm updating to use CROSS JOIN now, thanks! — Tom, Apr 08 '13 at 20:09

score 1 · Accepted Answer · answered Apr 08 '13 at 18:18

1

I would suggest trying a temp table instead of a table variable for the matrix and then indexing it.

answered Apr 08 '13 at 18:18

HLGEM

94,695
15
113
186

SQL Server : improving efficiency of cross joining to create a matrix result

1 Answers1