Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Answered: Fine tune a query

    Hi All

    Please help me to tune this query
    please tell me if see any places to improve its performance

    Code:
    SELECT
    	tt_user.user_id 
    FROM
    	tt_user 
    WHERE
    	org_id=15900 AND
    	(user_id IN (	SELECT
    						DISTINCT tl_user_role.user_id 
    					FROM
    						tl_user_role 
    						INNER JOIN tl_role_perm 
    						ON tl_role_perm.role_id=tl_user_role.role_id AND
    						tl_role_perm.perm_id=92 ) OR
    	user_id IN (SELECT
    					DISTINCT tl_user_group.user_id 
    				FROM
    					tl_user_group 
    				WHERE
    					group_id IN (	SELECT
    										DISTINCT tl_group_role.group_id 
    									FROM
    										tl_group_role 
    										INNER JOIN tl_user_group 
    										ON tl_user_group.group_id=tl_group_role.
    										group_id 
    									WHERE
    										role_id IN (SELECT
    														role_id 
    													FROM
    														tl_role_perm 
    													WHERE
    														perm_id=92) ) ) )

  2. Best Answer
    Posted by gvee

    "Here's a start at re-factoring:
    Code:
    DECLARE @org_id  int = 15900
          , @perm_id int = 92
    ;
    
    ; WITH roles_with_permission AS (
      SELECT role_id
      FROM   t1_role_perm
      WHERE  perm_id = @perm_id
    )
    , groups_with_permission AS (
      SELECT group_id
      FROM   tl_group_role
      WHERE  EXISTS (
               SELECT *
               FROM   roles_with_permission
               WHERE  role_id = tl_group_role.role_id
             )
    )
    , users_in_those_roles AS (
      SELECT user_id
      FROM   tl_user_role
      WHERE  EXISTS (
               SELECT *
               FROM   roles_with_permission
               WHERE  role_id = tl_user_role.role_id
             )
    )
    , users_in_those_groups AS (
      SELECT user_id
      FROM   tl_user_group
      WHERE  EXISTS (
               SELECT *
               FROM   groups_with_permission
               WHERE  group_id = tl_user_group.group_id
             )
    )
    , users AS (
      SELECT user_id
      FROM   tt_user
      WHERE  org_id = @org_id
    )
    SELECT user_id
    FROM   users
    WHERE  EXISTS (
             SELECT *
             FROM   users_in_those_roles
             WHERE  user_id = users.user_id
           )
    OR     EXISTS (
             SELECT *
             FROM   users_in_those_groups
             WHERE  user_id = users.user_id
           )
    More verbose than it needs to be, but hopefully this is nice and clear and easy to understand."


  3. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should be using EXISTS instead of IN, for a start.

    P.S. no need for DISTINCT in your IN statements.
    Last edited by gvee; 02-13-15 at 06:03.
    George
    Home | Blog

  4. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's a start at re-factoring:
    Code:
    DECLARE @org_id  int = 15900
          , @perm_id int = 92
    ;
    
    ; WITH roles_with_permission AS (
      SELECT role_id
      FROM   t1_role_perm
      WHERE  perm_id = @perm_id
    )
    , groups_with_permission AS (
      SELECT group_id
      FROM   tl_group_role
      WHERE  EXISTS (
               SELECT *
               FROM   roles_with_permission
               WHERE  role_id = tl_group_role.role_id
             )
    )
    , users_in_those_roles AS (
      SELECT user_id
      FROM   tl_user_role
      WHERE  EXISTS (
               SELECT *
               FROM   roles_with_permission
               WHERE  role_id = tl_user_role.role_id
             )
    )
    , users_in_those_groups AS (
      SELECT user_id
      FROM   tl_user_group
      WHERE  EXISTS (
               SELECT *
               FROM   groups_with_permission
               WHERE  group_id = tl_user_group.group_id
             )
    )
    , users AS (
      SELECT user_id
      FROM   tt_user
      WHERE  org_id = @org_id
    )
    SELECT user_id
    FROM   users
    WHERE  EXISTS (
             SELECT *
             FROM   users_in_those_roles
             WHERE  user_id = users.user_id
           )
    OR     EXISTS (
             SELECT *
             FROM   users_in_those_groups
             WHERE  user_id = users.user_id
           )
    More verbose than it needs to be, but hopefully this is nice and clear and easy to understand.
    Last edited by gvee; 02-13-15 at 07:59.
    George
    Home | Blog

  5. #4
    Join Date
    Sep 2011
    Posts
    85
    Thanx for the reply George

    I try earlier with EXISTS and I'm getting more data that the IN statement
    also the code you posted here will give me some syntax errors which i cannot figure how to rectify.
    I'm still new to SQL server and learning it

  6. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What are the syntax errors?
    What version of SQL Server are you using?
    George
    Home | Blog

  7. #6
    Join Date
    Sep 2011
    Posts
    85
    Hi George

    Im using 11.0.3393.0 SP1 Enterprise Edition (64-bit)

    This is the error i got

    Msg 156, Level 15, State 1, Line 24
    Incorrect syntax near the keyword 'SELECT'.
    Msg 102, Level 15, State 1, Line 34
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 46
    Incorrect syntax near ','.

  8. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Updated my query above. Give it another try.
    George
    Home | Blog

  9. #8
    Join Date
    Sep 2011
    Posts
    85
    Hi George

    Thanx for this. I will reply back after comparing this with the original query

  10. #9
    Join Date
    Sep 2011
    Posts
    85
    Hi George

    Thanx for this. This takes less cost than my original query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •