SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Hommer
Aged Yak Warrior

789 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!

lauramayer
Posting Yak Master

USA
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.

Laura
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
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.

MeanOldDBA
derrickleggett@hotmail.com

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

Hommer
Aged Yak Warrior

789 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

tkizer
Almighty SQL Goddess

USA
37159 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.

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22415 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:
SELECT COUNT(*) FROM MyView

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

IF (SELECT COUNT(*) FROM MyWView WHERE ...) > 50126
BEGIN
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'
END
ELSE
BEGIN
SELECT * FROM MyView WHERE ...
END

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000