Complex query taking to long - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex query taking to long

Author

Topic

Joe_sql
Starting Member

4 Posts

Posted - 2008-07-15 : 06:23:34

Hi People

I've just started at a new company and unfortunately the previous database developer left me with pretty complex stored procs that i now have to optimize. can anyone offer me some advice with regards to optimising the following query(taken from a stored proc). Thanks

set nocount on

declare @temp table (
[Account Name] varchar(64),
Source_Destination varchar(64),
Date DateTime,
[Merchant Transaction ID] varchar(64),
[PSL Transaction ID] varchar(64),
[PSL Wallet ID] varchar(64),
[Customer First Name] varchar(64),
[Customer Last Name] varchar(64),-
[Currency] varchar(30),
[Amount] money,
[Description] varchar(255),
[Fee] money
)

insert @temp

select
CA.PublicIdentifier as 'Account Name',
'Destination' as Source_Destination,
AJ.StateTime as 'Date',
MERCHRef.RelationshipIdentifier,
GG.RelationshipIdentifier as 'PSL Transaction ID' ,
MV.PublicIdentifier as 'PSL Wallet ID',
FNAMELNAME2.FirstName as 'FirstName',
FNAMELNAME2.LastName as 'LastName',
ISNULL(dbo.f_ConvertXML(MCL.Data).value ( '(/*[1]/*[local-name()="Destination"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]','varchar(30)' ),
MCL.XML.value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]','varchar(30)' )) AS Currency,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Destination"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','varchar(30)' ),
MCL.XML.value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','varchar(30)' )) AS Amount,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Annotation"])[1]','varchar(30)' ),
MCL.XML.value ( '(/*[1]/*[local-name()="Annotation"])[1]','varchar(30)' )) AS [Description],
Fee.Amount as 'Fee'
from
CardAuthentication CA
inner join
(
select
MR1.MovementIdentifier,
MR1.RelationshipIdentifier,
MI.StateTime
from
MovementRelationship MR1
inner Join(
select
MR.MovementIdentifier,
MR.RelationshipIdentifier,
MS.StateTime as StateTime
from
MovementRelationship MR
inner join (
select
MovementIdentifier,
max(StateTime) as StateTime
from
MovementState
group by
MovementIdentifier )MS
on MR.MovementIdentifier = MS.MovementIdentifier
inner Join (
select
MovementIdentifier
from
MovementRelationship
where
RelationshipIdentifier = @MerchantTransactionId) MR2
on MR2.MovementIdentifier = MR.RelationshipIdentifier
where
MR.Relationship = 'Movement.Logical'
) MI
on MI.MovementIdentifier = MR1.MovementIdentifier
where
MR1.Relationship = 'Movement.Destination.Card'
) AJ
inner join(
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Logical') LM

on LM.MovementIdentifier = AJ.MovementIdentifier

left outer join(

select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Merchant.Reference') MERCHRef

on MERCHRef.MovementIdentifier = LM.RelationshipIdentifier

inner join (
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Transaction') GG

on GG.MovementIdentifier = LM.RelationshipIdentifier

inner join MovementCollection MCL
on MCL.MovementIdentifier = AJ.MovementIdentifier

inner join(
select
MovementIdentifier,
PublicIdentifier
from
CardAuthentication
inner join MovementRelationship
on CardIdentifier = RelationshipIdentifier
where Relationship in ('Movement.Destination.Card','Movement.Source.Card')) MV
on MV.MovementIdentifier = AJ.MovementIdentifier
on AJ.RelationshipIdentifier = CA.CardIdentifier

left outer join (
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Destination.Card'
) SD
on SD.MovementIdentifier = AJ.MovementIdentifier

left outer join (
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Source.Card'
) SD2
on SD2.MovementIdentifier = AJ.MovementIdentifier

left outer join(
select
EntityIdentifier,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(64)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(64)' )) AS [FirstName],
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(64)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(64)' )) AS [LastName]
from
EntityCollection
) EC
on EC.EntityIdentifier = SD.RelationshipIdentifier

left outer join
(
select
PublicIdentifier,
CardIdentifier
from
CardAuthentication
) EC2
on EC2.CardIdentifier = SD2.RelationshipIdentifier

left outer join
(
SELECT
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(30)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(30)' )) AS FirstName,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(30)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(30)' )) AS LastName,
CRS.RelationshipIdentifier,
CRS.CardIdentifier
from
(select
CardIdentifier,
RelationshipIdentifier
from
CardRelationship ) CRS

inner join EntityRelationship
on CRS.RelationshipIdentifier = EntityRelationship.RelationshipIdentifier

inner join EntityCollection ECL
on ECL.EntityIdentifier = EntityRelationship.EntityIdentifier
) FNAMELNAME2
on FNAMELNAME2.CardIdentifier = SD2.RelationshipIdentifier
inner join
(select
RelationshipIdentifier,
MovementIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Physical' ) Ph
on Ph.MovementIdentifier = LM.RelationshipIdentifier
inner join
(select
a.MovementIdentifier,
Sum(ISNULL(a.Amount,0)) as Amount
from
(select
MovementCollection.MovementIdentifier,
MovementClassification.Classification as FeeClassification,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','money' ) ,XML.value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','money' )) AS Amount
from MovementCollection
inner join MovementClassification
on MovementClassification.MovementIdentifier = MovementCollection.MovementIdentifier
inner join (select distinct(MovementIdentifier) from MovementRelationship) MovementRelationship
on MovementRelationship.MovementIdentifier = MovementCollection.MovementIdentifier
where MovementClassification.Classification in ( 'Fee.Acquirer.PercentageFee','Fee.Acquirer.FixedFee','Fee.Account.FixedFee','Fee.Account.PercentageFee','Fee.Account.Fees')
) a
group by a.MovementIdentifier
) Fee
on Fee.MovementIdentifier = Ph.RelationshipIdentifier
where MV.PublicIdentifier like '%@%'

