SQLTeam.com | Weblogs | Forums

Timed out error - Report processing has been canceled by the user


#1

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%' )


#2

first thing to check:
Are there indexes on the columns in joins and where conditions?


#3

Hi Ahmed, Thanks for your reply.

'Policytraveller' table has policytravellerid as primary key. But i cant establish a relation using policytravellerid. PolicyTraveller.QuoteId has no index now, is there any work way round to fix this issue. As i can not create index in this table as the permission is limited.


#4

check for index fragmentation and also if index statistics are up to date.