Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Performance
When you write a query on a view, the view is translated into its underlying SELECT statements. If you need information from one table, but go through a multitable view to get it, you pay a price. For example, start with a view based on a two-table join.
create view ordervu as select od.ordnum, od.prodnum,substring( p.name,1, 20) as name, p.price * od.unit as cost from orderdetail od, product p where od.prodnum = p.prodnum The command(s) completed successfully.
To find the name of a particular product, you might write a query like this:
select distinct prodnum, name from ordervu where prodnum = 2050 prodnum name ----------- -------------------- 2050 tax time (1 row(s) affected)
It gives you exactly the results you want, but performance (which you can monitor with vendor-supplied GUI tools or SQL-like commands) leaves something to desire. Although all you need is prodnum and name, both available in the product table, the optimizer accesses both tables underlying the view.
Removing DISTINCT cuts down on the processing a bit, but it also returns multiple copies of the product number and name. It shows one row for every order including the product number 2050.
select prodnum, name from ordervu where prodnum = 2050 prodnum name ----------- -------------------- 2050 tax time 2050 tax time 2050 tax time 2050 tax time 2050 tax time 2050 tax time 2050 tax time (7 row(s) affected)
Now check the same query on the product base table. Although you get the same results, you'll find a substantial difference in performance.
select prodnum, name from product where prodnum = 2050 prodnum name ----------- -------------------- 2050 tax time (1 row(s) affected
Don't use a view if all the data you want is in a single underlying table, and don't make frequently queried views unnecessarily complex.