468,770 Members | 2,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,770 developers. It's quick & easy.

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.
Jul 20 '05 #1
2 1585
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.

Jul 20 '05 #2
Hi

Check out the query execution plan

http://www.sql-server-performance.co...n_analysis.asp

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

John
"inna" <me****@hotmail.com> wrote in message
news:34**************************@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.

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by phlype.johnson | last post: by
1 post views Thread by Jimbo | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.