Optimizing a TRANSACT SQL statement [closed]

Question

I'm in need to optimize the following TRANSACT SQL statement :

SELECT Id, Type, StartTime, Amount, CurrencyCode, CorrelationStatus, ProviderId
FROM (
        SELECT UserId, Id, {=charge} Type, StartTime, Amount, CurrencyCode, CorrelationStatus, ProviderId
        FROM charge
        WHERE (@Type IS NULL OR @Type = {=charge})

        UNION ALL

        SELECT UserId, Id, {=recharge} Type, StartTime, Amount, CurrencyCode, CorrelationStatus, ProviderId
        FROM recharges
        WHERE (@Type IS NULL OR @Type = {=recharge})
     ) AS T
    WHERE UserId = @UserId
    AND StartTime BETWEEN @StartDate AND @EndDate
    AND (@ProviderId IS NULL OR ProviderId = @ProviderId)
    AND (@FilterCorrelationStatus = 0 OR CorrelationStatus IN @CorrelationStatuses)

    ORDER BY StartTime DESC
    OFFSET (@PageIndex - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;

Can you give me some suggestions? Thanks, Simone

Answer 1

As this is a application SQL (executed by stored proc?) returning DBSet (or similar) your choices are limited. I'd start by looking at the execution plans - look out for "Scans" - this would indicate a missing index.

use SET STATISTICS IO ON to see how many reads are being undertaken.

I would also look for the order of execution, you might be better off including the filter criteria in each of the unions, rather than having an outer

At a guess, though each of the UNION clauses is returning a large set of data, before then applying the filter.

Try something like this:

SELECT Id, Type, StartTime, Amount, CurrencyCode, CorrelationStatus, ProviderId 
FROM (
    SELECT UserId, Id, {=charge} Type, StartTime, Amount, CurrencyCode, CorrelationStatus, ProviderId
    FROM charge
    WHERE
        UserId = @UserId
        AND StartTime BETWEEN @StartDate AND @EndDate
        AND (@ProviderId IS NULL OR ProviderId = @ProviderId)
        AND (@FilterCorrelationStatus = 0 OR CorrelationStatus IN @CorrelationStatuses) 
        AND (@Type IS NULL OR @Type = {=charge})

    UNION ALL

    SELECT UserId, Id, {=recharge} Type, StartTime, Amount, CurrencyCode, CorrelationStatus, ProviderId
    FROM recharges
    WHERE
        UserId = @UserId
        AND StartTime BETWEEN @StartDate AND @EndDate
        AND (@ProviderId IS NULL OR ProviderId = @ProviderId)
        AND (@FilterCorrelationStatus = 0 OR CorrelationStatus IN @CorrelationStatuses)
        AND (@Type IS NULL OR @Type = {=recharge})
 ) AS T

ORDER BY StartTime DESC
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

An alternate approach (last resort?!) is to create a schema bound indexed view. In other words create a schema bound view of CREATE VIEW xxx WITH SCHEMABINDING AS SELECT fields FROM charge UNION ALL SELECT fields recharges. You can then add indexes to the view.