Re: View performance in IDS - suggestions?

Thanks, that seems to do the trick. - Matt Richard Harnden wrote: > 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