CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Berend
Berend

Reputation: 1079

Adding an extra condition to a group-by query makes it slow

I have a query that selects a maximum value for a certain group, e.g.

select * 
from mytable 
where value in (select max(value) from mytable group by mygroupfield)

This is fast enough (~ 0.1 sec), but when I add an extra condition, e.g.

select * 
from mytable 
where value in (select max(value) from mytable group by mygroupfield)
AND mygroupfield='something'

the query becomes very slow (~ 4-5 seconds)

Both value and mygroupfield are indexed (non unique, non clustered, if it matters). This table contains ~ 40,000 records.

I know I can do this: where value in (select max(value) where mygroupfield='something') (which is also fast) but due to our architecture, that is not an option right now.

Is there a way to speed up this query?

Upvotes: 0

Views: 98

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

I would suggest a correlated subquery:

select t.* 
from mytable t
where t.value = (select max(t2.value)
                 from mytable t2
                 where t2.mygroupfield = t.mygroupfield
                ) and
      t.mygroupfield = 'something';

In particular, this can take advantage of an index on mytable(mygroupfield, value)

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46213

Try a correlated subquery like the example below.

SELECT a.* 
FROM mytable AS a
WHERE value IN (
    SELECT MAX(value) 
    FROM mytable AS b
    WHERE b.mygroupfield=a.mygroupfield 
    GROUP by mygroupfield
    )
    AND mygroupfield='something';

This index may help as well:

CREATE INDEX idx ON dbo.mytable(mygroupfield) INCLUDE(value);

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try using row_number() though not sure whether it'll serve your purpose or not

with cte as
(
select *,row_number() over(partition by mygroupfield order by value desc) as rn
from mytable 
where mygroupfield='something'
) 

select * from cte where rn=1

Upvotes: 0

Related Questions