October 7, 2009 at 4:53 am
Hi,
This statement works when i run the select statement. When I copy and paste this into a add new VIEW and run it i got a message
Error MSg: Timeout Expired. timeout period elapsed prior to completion of the operation or server not responding.
HELP!
SELECT dbo.contact.patient_date_CONTACT, dbo.contact.CONTACT_id, dbo.patient.PATIENT_id, dbo.patient.date_of_birth, dbo.address.postcode,
dbo.contact.method_1, dbo.contact.STAFF_TEAM, dbo.address.category, dbo.address.end_date
FROM dbo.contact INNER JOIN
dbo.patient ON dbo.contact.patient_ID = dbo.patient.PATIENT_id INNER JOIN
dbo.address ON dbo.patient.PATIENT_id = dbo.address.patient
WHERE (dbo.contact.patient_date_CONTACT BETWEEN CONVERT(DATETIME, '2008-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-03-31 23:59:00',
102)) AND (dbo.contact.method_1 = 'FP14' OR
dbo.contact.method_1 = 'FP15' OR
dbo.contact.method_1 LIKE 'FP17*') AND (dbo.contact.STAFF_TEAM = 'fplan
October 7, 2009 at 7:32 am
Are the other objects being referenced views or tables? You could start trying to figure out what's happening by looking at the actual execution plan for the query and the estimated execution plan for the view.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2009 at 9:52 am
i don't understand. I created a select statement ehich worked and then tried to create a view which involved copying the select statement
regards
R
October 7, 2009 at 10:01 am
Can you post the code for your view as well? Are you hardcoding the date parameters?
October 7, 2009 at 11:18 am
ritesh.kansara (10/7/2009)
i don't understand. I created a select statement ehich worked and then tried to create a view which involved copying the select statementregards
R
I asked if the other objects being refereced were also views because then you would be nesting a view. That can lead to some serious performance issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2009 at 1:43 am
to create the view, I've gone into the management studio and selected view - new view and copy and pasted the above SQL statement into it. I've saved this and expected it to work when i run it.
October 8, 2009 at 5:51 am
ritesh.kansara (10/8/2009)
to create the view, I've gone into the management studio and selected view - new view and copy and pasted the above SQL statement into it. I've saved this and expected it to work when i run it.
I understand. But you're not answering my question and since I'm not sitting in your chair, I need you to tell me things so that I can try to help.
Are the objects referenced in this query also views? Or are they all tables?
Can you post the query that you're using to reference the view? Can you post an estimated execution plan from the view? Can you also post the actual execution plan from the query when it's not a view?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2009 at 6:52 am
Hi I've just ran the view ,
it is working onw.
thanks everyone
October 8, 2009 at 1:16 pm
ritesh.kansara (10/8/2009)
to create the view, I've gone into the management studio and selected view - new view and copy and pasted the above SQL statement into it. I've saved this and expected it to work when i run it.
It will actually work and it will run.
I'm just guessing: Did you open the view by right click in Object Explorer and select OpenView?
If so, then the select will time out after 30 seconds. Seems to be a standard value within SSMS (I don't know if there's a way to configure it...).
Anyhow, if you run the view in a query window using SELECT * FROM myView it will run until finished or manually stopped (unless query execution time-out is configured to a value other than zero (=default)).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply