db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Westerfeld, Kurt" <kwesterf...@managedobjects.com>
Subject VTI, Indexed Lookup and the Query Optimizer
Date Thu, 12 Jan 2006 19:54:55 GMT
OK, my head is swimming trying to sort through all the issues regarding
query performance and VTI-based tables.  We have implemented several VTI
tables that when joined together cause massive performance problems.


I have narrowed down the issues I have to two areas:


1.	The VTI "2" version instances are marked reusable, and thus on
each "next" call from the DRDA stack (we're using either ODBC or JDBC
interfaces) our VTI is not being constructed, but its last result is
being "reopened."  At any rate, our executeQuery() method is called once
per row of one side of a join.  This is a clear optimization we should
try and tackle.
2.	That being said (1), the optimization in (1) doesn't help a
whole lot because table scans are used to compare rows between two VTI
tables.  That is, one side of the JOIN is scrolled forward, but the
other side of the JOIN is a table scan.


What can be done about this?  It looks like FromVTI.java implements
Optimizable, which I'm assuming is the interface to supply indexed
lookup.  But, like I said, my head is swimming trying to figure out if I
either 1) don't understand how to enable an indexed lookup for my VTI or
2) if it's not even possible to do so, in which case I should start to
hack in that ability.


As always, any help is greatly appreciated!

  Kurt Westerfeld


View raw message