Hi All,
I am trying to run an SSRS report using the below query. However it is taking longtime to execute and i am getting time out error. I would like to simplify or increase the performance of this query. Can any one of you can assist me how to modify this query. If i remove the xml(policytraveller) part this query seems OK but i need that bit. I appreciate any reply regarding this. Thanks in advance.
Regards,
SG
--Query--
SELECT
AgentProduct.ProductName
,AgentProduct.[Description] as SchemeID
,ISNULL(PolicyHolder.PostcodeZip,' ')as Postcode
,PolicyHolder.EmailAddress
,policytraveller.Travellers
,CONVERT(VARCHAR(10),policy.DateCreated, 103) as PolicyCreationDate
,CONVERT(VARCHAR(10),policy.PolicyStartDate,103) as DepartureDate
,CONVERT(VARCHAR(10),policy.PolicyEndDate,103) as ReturnDate
,Quote.NumberOfTravellers
,quote.NumberOfAdults
,quote.NumberOfChildren
,quote.DestinationCode
,Quote.triptypeid as TripType
,Quote.Relationshiptype as Relation
,quote.EndorsementCodes as Endorsements
,policy.PricePaid as GrossPrice
,agent.AgentName as Agent
,policy.IsCancelled as Cancelled
,isnull(PolicyHolder.AddressLineOne ,'')+',' +isnull(PolicyHolder.AddressLineTwo ,'') + ','+ISNULL(policyholder.AddressLineThree,'')
- ','+ isnull(PolicyHolder.CountyState ,'')+ ','+ isnull(PolicyHolder.Country ,'')as FullAddress
,PolicyHolder .PhoneDaytime as Phonenumber
,DATEDIFF(day,policy.PolicyStartDate ,policy.PolicyEndDate ) as Tripduration
,PolicyHolder.WantsEmailUpdates as MarketingOptin
from policy WITH (NOLOCK)
inner join policyholder WITH (NOLOCK)
on policyholder.policyholderid = policy.policyholderid
inner join quote WITH (NOLOCK)
on quote.quoteid = policy.quoteid
inner join agent WITH (NOLOCK)
on quote.agentid = agent.agentid
inner join AgentProduct WITH (NOLOCK)
on agentproduct.agentproductid = quote.productid
cross apply (
select PolicyTraveller.title +''+ '.'+PolicyTraveller.firstname +''+ ' '+PolicyTraveller.lastname +', '+ cast (PolicyTraveller.Age as varchar (3))+'; ' as [text()]
from PolicyTraveller
where quote.QuoteId = PolicyTraveller.QuoteId
for xml path ('')
)policytraveller (Travellers)
where
policy.DateCreated >=DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)AND policy.DateCreated <DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
and policy.IsPaid = 1
and (agent.agentname like '%test%' )