query performance question

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

CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
@.P1 VARCHAR(50),
@.P2 INTEGER
AS
DECLARE @.IORGANIZATIONID INTEGER
EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT

SELECT TSECCOMP.ID,
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END

ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END AS EXPR1
FROM TSECCOMP
INNER JOIN ((TSECPROFILE
INNER JOIN (TSECCLASS
INNER JOIN TSECPROFILEGRP
ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
WHERE
(
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END
ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
GO
Thank you In advance.Make sure you have indexed the join keys. You can try running the Index
Tuning Wizard for advice.

Gert-Jan

inna wrote:
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.|||Hi

Check out the query execution plan

http://www.sql-server-performance.c...an_analysis.asp

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

John

"inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0309131204.19c074e8@.posting.google.c om...
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.