Subquery performace - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Subquery performace

Author

Topic

xalpha
Starting Member

13 Posts

Posted - 2012-09-24 : 04:47:59

Hello,
I have a little question about the subquery performace. I have a statement like this on a really big database:

SELECT *
FROM [Users] where User_ID in (SELECT MAX([User_ID]) FROM [Users] GROUP BY USERGROUP)

The result should be the newest user of each usergroup. The result is right but it is really really slow on a big database. Is there a possibility to design this query with a better performace?

Thank you for every hint.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-09-24 : 06:00:51

1. Check there is an index on Users.[User_id]
(Or even better [UserGroup], [User_Id])

2. Try the usual variations:


SELECT *
FROM Users U
WHERE EXISTS
(
	SELECT *
	FROM Users U1
	GROUP BY U1.UserGroup
	HAVING U.[User_Id] = MAX(U1.[User_Id])
);

WITH MaxUsers
AS
(
	SELECT MAX([User_Id]) AS [User_Id]
	FROM Users
	GROUP BY UserGroup
)
SELECT U.*
FROM Users U
	JOIN MaxUsers M
		ON U.[User_Id] = M.[User_Id];

SELECT U.*
FROM Users U
	CROSS APPLY
	(
		SELECT MAX(U1.[User_Id])
		FROM Users U1
		WHERE U1.UserGroup = U.UserGroup
	) D
WHERE U.[User_Id] = D.[User_Id];

xalpha
Starting Member

13 Posts

Posted - 2012-09-24 : 08:34:43

Thank you! I'm very impressed about the speed of your Statement. With my version it was so slowly and now it is completely executed right after clicking the button. A great Solution.

Matthias

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources