bfuchs

asked on 12/7/2017

SQL view suddenly timeout expired!!

Hi Experts,

I'm suddenly getting an error "Timeout Expired" when opening a view.
I know earlier in the day it was working fine.
What should I do?

See attached.

Following is the view's SQL.

SELECT     TOP (99.9999) PERCENT dbo.View_OrientSchedOpenFillUnionQry.ID, dbo.View_OrientSchedOpenFillUnionQry.EmployeeID, 
                      dbo.View_OrientSchedOpenFillUnionQry.FacilityID, dbo.View_OrientSchedOpenFillUnionQry.FacilityDate, dbo.View_OrientSchedOpenFillUnionQry.Result, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationYN, dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmation, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationDate, dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationInitial, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationTime, dbo.View_OrientSchedOpenFillUnionQry.FileApproved, 
                      dbo.View_OrientSchedOpenFillUnionQry.ApprovalStatusDate, dbo.View_OrientSchedOpenFillUnionQry.CompleteFile, 
                      dbo.View_OrientSchedOpenFillUnionQry.CompleteFileDate, dbo.View_OrientSchedOpenFillUnionQry.DocMissing, 
                      dbo.View_OrientSchedOpenFillUnionQry.NotesPayment, dbo.View_OrientSchedOpenFillUnionQry.DateEntered, dbo.View_OrientSchedOpenFillUnionQry.Initial, 
                      ISNULL(dbo.Employeestbl.LastName, '') + ' ' + ISNULL(dbo.Employeestbl.FirstName, '') AS EmployeeName, dbo.Employeestbl.Title, 
                      dbo.Employeestbl.Initial AS HireInitial, dbo.TovInfo.Day AS LastDate, dbo.TovInfo.Facility1, dbo.Facilitiestbl.Name AS FacilityName, 
                      dbo.Employeestbl.ts AS EmployeeTS, dbo.Facilitiestbl.ts, dbo.View_OrientSchedOpenFillUnionQry.HC, dbo.View_OrientSchedOpenFillUnionQry.TableName, 
                      dbo.View_OrientSchedOpenFillUnionQry.reporttype, dbo.View_OrientSchedOpenFillUnionQry.Notes, dbo.Facilitiestbl.County, 
                      dbo.View_OrientSchedOpenFillUnionQry.Staffer, dbo.View_OrientSchedOpenFillUnionQry.SubmittedDate, dbo.View_OrientSchedOpenFillUnionQry.StafferDate
FROM         dbo.View_OrientSchedOpenFillUnionQry LEFT OUTER JOIN
                      dbo.Facilitiestbl ON dbo.View_OrientSchedOpenFillUnionQry.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
                      dbo.Employeestbl ON dbo.View_OrientSchedOpenFillUnionQry.EmployeeID = dbo.Employeestbl.ID LEFT OUTER JOIN
                      dbo.TovInfo ON dbo.View_OrientSchedOpenFillUnionQry.EmployeeID = dbo.TovInfo.EmployeeID
ORDER BY FacilityName, dbo.View_OrientSchedOpenFillUnionQry.FacilityDate DESC

Open in new window

Untitled.png
Untitled.png

DatabasesMicrosoft AccessMicrosoft SQL ServerSQL

Last Comment

bfuchs

8/22/2022 - Mon

Pawan Kumar

12/7/2017

Right click on the DB Server -> Connections -> Remote Query TimeOUT ( In seconds, 0 = noTimeOUT)
-> SET 0
and try again.

Also are you guys updating stats regularly on the server as the part of DB Maintenance.?

bfuchs

12/7/2017

ASKER

@Pawan,

I changed that now (see attached).
However still getting Timeout expired.

Also are you guys updating stats regularly on the server as the part of DB Maintenance.?

Not really, what is the recommended?

Thanks,
Ben
Untitled.png

Pawan Kumar

12/7/2017

Close the SSMS and connect again and then try again...It should take affect.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

bfuchs

12/7/2017

ASKER

No, that didnt help!
What else could it be?
Should I change back value to 600?

Thanks,
Ben

bfuchs

12/7/2017

ASKER

When I remove the order by clause it works fine.
Trying to execute this SQL in new query window and its taking way too long..
Something must have happen today as it suddenly stopped to work!!

Thanks,
Ben

Pawan Kumar

12/7/2017

How are you opening the view?

You can use below-

select definition
from sys.objects o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.yourVIEWNAME')
and o.type = 'V'

or this

SELECT object_definition (OBJECT_ID(N'dbo.yourVIEWNAME'))

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

SOLUTION

Pawan Kumar

12/7/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

bfuchs

12/7/2017

ASKER

SELECT object_definition (OBJECT_ID(N'dbo.yourVIEWNAME'))

I just get create view with the SQL I posted above.

in that case if it working after removing the where clause...

Not the where clause, the Order by clause.
BTW, its already 16+ min and the query is still running..

What can be done?

I need this fixed ASAP.

Thanks,
Ben

ASKER CERTIFIED SOLUTION

bfuchs

12/7/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Pawan Kumar