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.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2004-07-21 : 12:09:08
|
| 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
152 Posts |
Posted - 2004-07-21 : 12:26:33
|
| 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 |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-21 : 14:05:08
|
| 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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-07-21 : 15:12:35
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-21 : 15:15:31
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-21 : 15:44:16
|
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 MyViewIf not, and the SELECT * thingie times out at, say, 50,126 rows  then IF (SELECT COUNT(*) FROM MyWView WHERE ...) > 50126BEGINSELECT [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'ENDELSEBEGINSELECT * FROM MyView WHERE ...ENDKristen |
|
|
|
|
|
|
|
|
view too big