View timeout problem

Question

View timeout problem

BRP

Default port

Points: 1433
More actions
January 17, 2019 at 4:49 pm

#376831

I have a fairly complex sql view that times out most of the time. I run it from SSMS to test, if I run the same sql in a query window it takes 1:35 mins first time to return 10108 records the second time 22 secs. After it is run as a query all good both run satisfactorily but that is not good when the View is run by Python scripts in the early hours.
It is a 2008 database using a link server connection to a 2012 db.
Here is some of the detail of the execution error: from the View
Error Source: .Net SqlClient Data Provider
Error Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Is there a way of hitting up the linkserver first to activate (if that's the problem) or a View timeout setting I can adjust or is there something else I can do?
Bruce

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Bruce-12445 - Thursday, January 17, 2019 4:49 PM
I have a fairly complex sql view that times out most of the time. I run it from SSMS to test, if I run the same sql in a query window it takes 1:35 mins first time to return 10108 records the second time 22 secs. After it is run as a query all good both run satisfactorily but that is not good when the View is run by Python scripts in the early hours.
It is a 2008 database using a link server connection to a 2012 db.
Here is some of the detail of the execution error: from the View
Error Source: .Net SqlClient Data Provider
Error Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Is there a way of hitting up the linkserver first to activate (if that's the problem) or a View timeout setting I can adjust or is there something else I can do?
Bruce

I think it could be that you ran the query and didn't get the error due to different timeout settings.
Usually the default CommandTimeout with the provider is 30 seconds. Maybe try to change the CommandTimeout property in the Python scripts?
And then simplify the view - whether the linked server is the issue or not there is way way to tell from the description.

Sue

BRP Default port Points: 1433 More actions · Answer 2

I tried changing CommandTimeout and kickstarting the linked server and reducing some of the query without any change. I'll look for alternative methods.
Thanks.

Ken McKelvey SSCoach Points: 18766 More actions · Answer 3

Are you using four part naming in the view? If you are then all the rows for the remote table will be returned before filters are applied.
You might be able to do some remote filtering by using OPENQUERY which will speed up the view regardless of where it is run from.

BRP Default port Points: 1433 More actions · Answer 4

Yes I am using four part naming and have tried to offset that by using other views which I thought was successful until it failed over time. I didn't know it returned all records without filters. It looks like the OPENQUERY is the answer it's going well, still testing though.
Thank you so much for that Ken, much appreciated.
Bruce

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Bruce-12445 - Thursday, January 17, 2019 4:49 PM
I have a fairly complex sql view that times out most of the time. I run it from SSMS to test, if I run the same sql in a query window it takes 1:35 mins first time to return 10108 records the second time 22 secs. After it is run as a query all good both run satisfactorily but that is not good when the View is run by Python scripts in the early hours.
It is a 2008 database using a link server connection to a 2012 db.
Here is some of the detail of the execution error: from the View
Error Source: .Net SqlClient Data Provider
Error Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Is there a way of hitting up the linkserver first to activate (if that's the problem) or a View timeout setting I can adjust or is there something else I can do?
Bruce

I'm thinking that whatever Python is using, the connection string has different properties than the connection string of SSMS. I'd check those first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BRP Default port Points: 1433 More actions · Answer 6

thanks Jeff the OPENQUERY solution solved all discrepancies.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Bruce-12445 - Monday, January 21, 2019 11:16 PM
thanks Jeff the OPENQUERY solution solved all discrepancies.

Thanks for the feedback, Bruce.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)