5

I have a query with a max in the where clause, which is slow.

select count(*)
from   TableName tbl1
where  tbl1.id = (
                select max(tbl2.id) 
                from TableName tbl2
                where tbl2.companyId = tbl1.companyId)
and    tbl1.active = 2

Please note that both tables are the same.

I rewrote it as:

select count(*) 
from   TableName tbl1 
inner join (
                select max(id) as id, companyId 
                from TableName tbl2 
                where tbl2.active= 2
                group by companyId
        ) tbl2 
        on  tbl2.companyId = tbl1.companyId 
        and tbl2.id=tbl1.id

but number of records is different.

Can anyone say how can I rewrite it for better performance?

  • I've not gone over it in detail so I'll not post a full answer until I have, but on first sight you are filtering on the active flag of the table in the outer query in the first statement and the active flag of the table in the sub-query in the second. That would certainly give different counts for some input data. – David Spillett Apr 24 '16 at 18:14
  • If (companyId, id) is unique, you should get the same number of rows. Is that the case? – Daniel Hutmacher Apr 24 '16 at 19:05
  • Daniel,what should I do if they are not unique? – Allen Apr 24 '16 at 19:30
  • 1
    I think it would be better to explain the result you want so we can work from that rather than a piece of SQL that I'm not 100% sure is getting the result you expect. It would also be useful to list the keys and indexes that are present on relevant columns on the table. – David Spillett Apr 25 '16 at 0:58
  • Is it possible that the number of rows returned by the original query is always 0 or 1? The subquery with MAX will return 1 row. Count on this result will stay one row. Or zero if there is no record. This query seems pretty useless. – Magier Apr 25 '16 at 18:24
7

Summary

Add the index:

CREATE INDEX index_name 
ON TableName 
    (companyid DESC, id DESC) 
INCLUDE 
    (active);

Try this rewrite:

SELECT COUNT_BIG(*) 
FROM
(
    SELECT TOP (9223372036854775807) 
        TN1.active
    FROM TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id) 
            FROM TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

Detail

The original query specification appears to be:

How many rows are there in the table where active = 2; and the id column equals the highest id value across all rows within the same companyid (without the active = 2 restriction, for clarity).

There is a way to get these results with a single scan of a suitable index.

For example, given the table:

CREATE TABLE #TableName
(
    companyid integer NOT NULL,
    id integer NOT NULL,
    active integer NOT NULL
);

A few rows of sample data:

INSERT #TableName
    (companyid, id, active)
VALUES
    (1, 1, 2),
    (1, 2, 2),
    (1, 3, 0),
    (2, 2, 2),
    (2, 3, 2),
    (2, 3, 0);

With the index:

CREATE INDEX index_name 
ON #TableName 
    (companyid DESC, id DESC) 
INCLUDE 
    (active);

A query (based on the original) to find rows without the predicate (active = 2) is:

SELECT
    TN1.active
FROM #TableName AS TN1
WHERE
    TN1.id =
    (
        SELECT MAX(TN2.id) 
        FROM #TableName AS TN2
        WHERE
            TN2.companyid = TN1.companyid
    );

The execution plan for that query features a single scan of the index, despite the query referencing the same table twice:

Segment Top plan

The basic idea is to scan the index in key order, returning the row(s) with the highest id within each group of companyid values.

Unfortunately, we cannot simply add the predicate active = 2 to that query without breaking the optimizer transformation that gives us the single scan. The problem (if you're interested) is that the new predicate gets pushed down the plan tree, resulting in a shape that no longer matches the 'group by top' transform.

One workaround adds a meaningless TOP specification around the part of the query we want transformed, preventing the new predicate being pushed past it:

SELECT COUNT_BIG(*) 
FROM
(
    SELECT TOP (9223372036854775807) 
        TN1.active
    FROM #TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id) 
            FROM #TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

The execution plan is similar to before, with an extra Top (the meaningless, bigint.max one we added), a Filter for the active = 2 predicate, and a final Stream Aggregate to count the rows:

Final plan

I cannot promise you will get this plan shape with the real data, or that it will be faster. It all rather depends on the number and distribution of the rows.

The new Top also has the effect of disfavouring a parallel plan (the group by top is parallel-compatible, but a global top is not; parallelism would have to stop and restart around it).

Nevertheless, until a better option comes along, or more detail is provided, it is something you could try.

1

If I understood your query correctly, the following query should produce the same result, but it may be more efficient, if you create an index on (companyId, id). Columns should be included in the index in this order.

select count(*)
from TableName tbl1
where  
    tbl1.id = 
    (
        select TOP(1) tbl2.id
        from TableName tbl2
        where tbl2.companyId = tbl1.companyId
        order by tbl2.id DESC
    )
    and tbl1.active = 2
;

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.