Home > Articles > Data > SQL

  • Print
  • + Share This
From the author of

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.

  • + Share This
  • 🔖 Save To Your Account