Trouble with TOP when data is less than limit

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trouble with TOP when data is less than limit

Author

Topic

shinilkp
Starting Member

2 Posts

Posted - 2008-12-31 : 10:02:20

This is my query which is creating problem for me
SELECT top 5
m.messageid, m.parentid
FROM
[message] m
WHERE
parentid in (
SELECT m.messageid
FROM [message] m join [groupmessage] gm on m.messageid = gm.messageid join [group] g on gm.groupid = g.groupid and
gm.reftypeid = 'C' and m.inactive = 0 and g.portalid=20 and g.grouptypeid=0 and g.groupcategoryid in (22)) OR
m.messageid in (
SELECT m.messageid
FROM [message] m join [groupmessage] gm on m.messageid = gm.messageid join [group] g on gm.groupid = g.groupid and
gm.reftypeid = 'C' and m.inactive = 0 and g.portalid=20 and g.grouptypeid=0 and g.groupcategoryid in (22))
ORDER BY m.updatedt desc

The problem here is If the m.messageid in the last select is less than 5 (i am taking top 5), its taking a long time. But if I remove Top 5 or if there are more than 5 messageid for groupcategoryid = 22, its like less than a sec which is expected. What am I missing here?
I even thought of seperating the two queries and union all it. But its little slower when the data is large. Any help is appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 10:35:18

When setting TOP 5, the query optimizer needs to deal with ALL records in order to sort them.
Unless you have an INDEX over updateDT column. which makes the TOP 5 comparison much faster.

E 12°55'05.63"
N 56°04'39.26"