union all

select
EC2.PublicIdentifier as 'Account Name',
'Source' as Source_Destination,
AJ.StateTime as 'Date',
MERCHRef.RelationshipIdentifier,
GG.RelationshipIdentifier as 'PSL Transaction ID' ,
MV.PublicIdentifier as 'PSL Wallet ID',
FNAMELNAME.FirstName as 'FirstName',
FNAMELNAME.LastName as 'LastName',
ISNULL(dbo.f_ConvertXML(MCL.Data).value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]','varchar(30)' ),MCL.XML.value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]','varchar(30)' )) AS Currency,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','varchar(30)' ),MCL.XML.value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','varchar(30)' )) AS Amount,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Annotation"])[1]','varchar(30)' ),MCL.XML.value ( '(/*[1]/*[local-name()="Annotation"])[1]','varchar(30)' )) AS [Description],
Fee.Amount as 'Fee'
from
CardAuthentication CA
inner join(
select
MR1.MovementIdentifier,
MR1.RelationshipIdentifier,
MI.StateTime
from
MovementRelationship MR1
inner Join(
select
MR.MovementIdentifier,
MR.RelationshipIdentifier,
MS.StateTime as StateTime
from
MovementRelationship MR
inner join (
select
MovementIdentifier,
max(StateTime) as StateTime
from
MovementState
group by
MovementIdentifier )MS
on MR.MovementIdentifier = MS.MovementIdentifier
inner Join (
select
MovementIdentifier
from
MovementRelationship
where
RelationshipIdentifier = @MerchantTransactionId) MR2
on MR2.MovementIdentifier = MR.RelationshipIdentifier
where
MR.Relationship = 'Movement.Logical'
) MI
on MI.MovementIdentifier = MR1.MovementIdentifier
where MR1.Relationship = 'Movement.Destination.Card'
) AJ

