Large view problem

I have a considerably large view that is pulling data from tables and other view, using user defined functions, and using case statements. This view is taking a lot of time to load.

I was under the impression that SQL server keeps the views uptodate, so selecting data from them is as fast as selecting from a table. It now seems like SQL server rebuilds the view evertime something accesses it.

Can someone please tell me more on this. I am now having to re-write everything :-(

Thanks Jag

Shameless bump|||Do you have indexes on the columns being referenced in the view? Are you filtering the view with a WHERE clause and do they also have indexes and how up to date are they?|||

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

The view is being filtered by a where clause. However, the problem is actually building the view. When I use the query analyzer to select all the rows, it still takes 30-40 seconds to return just 252 rows !!!!

What was happening was that I was using a number of view to pull out the last data being entered into different areas. These view where then combined into this large view. I also used user defined functions to pull out other data.

I have had to change this to use triggers and store the data elsewhere. This has cut the time down considerable, but I an still wondering why the problem existed in the first place.

|||

jagdipa:

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

Perhaps its time to look into it now..There are some very good articles @.http://www.sql-server-performance.com/articles.asp