> From:          SteveGrose <[log in to unmask]>
 
> I have a couple questions about Visual Basic and Image Sql. I'm fairly new
> to both.
>
> I'm using Visual Basic to access Image databases via ODBC. The set I'm
> using is called Trans and contains 100,000 records. Each  transaction has
> an amount, date and account type, etc..
>
> The first thing I need to do is sum all amounts for the year 1994. I used
> db.createsnapshot(select sum (amt) as charges from trans where
> mid$(modayr,5,2) = '94').
 
Your 'where' clause is not specifying an Image key(can't as you are
using a substring). Therefore it does a serial read.
The ALLBASE pre-processor will user standard Image keys on a simple
select like this.
Interestingly,  if this is a Master set, the read is much faster
than if it is a Detail(I suspect it opens the set as file).
 
> The second thing I need to do, for each distinct account type (select
> distinct account_type from trans) I need to sum the charges that were
> billed in 1994. I currently cycle though each account type with this
> statement db.createsnapshot(select sum (amt) as item1 from trans where
> account_type = '?' and mid$(modayr,5,2)='94') (? being account type)
>
> The above example took 2 Hours 45 Min on a 937 with 30 account types.
 
Here again, is 'account_type' is a key? If so, ALLBASE MAY pick it up
and do a keyed read. Then again it may not.  Sometimes it hard to say
which item it chooses to search on first.
 
The next version of ALLBASE(G1, now in beta) will support TPI(or
B-tree type access).  You could put a b-tree index on your 'modayr'
field and this should speed this up.
 
Bob
--=--=--
Bob Walker, Computer Centre  --  [log in to unmask]
Capilano College, North Vancouver, BC, CANADA.