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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 view too big
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aged Yak Warrior

808 Posts

Posted - 07/21/2004 :  12:09:08  Show Profile  Reply with Quote
I have a view that is too big to open. SQL Server threw Timeout expired.

I know one option is to purge the data.

My questions are:
If I build index on some of the columns, will that improve its performance?
What about if I build a second view based on it with a narrower set of columns, does this solve the problem?
And, if users don't mind to wait, how can I increase the timeout buffer?

Thanks in advance!

Posting Yak Master

152 Posts

Posted - 07/21/2004 :  12:26:33  Show Profile  Reply with Quote
I was always taught a view based on another view was a bad idea. Can you narrow the parameters of the query? I have a table with 3 million records in it. In order to get anything worthwhile out of it and in a timely manner I have to make the query/view as specific as possible.

Go to Top of Page

Pointy Haired Yak DBA

4184 Posts

Posted - 07/21/2004 :  14:05:08  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
What application are you using that's getting the timeout? You should be able to set the timeout to whatever you want where you define the connection. Building indexes on some columns could improve performance if the columns are used in the FROM or WHERE clauses. Instead of building a second view, build another view that's more specific and drives off the base tables.


When life gives you a lemon, fire the DBA.
Go to Top of Page

Aged Yak Warrior

808 Posts

Posted - 07/21/2004 :  15:12:35  Show Profile  Reply with Quote
Thank you Laura and Derirck.

The timeout occured in MS Access. Then when I tried to open view (return all rows) in EM, it happened again.

As it is i Access linked table, I do not have a place to set timeout in connection string. Also, it will be very hard to find out which fields/records could be eliminated since there are hundreds of places that use this view.

Oh, well, I got the idea, and will fight through it.
Go to Top of Page

Almighty SQL Goddess

38200 Posts

Posted - 07/21/2004 :  15:15:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
How many rows should the view return? You shouldn't do a SELECT * FROM ViewName whether it be from Access, EM, or Query Analyzer? If it's returning millions or even thousands of rows, you should be putting a WHERE clause on it as well. If you don't want to filter the data with a WHERE clause, at least use TOP 1000 to get a preliminary look at the data. And yes putting indexes on the view would help out, but you could also index the columns on the tables involved in the view.

Go to Top of Page


United Kingdom
22859 Posts

Posted - 07/21/2004 :  15:44:16  Show Profile  Reply with Quote
Does SELECT TOP 10 * FROM MyView time out? SELECT TOP 100 ... ? SELECT TOP 1000 ... ?

You get the idea! then restrict the queries to only allowing a max of that number of records.

Does this also time out:

If not, and the SELECT * thingie times out at, say, 50,126 rows then

IF (SELECT COUNT(*) FROM MyWView WHERE ...) > 50126
SELECT [ERROR]='Sorry, you and trying to retrieve more than 50,126 rows, and I've tried it and it can't be done, so please try a smaller query'

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000