Query Help - 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)

Query Help

Author

Topic

Sanatan
Starting Member

19 Posts

Posted - 2010-04-30 : 13:08:33

Hi I have a User Table as below with 300,000 rows.

UserID ManagerID
A1 B1
A2 B1
B1 C1

I am building a view to where one column will represent if a user is a Manager himself. e.g. View will look as below.

UserID IsManager (bit)
A1 0
A2 0
B1 1

I am current using two approaches but both adding big performance hit to the view.
1) Using CTE
with usrmap as
(
SELECT UserID,
(CASE (SELECT count(*) FROM UserTable mgrtbl WHERE mgrtbl .ManagerID = tbl.UserID) WHEN 0 THEN 0 ELSE 1 END) AS IsManager
FROM UserTable tbl
)

2) Using the same CASE statement directly in the view select clause instead of CTE.

I am thinking if there is a way to just find 1 user whose ManagerID = Current UserID instead of using Count(*). The later I think searches the entire table and returns a count, which is adding to the performance hit. Can someone suggest query or another approach to do this?

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-30 : 13:19:18

Try this:


SELECT T1.UserID, ISNULL(T2.IsManager, CAST(0 AS BIT)) AS IsManager
  FROM UserTable AS T1
       OUTER APPLY
       (SELECT TOP(1) CAST(1 AS BIT) AS IsManager
          FROM UserTable AS T2
         WHERE T2.ManagerID = T1.UserID) AS T2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 04:25:56

[code]SELECT t.*,
CASE WHEN ISNULL(t1.cnt,0) = 0 THEN 0 ELSE 1 END AS IsManager
FROM TAble t
OUTER APPLY (SELECT COUNT(1) AS cnt
FROM Table
WHERE ManagerID = t.UserID
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-02 : 05:01:32

Just as comment and because your main problem is performance I should mention that my previous query (and visakh query do almost the same as mine) is not the most optimized query. So you can try the following query as it performs better than previous queries, and I hope that this will solve your problem.


SELECT T1.UserID, CAST(1 AS BIT) AS IsManager
  FROM UserTable AS T1
 WHERE EXISTS(SELECT *
                FROM UserTable AS T2
               WHERE T2.ManagerId = T1.UserId)

 UNION ALL

SELECT T1.UserID, CAST(0 AS BIT)
  FROM UserTable AS T1
 WHERE NOT EXISTS(SELECT *
                    FROM UserTable AS T2
                   WHERE T2.ManagerID = T1.UserID)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-02 : 06:38:23

This will result in less IO then previous methods posted.

SELECT DISTINCT Tu.UserID, 
	CASE WHEN Tm.ManagerID IS NULL THEN 0 
	ELSE 1 
	END AS IsManager
FROM UserTable AS Tu
LEFT JOIN UserTable Tm
	ON Tu.UserID = Tm.ManagerID

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources