Table vs. View SQL Server 2000 3

[P1A3]

Hi,

Wondering if anyone can provide input on using Views vs. Tables in SQL Server 2000. I have been tasked with a complete redesign of a bunch derived tables that are currently loaded with along with the main tablew with each import. There are, in some cases, millions of records. The derived tables range from being populated via criteria applied to main table with case statements to performing roll up calculations. For the most part, they are populated from one table, but there are a couple of cases where a join is required during the load script.

So, I'm wondering...

Can a view be refreshed based on the launch of the application that needs the data in the view? ..if so, is the performance relatively good when you're working with huge (wide) tables with millions of rows?

Does a view need to be refreshed each time one of the tables on which it is dependent is refreshed?

How are Views performance (this one is critical because they don't like to wait for their data)?

Can you shed some light on the indexed View?

Any input is greatly appreciated.

Mary

 

[hneal98]

<Can a view be refreshed based on the launch of the application that needs the data in the view?>

Not sure what you mean by refreshed. Views are queries that are based on tables.

<Does a view need to be refreshed each time one of the tables on which it is dependent is refreshed?>

Again, what do you mean by "refreshed"?

<How are Views performance (this one is critical because they don't like to wait for their data)?>

Depends on the indexes and restraints on the tables they are based on. I am not completely sure, but I think you can put an index on views as well. You can look that up in Books On Line.

 

[SQLSister]

INdexed views can indeed speed up processing under the right circumstances. Suggest you read about indexed views in Books Online which will explain in detail what can speed things up and what can slow the system down or not improve speed.

Questions about posting. See faq183-874

 

[P1A3]

Sorry for the ambiguity on what I mean by refreshed....
Thanks for the reply. I'm still reading up on this and trying to familiarize.

Looking in the Books, but I also wanted to get a non-biased experience on performance when it comes to views.

I think I'm finding out that there might be too much relativity to ask such an opinion (number of rows, extent of criteria, etc.).

 

[SQLSister]

The real kicker as I understand it is how extensively and how often the data is updated. The problem with indexing of any kind is that it significantly speeds up select queries at the cost of slowing down data input and revision. If you do mostly data imports and they can be scheduled during off hours, then the slowing of this process may not be a problem. If you have a lot of data entry or the imports are done frequently or during normal work hours, indexing the view could be a disaster.

If you have calculated fields and millions of records, it may well be worth it to use an indexed view and have the calculations aready done as long as it doesn't impact the data entry too greatly. You will probably have to test both ways to see what the impact will be.

I don't know exactly what criteria you need for your view, but it may well be worth your while to first pull out a subset of records in one view and then do the calculations only against that data set by using a stored procedure or another view. Of course this can't work if the calculation is what you need to filter on.

Questions about posting. See faq183-874