July 24, 2009 at 6:59 am
Hello all -
I do not want to use cursor in the following query. Could you please help me how to optimize this one. Using cursor has hit the performance and it is not avoidable.
Regards,
Sanjeev.
declare finalcursor CURSOR FOR
select
distinct
[User],
[Transaction],
APMA.AttributeValue [UserType],
TM.ID [BrConnectionID]
from
#TCodeTemp t,
ApplicationPrincipalMappingAttributes APMA,
ManagedApplication MA,
TMBizRightsConnections TM,
ManagedApplicationTMConnectionMapping MATM,
(SELECT Items FROM dbo.Split (@BRConnectionID,@seperator)) AA
where
APMA.AttributeName = 'User Type'
AND APMA.AttributeValue IN ('Dialog User', 'BDC User')
AND dbo.LookupApplicationPrincipalName(APMA.ApplicationPrincipalID) = t.[User]
AND ApplicationName = MA.Name
AND APMA.ApplicationID = MA.ID
AND TM.ID = MATM.TMBizRightsConnectionID
AND MA.ID = MATM.ManagedApplicationID
--AND TM.ID = ISNULL(@BRConnectionID, TM.ID)
AND AA.Items = TM.ID
OPEN finalcursor
FETCH NEXT FROM finalcursor INTO @User,@Transaction,@UserType, @BrConnectionID
WHILE @@FETCH_STATUS = 0
BEGIN
IF((@Transaction = '*') OR ((CHARINDEX('<=>', @Transaction) > 0 )) OR (@Transaction LIKE '_%*'))
BEGIN
INSERT INTO #FinalTable
SELECT
TCODE,
@User,
@UserType,
@BrConnectionID
FROM
(SELECT distinct TCODE from #SensitiveBASISTransactionsTemp ) X
WHERE
CASE
WHEN @Transaction = '*' THEN 1
WHEN CHARINDEX('<=>', @Transaction) > 0
THEN
dbo.IsTCodeInRange(TCODE, TCODE, SUBSTRING(@Transaction, 1, CHARINDEX('<=>', @Transaction) - 1) ,
SUBSTRING(@Transaction, CHARINDEX('<=>', @Transaction) + LEN('<=>'), LEN( @Transaction ) ))
WHEN @Transaction LIKE '_%*'
THEN
dbo.IsTCodeInRange(TCODE, TCODE, @Transaction , @Transaction)
END = 1
END
ELSE
BEGIN
INSERT INTO #FinalTable
SELECT
@Transaction,
@User,
@UserType,
@BrConnectionID
END
-- select * from #FinalTable
FETCH NEXT FROM finalcursor INTO @User,@Transaction,@UserType, @BrConnectionID
END
CLOSE finalcursor
DEALLOCATE finalcursor
July 24, 2009 at 8:30 am
lots of possible improvements i think are possible.
first, you can get rid of the cursor. that will be a massive improvement.
to help, we need a few things to get a clearer picture.
1. the definition of the temp table #TCodeTemp.
2. the function dbo.LookupApplicationPrincipalName(APMA.ApplicationPrincipalID) needs to be removed...it clearly is just looking up a named based on an ID, right? that can be replaced with a JOINed table instead. show us the definition of the function.
3. this line:AND ApplicationName = MA.Name --is the column ApplicationName from APMA.ApplicationName?
Lowell
July 24, 2009 at 8:36 am
also, i'm rewringing the sql to have proper ANSi joins, and I do not see any relationship for the aliased table APMA:
select distinct
[User],
[Transaction],
APMA.AttributeValue [UserType],
TM.ID [BrConnectionID]
from
#TCodeTemp t
INNER JOIN (SELECT ApplicationPrincipalID, FROM SomePrincipalNameTable) FN
ON FN.[User]= t.[User]
INNER JOIN ApplicationPrincipalMappingAttributes APMA
ON ????????????????????????????????????????????????
INNER JOIN ManagedApplication MA
ON APMA.ApplicationName = MA.Name
AND APMA.ApplicationID = MA.ID
INNER JOIN ManagedApplicationTMConnectionMapping MATM
ON MA.ID = MATM.ManagedApplicationID
INNER JOIN TMBizRightsConnections TM
ON MATM.TMBizRightsConnectionID = TM.ID
INNER JOIN (SELECT Items FROM dbo.Split (@BRConnectionID,@seperator)) AA
ON TM.ID =AA.Items
WHERE APMA.AttributeName = 'User Type'
AND APMA.AttributeValue IN ('Dialog User', 'BDC User')
Lowell
July 24, 2009 at 8:37 am
Why do you say the cursor is unavoidable?
Can you explain what this does?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2009 at 12:05 am
My apologizes for not being clear in my comments.. We can very well avoid the cursor.
Thanks,
Sanjeev.
July 27, 2009 at 12:47 am
Hi -
Following are the answers to your questions:
1. the definition of the temp table #TCodeTemp.
= create table #TCodeTemp
(
[User] Nvarchar(250) COLLATE database_default ,
Role Nvarchar(250) COLLATE database_default ,
RoleType Nvarchar(250) COLLATE database_default ,
AuthorizationName Nvarchar(300) COLLATE database_default ,
ParentAuthName nvarchar(300) COLLATE database_default ,
ApplicationName nvarchar(300) COLLATE database_default ,
[Transaction] nvarchar(300) COLLATE database_default ,
UserGroup nvarchar(300) COLLATE database_default ,
[Description] nvarchar(1000) COLLATE database_default
)
2. the function dbo.LookupApplicationPrincipalName(APMA.ApplicationPrincipalID) needs to be removed...it clearly is just looking up a named based on an ID, right? that can be replaced with a JOINed table instead. show us the definition of the function.
= We can remove this...thnx for pointing it out
3. this line:AND ApplicationName = MA.Name --is the column ApplicationName from APMA.ApplicationName?
= No, it is from #TcodeTemp table
Thanks,
Sanjeev.
July 27, 2009 at 4:01 am
something like this is pretty close; I don't know the sql to get the temp table, and I'm not sure of the table that the old function uses.
the sql below is incomplete, we still need to add WHERE statements and Case statements for the [Transaction] column, and there are two other tables to join against:#SensitiveBASISTransactionsTemp and the table the function dbo.IsTCodeInRange was using; that table needs to be joined and the function removed as well.
Lets get the select below going before going further, and then add the next two tables to get your final result the cursor was selecting into the temp table #FinalTable.
this should get the results, i think. after you change it to have the missing details, post the actual execution plan as a .sqlplan here, and we can give you suggestions to further improve the performance of this query as well.
SELECT DISTINCT
t.[User],
t.[Transaction],
APMA.AttributeValue [UserType],
TM.ID [BrConnectionID]
FROM
(SELECT
[User],
[Role],
[RoleType],
[AuthorizationName],
[ParentAuthName],
[ApplicationName],
[Transaction],
[UserGroup],
[Description]
From SomeTables --don't know the source
Where 1 = 1 --don't know the real conditions
) t
INNER JOIN (SELECT
ApplicationPrincipalID,
FROM SomePrincipalNameTable --what is the real table the function used to use?
) FN
ON FN.[User]= t.[User]
INNER JOIN ManagedApplication MA
ON t.ApplicationName = MA.Name
INNER JOIN ApplicationPrincipalMappingAttributes APMA
ON MA.ID = APMA.ApplicationID
INNER JOIN ManagedApplicationTMConnectionMapping MATM
ON MA.ID = MATM.ManagedApplicationID
INNER JOIN TMBizRightsConnections TM
ON MATM.TMBizRightsConnectionID = TM.ID
INNER JOIN (SELECT Items FROM dbo.Split (@BRConnectionID,@seperator)) AA
ON TM.ID =AA.Items
WHERE APMA.AttributeName = 'User Type'
AND APMA.AttributeValue IN ('Dialog User', 'BDC User')
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply