Search optimization 
Author Message
 Search optimization

I' am currently coding a program which used to search and extract data from
an existing database. The database itself consists of a few tables which
whole more than 300k records per table and the tables is not index at all
(the structure)

what I need to do is search a parent table (table 01), as the record is
found, search the other table (table 02) based on the data extract from
table 01. Then data from table 02 , search in table 03 and so on.

portion of the code:

SCAN FOR OWMJ32->MJ_CODE="FM" .AND. OWMJ32->J_DATE >= CS_DATE;
 .AND. OWMJ32->J_DATE <= CE_DATE .AND. .NOT. DELETED()
    ....do some other stuffs here.....
    .....
    .....
    LOCATE FOR OWCTNR->JOB_NO = "OU"+LTRIM(STR(OWMJ32->MJ_NO));
      .AND. .NOT. DELETED()
    .....more stuffs......
    SCAN FOR SUBSTR(OWGC->JOB_NO,1,7)=OWCTRN->JOB_NO .AND. .NOT.;
     DELETED()
        ........doing my stuffs here....
        ........
    ENDSCAN
ENDSCAN

The problem here is for the program to extract about 100+ records, in a P3
667 with 128MB RAM and a Quantum 15GB FB, it took about 4 hours, but with
256MB RAM, it just took about 20-30+ mins.

I am currently trying to modify the search mechanism to use SEEK, rather
than SCAN. Is it possible, since it involve a few fields for matching and
data extraction?
If possible, can anyone show me some sample code, which is about same as
above.
Thanx in advance



Mon, 03 Mar 2003 12:24:09 GMT  
 Search optimization
Learn

First, the reason it's slow is probably because as you said there are no
indexes, therefore anything like SCAN etc will not be Rushmore-optimised.
Looking at your example below you would need to match index tags to your
SCAN, i.e., index on cs_date, j_date, ce_date and DELETED().

You are correct that SEEK would be better than locate, or nested SCAN
commands.


Quote:

> I' am currently coding a program which used to search and extract data
from
> an existing database. The database itself consists of a few tables which
> whole more than 300k records per table and the tables is not index at all
> (the structure)

> what I need to do is search a parent table (table 01), as the record is
> found, search the other table (table 02) based on the data extract from
> table 01. Then data from table 02 , search in table 03 and so on.

> portion of the code:

> SCAN FOR OWMJ32->MJ_CODE="FM" .AND. OWMJ32->J_DATE >= CS_DATE;
>  .AND. OWMJ32->J_DATE <= CE_DATE .AND. .NOT. DELETED()
>     ....do some other stuffs here.....
>     .....
>     .....
>     LOCATE FOR OWCTNR->JOB_NO = "OU"+LTRIM(STR(OWMJ32->MJ_NO));
>       .AND. .NOT. DELETED()
>     .....more stuffs......
>     SCAN FOR SUBSTR(OWGC->JOB_NO,1,7)=OWCTRN->JOB_NO .AND. .NOT.;
>      DELETED()
>         ........doing my stuffs here....
>         ........
>     ENDSCAN
> ENDSCAN

> The problem here is for the program to extract about 100+ records, in a P3
> 667 with 128MB RAM and a Quantum 15GB FB, it took about 4 hours, but with
> 256MB RAM, it just took about 20-30+ mins.

> I am currently trying to modify the search mechanism to use SEEK, rather
> than SCAN. Is it possible, since it involve a few fields for matching and
> data extraction?
> If possible, can anyone show me some sample code, which is about same as
> above.
> Thanx in advance



Mon, 03 Mar 2003 03:00:00 GMT  
 Search optimization
What about a SQL view?

Greetings,

Ko Wisse
VFP-er
FlexiSoft



Quote:

> I' am currently coding a program which used to search and extract data
from
> an existing database. The database itself consists of a few tables which
> whole more than 300k records per table and the tables is not index at all
> (the structure)

> what I need to do is search a parent table (table 01), as the record is
> found, search the other table (table 02) based on the data extract from
> table 01. Then data from table 02 , search in table 03 and so on.

> portion of the code:

> SCAN FOR OWMJ32->MJ_CODE="FM" .AND. OWMJ32->J_DATE >= CS_DATE;
>  .AND. OWMJ32->J_DATE <= CE_DATE .AND. .NOT. DELETED()
>     ....do some other stuffs here.....
>     .....
>     .....
>     LOCATE FOR OWCTNR->JOB_NO = "OU"+LTRIM(STR(OWMJ32->MJ_NO));
>       .AND. .NOT. DELETED()
>     .....more stuffs......
>     SCAN FOR SUBSTR(OWGC->JOB_NO,1,7)=OWCTRN->JOB_NO .AND. .NOT.;
>      DELETED()
>         ........doing my stuffs here....
>         ........
>     ENDSCAN
> ENDSCAN

> The problem here is for the program to extract about 100+ records, in a P3
> 667 with 128MB RAM and a Quantum 15GB FB, it took about 4 hours, but with
> 256MB RAM, it just took about 20-30+ mins.

> I am currently trying to modify the search mechanism to use SEEK, rather
> than SCAN. Is it possible, since it involve a few fields for matching and
> data extraction?
> If possible, can anyone show me some sample code, which is about same as
> above.
> Thanx in advance



Mon, 03 Mar 2003 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Optimization question

2. optimization

3. Optimization

4. FoxPro 2.6 for DOS, code optimization

5. Rushmore optimization ?

6. JOIN Optimization

7. Query Optimization

8. Help with optimization

9. VFP Rushmore optimization not kicking in

10. SQL statement optimization

11. Cutting Length Optimization

12. Query Optimization

 

 
Powered by phpBB® Forum Software