Union all timeout - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Union all timeout

Author

Topic

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 11:41:58

sql 2000 sp4

What would cause this type of timeout?

DB1 - Table Members indexed memberid -
view1 contains data referencing TABLE ABOVE including memberid
table count 1.5m +/-
DB2 - Table Members indexed memberid -
view2 contains data referencing TABLE ABOVE including memberid
table count 4.5m +/-

DB3 - combinView contains
select *
from view 1
union all
select *
from view2

When accessing view through DB3
select *
from combinView
where memberid = 'xxxxxx'

Receiveing time out error

however when I query the views independly they execute in less than a second.
The only change that I've made is drop the table in db1 and recreated it along with the indexes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 11:50:06

Do you still get the same error when running this?

select *
from view1
where memberid = 'xxxxxx'
union all
select *
from view2
where memberid = 'xxxxxx'

Peter Larsson
Helsingborg, Sweden

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 11:54:40

No it works fine!

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 12:06:11

This does not resolve my problem.
The view is used by many users searching for specific members.

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 12:06:16

This does not resolve my problem.
The view is used by many users searching for specific members.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 12:59:50

Do you views contains an ORDER BY?
If so, remove it.

Peter Larsson
Helsingborg, Sweden

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-16 : 13:01:40

Before dropping the table in db1, was it returning the results fast? If so, try refreshing the view using the sp_refreshview system stored procedure. Also, if it is an option, use a UNION instead of a UNION ALL and see if it makes a difference.

SQL Server Helper
http://www.sql-server-helper.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 13:06:32

UNION would make the query slower, because it has to remove all duplicate records.

Peter Larsson
Helsingborg, Sweden

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-16 : 13:08:13

quote:
Originally posted by Peso

UNION would make the query slower, because it has to remove all duplicate records.

Peter Larsson
Helsingborg, Sweden

You're right. I always interchange the 2

SQL Server Helper
http://www.sql-server-helper.com

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 13:32:55

Yes, I did refresh the views (all three)!
And no there is not a order by in any of the views.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 13:35:44

If the individual views are running in < a second, try if this approach works:

Declare @Tmp table (.....)
Insert into @tmp (...)
SELECT col1,col2,.... FROM DB1.dbo.view1 WHERE < condition >

Insert into @tmp (...)
SELECT col1,col2,.... FROM DB2.dbo.view2 WHERE < condition >

Select * from @tmp

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 13:59:53

Yes, I'm sure this would work however, the union all view is utilized on a web site that offers numeris options.
This view has work for over 1+ years. All I did was drop and rebuild a table.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 14:09:25

hmm..see if you can drop/recreate the view and if is helps..not sure if if/why it may/may not work..just throwing out an idea..schema changes always mess up query plans. so perhaps dropping recreating the view might help..

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 14:13:34

In reference to your suggestion, I dropped the view in question, but still no changes.
The view is taking over 30 seconds to return results - which times out. I can reset the time out but that is not really resolving the issue.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 14:19:13

when you created the table do you use dbo.tablename ?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-16 : 14:20:14

yes, dbo.dw_v_WebMember

Sorry that was the view name the table name is:

dbo.dw.MainView

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-04-16 : 15:56:12

Have you updated statistics on the new table?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 16:26:33