SQL Server Performance

Testing performance(proactive)

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Reddy, Jun 4, 2008.

  1. Reddy New Member

    I created a view with cte

    WITH UserGroupUserGroups AS (
    SELECT
    CAST(NULL AS UNIQUEIDENTIFIER) AS ParentUserGroupID,
    ug.UserGroupID AS ChildUserGroupID
    FROM
    UserSecurity.UserGroups AS ug
    WHERE
    NOT EXISTS(SELECT 'X' FROM UserSecurity.UserGroupUserGroups AS ugug WHERE ugug.ChildUserGroupID = ug.UserGroupID)
    UNION ALL SELECT
    parentGroups.ChildUserGroupID AS ParentUserGroupID,
    childGroups.ChildUserGroupID AS ChildUserGroupID
    FROM
    UserGroupUserGroups AS parentGroups
    INNER JOIN UserSecurity.UserGroupUserGroups AS childGroups ON childGroups.ParentUserGroupID = parentGroups.ChildUserGroupID
    )
    SELECT * FROM UserGroupUserGroups AS ugugf WHERE ParentUserGroupID IS NOT NULL
    As of now i dont have any data into it but i expect millinons of records once this query is implemented. How can i test perofrmance in before so tht i dont get into problems with real data.
  2. madhuottapalam New Member

    Check the indexes available on the table. In the first look you may have Clustered index scan or table scan because you are using NOT Exists.
    Madhu
  3. satya Moderator

Share This Page