Re: View performance in IDS - suggestions?

Matt Penning wrote: > Hi all, > > I'm having performance problems with views in IDS 10 (RC3,Linux). > > Creating a simple view with one join and then querying against it > results in not only a temporary table being created, but a temporary > table with no indexes on it. The temp table creation followed by > sequential scan is obviously very expensive. > > Is there anyway to provide hints in the view query or anything else that > can be done to make a view more performant? > > This is the test I did with the stores_demo database, and the results: > > Test > -------------------------- > set explain on; > create view vw_orderfname as > select order_num, fname from orders > left join customer on orders.customer_num=customer.customer_num; I'd try re-writing the view: create view vw_orderfname as select order_num, fname from orders, customer where orders.customer_num=customer.customer_num; Don't know if it'll make any difference, but the optimizer usually seems to be happier with the informix from syntax as opposed to the ansi syntax. > > select * from vw_orderfname where order_num=1008;> > > Results > -------------------------- > Estimated Cost: 19 > Estimated # of Rows Returned: 1 > > 1) (Temp Table For View): SEQUENTIAL SCAN > > > - Matt