Hi!

Another speed question... I just can't find out how this stuff works.
I have SqlMemTable with 2 data, 5 Lookup and 5 Calc fields.
Lookup fields are linked with table (which has around 150.000 records,
50MB of data).
Calc fields are retrieved from another table (which has around 600.000
records, 130 MB of data).

Code for calc field is:
tmp := tBil2002.FindKey([Key1, Key2]); <-- this should be the fastest
way, right?
Both Key1 and Key2 are data fields from SqlMemTable and are also fields
from index of table tBil2002.

For every calc-row it needs 14-16 ms, i need it under 8 or better.

On form I have:
- DBISAMSession (with PrivateDir to c:\tmp)
- DBISAMDatabase
- DBISAMTable (with ReadOnly set to true)
All other properties are default.

Any help would be really appreciated

Best regards,
 Pete

Pete


I may be reading more into names etc than I should be. But if SqlMemTable is a memory table generated by sql why not do the lookups/calcs as part of the sql generating the table?

> Code for calc field is:
> tmp := tBil2002.FindKey([Key1, Key2]); <-- this should be the fastest
>way, right?
> Both Key1 and Key2 are data fields from SqlMemTable and are also fields
>from index of table tBil2002.

The way you've written this makes me think that tBil2002's index is a compound one in which case it might be worthwhile looking at a composite index eg key1+key2

Roy Lambert

Pete,

<< Another speed question... I just can't find out how this stuff works. I
have SqlMemTable with 2 data, 5 Lookup and 5 Calc fields.  Lookup fields are
linked with table (which has around 150.000 records, 50MB of data). Calc
fields are retrieved from another table (which has around 600.000 records,
130 MB of data). >>

Is this all local and not C/S access ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim: Yes, everything is accessed local.

Roy: query with all the joins I need takes about 30s to execute on my
machine (P4 3.0Ghz 1GB ram). That's why I decided to go this way.
Fields Key1 and Key2 are returned from our own index search (which is
way faster than any db query) and are not read from dbIsam database.
That's also why I have to use memory table.
Queries are intended to be ran on other parts of application where user
can wait a while.

Pete


Tim Young [Elevate Software] wrote:
> Pete,
>
> << Another speed question... I just can't find out how this stuff works. I
> have SqlMemTable with 2 data, 5 Lookup and 5 Calc fields.  Lookup fields are
> linked with table (which has around 150.000 records, 50MB of data). Calc
> fields are retrieved from another table (which has around 600.000 records,
> 130 MB of data). >>
>
> Is this all local and not C/S access ?
>

Pete,

<< Tim: Yes, everything is accessed local. >>

Hmmm.   The only thing I can come up with is that you're simply seeing too
much refreshing of the lookup/calculated fields and the time is adding up.
However, that is very unusual when the data is local to the machine.
Usually, you can get away with quite a bit in such cases.  Did you check and
verify that the lookups were optimized and not causing a brute-force search
?

--
Tim Young
Elevate Software
www.elevatesoft.com