> The data cannot be pulled at
> one time either since it comes from different rows.
Think about using a set of DECLARE CURSOR statements and then OPENing and
CLOSEing the cursor as required. When you re-open a cursor to get different
rows (by changing one or more of the variables that was used in the WHERE
clause of the DECLARE CURSOR statement), R:Base has already optimized the
query and you may find a substantial speed improvement.
Also, you might want to look at index-only retrieval which R:Base will do if
_all_ the columns necessary to satisfy a query are available in an index.
In this case, the main table is never read at all. If you know that, say,
three of the columns in the table are going to make up 50% of the data
requests, you could build an index on the primary key plus those 3 columns.
Retrieval against that index would be very fast.
> Unfortunately every stock in the same table will have the same
> numbers- 700 stocks equal 700 duplicate values which is not very good for
> an index although 700 out of 2-3 million rows is not terrible I guess.
I'm pretty sure that R:Base will use two columns for an indexed retrieval if
the two columns are included in a single key. You should be able to make up
a unique primary key from the ticker symbol and date id column. That will
get you the row you want every time. (If R:Base only uses one column in an
index, you could always make a computed column to hold the key).
--
Larry
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
