2

I have this table:

CREATE TABLE transactions
(
    id                                    NUMERIC(20, 0)              NOT NULL PRIMARY KEY,
    amount                                NUMERIC(18, 2) DEFAULT NULL NULL,
    -- Some 100 columns
    customer_msisdn                       VARCHAR(255)   DEFAULT NULL              NULL,
    customer_email                        VARCHAR(255)   DEFAULT NULL              NULL,
    payment_date                          DATETIME2                   NOT NULL
);

CREATE NONCLUSTERED INDEX msisdn_idx ON transactions (customer_msisdn, payment_date, id);
CREATE NONCLUSTERED INDEX email_idx ON transactions (customer_email, payment_date, id);

I'm indexing around ~1m rows a month. Very frequently, I need to select last 3 month's transactions per customer_msisdn or per customer_email which is 99% time 50 - 1000 records.

Here's my query for a bit more insight:

SELECT t.*
FROM transactions t
         JOIN (SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate
               UNION
               SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

And I feel like since the :endDate is always now (and when not, can tolerate fault) and :startDate is always three months back, I have some room for improvement. Here's what I thougt:

Create an indexed view with a filter on payment_date:

CREATE VIEW [dbo].transactions_iv
    WITH SCHEMABINDING AS
SELECT [t].id,
       -- All the rows
       [t].customer_msisdn,
       [t].customer_phone,
       [t].payment_date
FROM [dbo].[transactions] [t]
WHERE [t].payment_date >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP);

and my indexes:

CREATE NONCLUSTERED INDEX msisdn_iv_idx ON transactions_iv (customer_msisdn, id);
CREATE NONCLUSTERED INDEX phone_iv_idx ON transactions_iv (customer_phone, id);

and drop the AND t.payment_date >= :startDate AND t.payment_date < :endDate clauses altogether from the query. The query becomes:

SELECT t.*
FROM transactions_iv t
         JOIN (SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
               UNION
               SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

Since the view only has last 3 month's transactions, I'm assuming so will the indexes. Is this assumption correct correct? Would the index be updated only to cover last 3 month's records and would I get my performance boost?


Another alternative is:

  1. Create another identical table,
  2. Populate it with a trigger on the main table
  3. With a cron job, delete records older than 3 months every night.

How would this option compare to the previous one?

2
  • I see no obvious reason why you use a UNION at all nor why you have no index on payment_date - which seems like an obvious candidate here.
    – SMor
    Sep 3 at 11:27
  • 3
    @SMor Presumably because the payment date is not very selective but email/msisdn + date is. Having an inequality in the first key of an index prevents an equality seek on e.g. email or msisdn. The union is a standard way to rewrite an OR to get better index selection from the optimizer.
    – Paul White
    Sep 3 at 11:44
4

Your indexed view cannot be created as written because it is nondeterministic. Rows would fall out of the view as time passes.

Your current query is likely producing an execution plan like:

Current plan

Leaving aside the question of separate tables and indexed views for a moment, give the following minor rewrite (using existing indexes) a shot:

SELECT t.*
FROM transactions t
JOIN 
(
    SELECT t.id, t.payment_date
    FROM transactions t
    WHERE
        t.customer_email = @customerEmail
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
    UNION
    SELECT t.id, t.payment_date
    FROM transactions t
    WHERE 
        t.customer_msisdn = @customerMsisdn
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
) AS filtered_transactions
    ON filtered_transactions.id = t.id
ORDER BY 
    filtered_transactions.payment_date;

Notice the extra columns t.payment_date in the UNION, and the ORDER BY has changed to filtered_transactions.payment_date. This is semantically no different to your query, but it will help the optimizer find the better plan.

You ought to get quite an efficient execution plan like:

Desired plan shape

The optimizer really ought to select that plan shape (or a parallel version perhaps) but if not, one or more hints might be needed. An extreme example:

SELECT t.*
FROM
(
    SELECT t.id, t.payment_date
    FROM transactions t
        WITH (FORCESEEK(email_idx(customer_email,payment_date)))
    WHERE
        t.customer_email = @customerEmail
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
    UNION
    SELECT t.id, t.payment_date
    FROM transactions t
        WITH (FORCESEEK(msisdn_idx(customer_msisdn,payment_date)))
    WHERE 
        t.customer_msisdn = @customerMsisdn
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
) AS filtered_transactions
JOIN dbo.transactions AS t
    ON t.id = filtered_transactions.id
ORDER BY 
    filtered_transactions.payment_date
OPTION (FORCE ORDER, LOOP JOIN, MERGE UNION);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.