Hi Tom,
Thanks for the input but I guess what nobody gets is that the calculations
require many, many, many, many accesses to the table to calculate. One
calculation can require data from up to 144 rows of data and sometimes data
is needed from multiple columns in each row. The data cannot be pulled at
one time either since it comes from different rows. The "autonumber"
column is a critical number as it correlates to the TradingDate column. I will
often have to get data from a date like 13 trading periods ago. The dates are
not sequential. There are weekends and holidays with no TradingDate. With
the autonumber column I can just subtract 13 from the current autonumber
and I am at the correct data. Otherwise I must use a loop to verify that data
exists and go back one day at a time and check for existing data and if not
then go back another day until I have my 13th day.
This is why every stock for every date must be sequentially numbered. If not
then the number of calls to the database for data could easily go up by a
factor of ten and in some cases much more than that. This is the main index I
am using. 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 will try the single database and pull data into a temporary table for one
stock at a time and work the data there. This may work okay, I will not
know until I try it.
Best regards,
Mike Young
On Thu, 27 Sep 2001 10:37:41 +1000, Tom Grimshaw wrote:
>G'day Mike,
>
>How would this do?
>
>Col1 Row no in table Autonumber INT PK
>Col2 DataSetNo (values 1 to 700) INTEGER FK to DataSet table
>Col3 RowInDataSet (essentially a row number for the dataset) INTEGER
>
>At 08:19 AM 26/09/2001 -0700, you wrote:
>>Hi Ben,
>>
>>I thought about this but the table would end up with millions of rows of
data
>>and the only useful index would have at least 700 duplicatesWarmest
regards,
>
>
>
>Tom Grimshaw
>coy: Just For You Software
>tel: 612 9552 3311
>fax: 612 9566 2164
>mobile: 0414 675 903
>
>post: PO Box 470 Glebe NSW 2037 Australia
>street: 3/66 Wentworth Park Rd Glebe NSW 2037
>
>email: [EMAIL PROTECTED]
>web: www.just4usoftware.com.au
>
>This email and any files transmitted with it are confidential to the
>intended recipient and may be privileged. If you have received this email
>inadvertently or you are not the intended recipient, you may not
>disseminate, distribute, copy or in any way rely on it. Further, you should
>notify the sender immediately and delete the email from your computer.
>Whilst we have taken precautions to alert us to the presence of computer
>viruses, we cannot guarantee that this email and any files transmitted with
>it are free from such viruses.
>
