Report timing out

Question

Report timing out

Dan McGorty

SSC Journeyman

Points: 84
More actions
June 8, 2005 at 9:43 am

#110461

I have a report based on a complex query that takes a long time to run and is timing out. I've tuned indexes the best I know how, I've set the "Do not timeout report execution" in the Home page Site Settings and in the individual report properties, but it still times out. Any suggestions will be most appreciated.

Thanks in advance,
Dan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can you post the query, the execution plan and the table definition with its indexes... maybe we can speed it up still (don't know any time out settings in reporting services).

Ray McCoppin SSC Journeyman Points: 76 More actions · Answer 2

The rendering of the report may not be timing out but the IE session maybe timing out. IIS times out session after 20 minutes by default. That is the message on the time out. Have you tried to schedule running of this report at night and render the report from the snapshot?

Dan McGorty SSC Journeyman Points: 84 More actions · Answer 3

I didn't know about the IIS time out, but I'll check it. There was no message indicating the reason for timeout.

Here's the query:

SELECT dbo.Reps.RepNumber, dbo.ShelfAudHeader.CustomerNumber, dbo.Customers.Name, dbo.Chains.Chain, dbo.Customers.City,

dbo.Customers.Name AS Customer, dbo.ShelfAudHeader.TrxnDate, dbo.Products.Description, dbo.ShelfAudDetail.Facings,

CASE WHEN ActualPrice = '0' THEN NULL ELSE Actualprice END AS ShelfPrice, dbo.Reps.RepName, dbo.Supervisors.Supervisor,

dbo.Supervisors.SupervisorID, dbo.ShelfAudDetail.ItemNumber, dbo.Regions.Region, CASE WHEN LEFT(shelfauddetail.itemnumber, 1)

> 4 THEN '' ELSE Facings END AS OurFacings, CASE WHEN LEFT(shelfauddetail.itemnumber, 1) > 4 THEN Facings ELSE '' END AS CompetFacings,

dbo.ShelfAudDetail.OOS AS OutOfStockCount, dbo.Reps.RouteNumber, dbo.Chains.ChainID,

dbo.ShelfAudHeader.CustomerNumber + ' ' + CAST(dbo.ShelfAudHeader.TrxnDate AS nvarchar(12)) AS CustAudits,

dbo.ShelfAudDetail.SPFlag AS PromoCount

FROM dbo.Chains INNER JOIN

dbo.Customers ON dbo.Chains.ChainID = dbo.Customers.ChainNumber INNER JOIN

dbo.ShelfAudHeader ON dbo.Customers.CustomerNumber = dbo.ShelfAudHeader.CustomerNumber INNER JOIN

dbo.ShelfAudDetail INNER JOIN

dbo.Products ON dbo.ShelfAudDetail.ItemNumber = dbo.Products.ItemNumber ON

dbo.ShelfAudHeader.TransactionId = dbo.ShelfAudDetail.TransactionId AND dbo.ShelfAudHeader.RepNumber = dbo.ShelfAudDetail.RepNumber AND

dbo.ShelfAudDetail.TrxnDate = dbo.ShelfAudHeader.TrxnDate INNER JOIN

dbo.Supervisors INNER JOIN

dbo.Reps ON dbo.Supervisors.SupervisorID = dbo.Reps.SupervisorID INNER JOIN

dbo.Regions ON dbo.Supervisors.Region = dbo.Regions.Region ON dbo.ShelfAudHeader.RepNumber = dbo.Reps.RepNumber

WHERE (ShelfAudHeader.TrxnDate BETWEEN @DateRange1 AND @DateRange2) AND (Chains.ChainID = @ChainID OR

@ChainID = - 1) AND (Regions.Region = @Region Or @Region = 'All Regions') AND (NOT EXISTS

(SELECT *

FROM vw_invalidauditjoin

WHERE custauditsinvalid = (shelfaudheader.customernumber + ' ' + cast(shelfaudheader.trxndate AS nvarchar(12)))))

vw_invalidauditjoin:

SELECT dbo.ShelfAudHeader.CustomerNumber + ' ' + CAST(dbo.ShelfAudHeader.TrxnDate AS nvarchar(12)) AS CustAuditsInvalid,

dbo.ShelfAudHeader.TransactionId, dbo.ShelfAudHeader.RepNumber

FROM dbo.ShelfAudHeader INNER JOIN

dbo.ShelfAudDetail INNER JOIN

dbo.Products ON dbo.ShelfAudDetail.ItemNumber = dbo.Products.ItemNumber ON

dbo.ShelfAudHeader.TransactionId = dbo.ShelfAudDetail.TransactionId AND dbo.ShelfAudHeader.RepNumber = dbo.ShelfAudDetail.RepNumber AND

dbo.ShelfAudHeader.TrxnDate = dbo.ShelfAudDetail.TrxnDate

WHERE (dbo.ShelfAudDetail.Facings > 9) OR

(CASE WHEN shelfAudDetail.ActualPrice = 0 THEN NULL ELSE ShelfAudDetail.Actualprice END > 3.99) OR

(CASE WHEN shelfAudDetail.ActualPrice = 0 THEN NULL ELSE ShelfAudDetail.Actualprice END < dbo.Products.UtilityValue - .004)

ShelfAudHeader indexes: RepNumber, TransactionID, TransactionID, TrxnTime

Products index: ItemNumber

No indexes on ShelfAudDetail

Customers indexes: CustomerNumber, ChainNumber

Supervisors index: SupervisorID

Reps index: RepNumber

Regions index: Region

Chains index: ChainID

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Can you post the query plan?? We can help you without that... except to tell you that the table with no index can be slowing things down.

Do you have a table/index/clustered index scan in the query plan?