inner join(
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Logical') LM
on LM.MovementIdentifier = AJ.MovementIdentifier

left outer join(
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Merchant.Reference') MERCHRef
on MERCHRef.MovementIdentifier = LM.RelationshipIdentifier

inner join (
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Transaction') GG
on GG.MovementIdentifier = LM.RelationshipIdentifier

inner join MovementCollection MCL
on MCL.MovementIdentifier = AJ.MovementIdentifier

inner join(
select
MovementIdentifier,
PublicIdentifier
from
CardAuthentication
inner join MovementRelationship
on CardIdentifier = RelationshipIdentifier
where Relationship in ('Movement.Destination.Card','Movement.Source.Card')) MV
on MV.MovementIdentifier = AJ.MovementIdentifier
on AJ.RelationshipIdentifier = CA.CardIdentifier

left outer join (
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Destination.Card'
) SD
on SD.MovementIdentifier = AJ.MovementIdentifier

left outer join (
select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Source.Card'
) SD2
on SD2.MovementIdentifier = AJ.MovementIdentifier

left outer join(
select
EntityIdentifier,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(64)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(64)' )) AS [FirstName],
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(64)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(64)' )) AS [LastName]
from
EntityCollection
) EC
on EC.EntityIdentifier = SD.RelationshipIdentifier

left outer join(
select
PublicIdentifier,
CardIdentifier
from
CardAuthentication
) EC2
on EC2.CardIdentifier = SD2.RelationshipIdentifier

left outer join(
SELECT
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(30)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]','varchar(30)' )) AS FirstName,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(30)' ),XML.value ( '(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]','varchar(30)' )) AS LastName,
CRS.RelationshipIdentifier,
CRS.CardIdentifier
from
( select
CardIdentifier,
RelationshipIdentifier
from
CardRelationship ) CRS
inner join
EntityRelationship
on CRS.RelationshipIdentifier = EntityRelationship.RelationshipIdentifier
inner join
EntityCollection ECL
on ECL.EntityIdentifier = EntityRelationship.EntityIdentifier
) FNAMELNAME
on FNAMELNAME.CardIdentifier = SD.RelationshipIdentifier

inner join
(select
MovementIdentifier,
RelationshipIdentifier
from
MovementRelationship
where
Relationship = 'Movement.Physical' ) Ph
on Ph.MovementIdentifier = LM.RelationshipIdentifier

left outer join(
select
a.MovementIdentifier,
Sum(ISNULL(a.Amount,0)) as Amount
from
(select
MovementCollection.MovementIdentifier,
MovementClassification.Classification as FeeClassification,
ISNULL(dbo.f_ConvertXML(Data).value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','money' ) ,XML.value ( '(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]','money' )) AS Amount
from
MovementCollection
inner join MovementClassification
on MovementClassification.MovementIdentifier = MovementCollection.MovementIdentifier
inner join (
select distinct(MovementIdentifier)
from MovementRelationship) MovementRelationship
on MovementRelationship.MovementIdentifier = MovementCollection.MovementIdentifier
where MovementClassification.Classification in ( 'Fee.Acquirer.PercentageFee','Fee.Acquirer.FixedFee','Fee.Account.FixedFee','Fee.Account.PercentageFee','Fee.Account.Fees')
) a
group by a.MovementIdentifier
) Fee
on Fee.MovementIdentifier = Ph.RelationshipIdentifier
where MV.PublicIdentifier like '%@%'

delete @temp where [PSL Transaction ID] not in
(
select [PSL Transaction ID] from @temp
where [Account Name] in (select * from dbo.fnSplit(@MerchantList,','))
)

select
ll.Source,
mm.Destination,
mm.Date,
mm.[Merchant Transaction ID],
mm.[PSL Transaction ID],
mm.[PSL Wallet ID],
mm.[Customer First Name],
mm.[Customer Last Name],
mm.[Currency],
mm.[Amount],
mm.[Description],
ll.Fee
from
(select
case
when Source_Destination = 'Source' then [Account Name]
end as Source,
case
when Source_Destination = 'Destination' then [Account Name]
end as Destination,
Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description],
Sum(ISNULL([Fee],0)) as 'Fee'
from
@temp
group by
Source_Destination,
[Account Name],
Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description]) ll
inner join
(
select
case
when Source_Destination = 'Source' then [Account Name]
end as Source,
case
when Source_Destination = 'Destination' then [Account Name]
end as Destination,

Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description],
Sum(ISNULL([Fee],0)) as 'Fee'
from @temp
group by
Source_Destination,
[Account Name],
Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description] ) mm
on ll.[PSL Transaction ID] = mm.[PSL Transaction ID]
where
ll.Source IS NOT NULL
and
mm.Destination IS NOT NULL
and
mm.Date IS NOT NULL
and
mm.Amount > mm.Fee
GROUP BY
ll.Source,
mm.Destination,
mm.Date,
mm.[Merchant Transaction ID],
mm.[PSL Transaction ID],
mm.[PSL Wallet ID],
mm.[Customer First Name],
mm.[Customer Last Name],
mm.[Currency],
mm.[Amount],
mm.[Description],
ll.Fee
order by
Date asc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 06:26:09

Ahhh! I knew Shakespeare had left his last play somewhere...

E 12°55'05.25"
N 56°04'39.16"