query performance question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • inna
    #1

    query performance question

    Hello. I have query performance question.
    I need to optimize procedure

    CREATE PROCEDURE dbo.SECUQUSRCOM PACCES
    @P1 VARCHAR(50),
    @P2 INTEGER
    AS
    DECLARE @IORGANIZATIONI D INTEGER
    EXECUTE dbo.ORGNQGETORG ID @PORGUNIQUEID = @IORGANIZATIONI D OUTPUT

    SELECT TSECCOMP.ID,
    CASE TSECPROFILEGRP. ACCESSTYPE
    WHEN -1 THEN
    CASE TSECCLASS.DEFAU LTACCESS
    WHEN -1 THEN
    CASE TSECGROUPCOMP.D EFAULTACCESS
    WHEN -1 THEN
    TSECCOMP.DEFAUL TACCESS
    ELSE
    TSECGROUPCOMP.D EFAULTACCESS
    END

    ELSE
    TSECCLASS.DEFAU LTACCESS
    END
    ELSE TSECPROFILEGRP. ACCESSTYPE
    END AS EXPR1
    FROM TSECCOMP
    INNER JOIN ((TSECPROFILE
    INNER JOIN (TSECCLASS
    INNER JOIN TSECPROFILEGRP
    ON TSECCLASS.UNIQU EID = TSECPROFILEGRP. SECURITYGROUPID )
    ON TSECPROFILE.UNI QUEID = TSECPROFILEGRP. PROFILEID) INNER JOIN
    TSECGROUPCOMP ON TSECCLASS.UNIQU EID = TSECGROUPCOMP.S ECURITYGROUPID)
    ON TSECCOMP.UNIQUE ID = TSECGROUPCOMP.S ECCOMPID
    WHERE
    (
    CASE TSECPROFILEGRP. ACCESSTYPE
    WHEN -1 THEN
    CASE TSECCLASS.DEFAU LTACCESS
    WHEN -1 THEN
    CASE TSECGROUPCOMP.D EFAULTACCESS
    WHEN -1 THEN
    TSECCOMP.DEFAUL TACCESS
    ELSE
    TSECGROUPCOMP.D EFAULTACCESS
    END
    ELSE
    TSECCLASS.DEFAU LTACCESS
    END
    ELSE TSECPROFILEGRP. ACCESSTYPE
    END > 0 ) AND (TSECPROFILE.KE YVALUE=@P1) AND ( TSECCOMP.TYPE =@P2)
    AND TSECCOMP.ORGANI ZATIONID = @IORGANIZATIONI D
    GO
    Thank you In advance.
    Tags: None
    • Gert-Jan Strik
      #2
      Re: query performance question

      Make sure you have indexed the join keys. You can try running the Index
      Tuning Wizard for advice.

      Gert-Jan


      inna wrote:[color=blue]
      >
      > Hello. I have query performance question.
      > I need to optimize procedure
      >
      > CREATE PROCEDURE dbo.SECUQUSRCOM PACCES
      > @P1 VARCHAR(50),
      > @P2 INTEGER
      > AS
      > DECLARE @IORGANIZATIONI D INTEGER
      > EXECUTE dbo.ORGNQGETORG ID @PORGUNIQUEID = @IORGANIZATIONI D OUTPUT
      >
      > SELECT TSECCOMP.ID,
      > CASE TSECPROFILEGRP. ACCESSTYPE
      > WHEN -1 THEN
      > CASE TSECCLASS.DEFAU LTACCESS
      > WHEN -1 THEN
      > CASE TSECGROUPCOMP.D EFAULTACCESS
      > WHEN -1 THEN
      > TSECCOMP.DEFAUL TACCESS
      > ELSE
      > TSECGROUPCOMP.D EFAULTACCESS
      > END
      >
      > ELSE
      > TSECCLASS.DEFAU LTACCESS
      > END
      > ELSE TSECPROFILEGRP. ACCESSTYPE
      > END AS EXPR1
      > FROM TSECCOMP
      > INNER JOIN ((TSECPROFILE
      > INNER JOIN (TSECCLASS
      > INNER JOIN TSECPROFILEGRP
      > ON TSECCLASS.UNIQU EID = TSECPROFILEGRP. SECURITYGROUPID )
      > ON TSECPROFILE.UNI QUEID = TSECPROFILEGRP. PROFILEID) INNER JOIN
      > TSECGROUPCOMP ON TSECCLASS.UNIQU EID = TSECGROUPCOMP.S ECURITYGROUPID)
      > ON TSECCOMP.UNIQUE ID = TSECGROUPCOMP.S ECCOMPID
      > WHERE
      > (
      > CASE TSECPROFILEGRP. ACCESSTYPE
      > WHEN -1 THEN
      > CASE TSECCLASS.DEFAU LTACCESS
      > WHEN -1 THEN
      > CASE TSECGROUPCOMP.D EFAULTACCESS
      > WHEN -1 THEN
      > TSECCOMP.DEFAUL TACCESS
      > ELSE
      > TSECGROUPCOMP.D EFAULTACCESS
      > END
      > ELSE
      > TSECCLASS.DEFAU LTACCESS
      > END
      > ELSE TSECPROFILEGRP. ACCESSTYPE
      > END > 0 ) AND (TSECPROFILE.KE YVALUE=@P1) AND ( TSECCOMP.TYPE =@P2)
      > AND TSECCOMP.ORGANI ZATIONID = @IORGANIZATIONI D
      > GO
      > Thank you In advance.[/color]

        Comment

        • John Bell
          #3
          Re: query performance question

          Hi

          Check out the query execution plan

          http://www.sql-server-performance.com/query_execution_plan_analysis.asp


          http://www.sql-server-performance.com/transact_sql.asp


          John


          "inna" <mednyk@hotmail .com> wrote in message
          news:347a408b.0 309131204.19c07 4e8@posting.goo gle.com...[color=blue]
          > Hello. I have query performance question.
          > I need to optimize procedure
          >
          > CREATE PROCEDURE dbo.SECUQUSRCOM PACCES
          > @P1 VARCHAR(50),
          > @P2 INTEGER
          > AS
          > DECLARE @IORGANIZATIONI D INTEGER
          > EXECUTE dbo.ORGNQGETORG ID @PORGUNIQUEID = @IORGANIZATIONI D OUTPUT
          >
          > SELECT TSECCOMP.ID,
          > CASE TSECPROFILEGRP. ACCESSTYPE
          > WHEN -1 THEN
          > CASE TSECCLASS.DEFAU LTACCESS
          > WHEN -1 THEN
          > CASE TSECGROUPCOMP.D EFAULTACCESS
          > WHEN -1 THEN
          > TSECCOMP.DEFAUL TACCESS
          > ELSE
          > TSECGROUPCOMP.D EFAULTACCESS
          > END
          >
          > ELSE
          > TSECCLASS.DEFAU LTACCESS
          > END
          > ELSE TSECPROFILEGRP. ACCESSTYPE
          > END AS EXPR1
          > FROM TSECCOMP
          > INNER JOIN ((TSECPROFILE
          > INNER JOIN (TSECCLASS
          > INNER JOIN TSECPROFILEGRP
          > ON TSECCLASS.UNIQU EID = TSECPROFILEGRP. SECURITYGROUPID )
          > ON TSECPROFILE.UNI QUEID = TSECPROFILEGRP. PROFILEID) INNER JOIN
          > TSECGROUPCOMP ON TSECCLASS.UNIQU EID = TSECGROUPCOMP.S ECURITYGROUPID)
          > ON TSECCOMP.UNIQUE ID = TSECGROUPCOMP.S ECCOMPID
          > WHERE
          > (
          > CASE TSECPROFILEGRP. ACCESSTYPE
          > WHEN -1 THEN
          > CASE TSECCLASS.DEFAU LTACCESS
          > WHEN -1 THEN
          > CASE TSECGROUPCOMP.D EFAULTACCESS
          > WHEN -1 THEN
          > TSECCOMP.DEFAUL TACCESS
          > ELSE
          > TSECGROUPCOMP.D EFAULTACCESS
          > END
          > ELSE
          > TSECCLASS.DEFAU LTACCESS
          > END
          > ELSE TSECPROFILEGRP. ACCESSTYPE
          > END > 0 ) AND (TSECPROFILE.KE YVALUE=@P1) AND ( TSECCOMP.TYPE =@P2)
          > AND TSECCOMP.ORGANI ZATIONID = @IORGANIZATIONI D
          > GO
          > Thank you In advance.[/color]


            Comment

            Previous template Next
            Working...