Avatar of bfuchs
bfuchs
Flag for United States of America asked on

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

Avatar of undefined
Last Comment
bfuchs

8/22/2022 - Mon
Pawan Kumar

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

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

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

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

Thanks,
Ben
bfuchs

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

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

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

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

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

Great.....Did we got any root cause? any blocking thing.. etc..
bfuchs

ASKER
Hi,

No, guess sometimes you need a consultant onsite to solve an issue..

Thanks,
Ben
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Pawan Kumar

Hi Ben,
You are correct. :)

Thanks,
Pawan
bfuchs

ASKER
Solved.