View versus Tables 2

[Michali]

What is prefrable in the meaning of speed and in general-
using views or using tables in order to create a crystal report.

 

[Naith]

Views are generally quicker than tables. But, it is largely dependant on your environment. If you have a database in good shape, with good indices and optimising, there's no reason why querying tables should not be as quick as views.

If your database is very heavy in terms of data quantity and average processing time, then the most efficient dynamic data sources as far as speed is concerned would have to be:

(1) stored procedures
(2) views
(3) tables

Naith

 

[synapsevampire]

A View works at near the same speed of a table (it might be a gnat's butt slower but you'll never notice it). Keep in mind that a View is simply SQL pointing at a table (or perhaps another View which eventually points to a table.

A View can be faster than a table if you have indexed Views, and the tables are not indexed. There are also concerns with what the View is doing. If it is not an indexed View, and you do something other than a straight select on a column (such as Cast or Substr), you may not enjoy the performance of the underlying tables indexes.

One of the great advantages to using a View over a table is if the underlying structure of a table changes. Alter the View, and any applications/reports are still funcitonal, whereas if you point to tables, you'll need to rework your applications and tables.

I never let developers go directly against tables.

Stored Procedures will generally be faster, as they enjoy a precompiled execution plan, and they also allow for functionality not afforded Views.

In terms of speed, SP's are first, and Tables and Views are the same.

As Naith mentioned, databases differ. For the best database optimization tips, use the appropriate database forums.

-k