Saturday, April 18

How does a view works ?

How does a sybase view works internally?
How does a view perform as against a direct SQL?

While trying to understand the performance aspect of a view, this is what i came across in Sybase infocenter documentation.

A view can be derived from one or more underlying tables. Only the definition of the view is stored int e database (syscomments table), and not the view data.

When we use a view in a SQL statement, ASE combines the statement with this stored definition
to translate the statement to query underlying tables. This process is called as view resolution.

Consider the following view definition

create view hiprice
as select *
from titles
where price > $15
and advance > $5000

Now, if we use this view like below,

select title, type
from hiprice
where type = "popular_comp"



Internally, ASE combines the query with "hiprice" view's definition, converting the query to:

select title, type
from titles
where price > $15
and advance > $5000
and type = "popular_comp"

Note that it is not only "resolving" the 'where' clause but also the selected column list.

This shows that performance wise it should work as good as any other SQL statement, and should not have any considerable extra overhead.

0 comments:





Disclaimer :
Unless, otherwise mentioned, TF's SQL tips/tricks are for Sybase ASE 12.5.x