Rushmore optimization ? 
Author Message
 Rushmore optimization ?

Hi,

i have a problem, i hope you can help me clear.

A query that searches in Table "Ar" and looks up a Text in Table "Fa"
takes about 12 seconds to execute. The resultset is about 71.000
records.
When i change the select to only search on "Ar" and manually search the
Text-Field in table "Fa" the select only takes 1,8 second, and about 4
seconds to search the texts.

Here are the tablestructures (stripped down to the nessecary fields).

Table "Ar" (Has 5.441.867 records)
----------
AwNr C(10)       && Indexed
AtNr   C(22)       && Indexed
EinNr  N(4)        && Indexed
KNr    N(6)        && Indexed (Foriegn Key from Table "Fa")

Table "Fa" (Has 11.200 Records)
-----------
KNr    N(6)        && Indexed
Text1  C(20)

The Field KNr in Table "Ar" is found exactely one time in table "Fa"
N:1
Both tables also have a index on Deleted(), named _deleted

What i want is a cursor with some rows from Table "Ar" and the matching
Text1 from Table "Fa"
-------------
AwNr C(10)
AtNr   C(22)
EinNr  N(4)
KNr    N(6)
Text1  C(20)

Here is the select

select Ar.*, Fa.Text1 ;
 from Ar, Fa ;
 into cursor _test ;
 where ;
  Ar.EinNr == 30 and Ar.AwNr = '15'  and ;
  Fa.KNr == Ar.KNr

If i change the query to

select Ar.*, Space(22) as Text1 ;
 from Ar; ;
 into cursor _test ;
 where ;
  Ar.EinNr == 30 and Ar.AwNr = '15'

and scans through the result "_test" and seeks the Text1 for each record
the whole operation takes about 6 seconds.

When i activate the rushmore-status - Sys(3054,1) - it showes which
indexes there are used.
Used are the _deleted indexes and the apropiate indexes in table "Ar",
but in table "Fa" the index KNr is not used.
The optimization-status says full on both tables.

I would appreciate any ideas.

TIA

Carsten



Sat, 25 Oct 2003 15:39:17 GMT  
 Rushmore optimization ?
Hi
Try dropping the indexes on deleted; in general they don't pay off.
SYS(3054) will the not report Full optimization if deleted is on, but the
query will still likely run faster.
It can also pay off to do this in two steps: first get the rows from Ar,
then join the result cursor to Fa.
-Anders


Quote:
> Hi,

> i have a problem, i hope you can help me clear.

> A query that searches in Table "Ar" and looks up a Text in Table "Fa"
> takes about 12 seconds to execute. The resultset is about 71.000
> records.
> When i change the select to only search on "Ar" and manually search the
> Text-Field in table "Fa" the select only takes 1,8 second, and about 4
> seconds to search the texts.

> Here are the tablestructures (stripped down to the nessecary fields).

> Table "Ar" (Has 5.441.867 records)
> ----------
> AwNr C(10)       && Indexed
> AtNr   C(22)       && Indexed
> EinNr  N(4)        && Indexed
> KNr    N(6)        && Indexed (Foriegn Key from Table "Fa")

> Table "Fa" (Has 11.200 Records)
> -----------
> KNr    N(6)        && Indexed
> Text1  C(20)

> The Field KNr in Table "Ar" is found exactely one time in table "Fa"
> N:1
> Both tables also have a index on Deleted(), named _deleted

> What i want is a cursor with some rows from Table "Ar" and the matching
> Text1 from Table "Fa"
> -------------
> AwNr C(10)
> AtNr   C(22)
> EinNr  N(4)
> KNr    N(6)
> Text1  C(20)

> Here is the select

> select Ar.*, Fa.Text1 ;
>  from Ar, Fa ;
>  into cursor _test ;
>  where ;
>   Ar.EinNr == 30 and Ar.AwNr = '15'  and ;
>   Fa.KNr == Ar.KNr

> If i change the query to

> select Ar.*, Space(22) as Text1 ;
>  from Ar; ;
>  into cursor _test ;
>  where ;
>   Ar.EinNr == 30 and Ar.AwNr = '15'

> and scans through the result "_test" and seeks the Text1 for each record
> the whole operation takes about 6 seconds.

> When i activate the rushmore-status - Sys(3054,1) - it showes which
> indexes there are used.
> Used are the _deleted indexes and the apropiate indexes in table "Ar",
> but in table "Fa" the index KNr is not used.
> The optimization-status says full on both tables.

> I would appreciate any ideas.

> TIA

> Carsten



Sat, 25 Oct 2003 18:57:08 GMT  
 Rushmore optimization ?
Mange tak Anders.
Det pr?ver jeg.

C.

Anders Altberg schrieb:

Quote:
> Hi
> Try dropping the indexes on deleted; in general they don't pay off.
> SYS(3054) will the not report Full optimization if deleted is on, but the
> query will still likely run faster.
> It can also pay off to do this in two steps: first get the rows from Ar,
> then join the result cursor to Fa.
> -Anders



> > Hi,

> > i have a problem, i hope you can help me clear.

> > A query that searches in Table "Ar" and looks up a Text in Table "Fa"
> > takes about 12 seconds to execute. The resultset is about 71.000
> > records.
> > When i change the select to only search on "Ar" and manually search the
> > Text-Field in table "Fa" the select only takes 1,8 second, and about 4
> > seconds to search the texts.

> > Here are the tablestructures (stripped down to the nessecary fields).

> > Table "Ar" (Has 5.441.867 records)
> > ----------
> > AwNr C(10)       && Indexed
> > AtNr   C(22)       && Indexed
> > EinNr  N(4)        && Indexed
> > KNr    N(6)        && Indexed (Foriegn Key from Table "Fa")

> > Table "Fa" (Has 11.200 Records)
> > -----------
> > KNr    N(6)        && Indexed
> > Text1  C(20)

> > The Field KNr in Table "Ar" is found exactely one time in table "Fa"
> > N:1
> > Both tables also have a index on Deleted(), named _deleted

> > What i want is a cursor with some rows from Table "Ar" and the matching
> > Text1 from Table "Fa"
> > -------------
> > AwNr C(10)
> > AtNr   C(22)
> > EinNr  N(4)
> > KNr    N(6)
> > Text1  C(20)

> > Here is the select

> > select Ar.*, Fa.Text1 ;
> >  from Ar, Fa ;
> >  into cursor _test ;
> >  where ;
> >   Ar.EinNr == 30 and Ar.AwNr = '15'  and ;
> >   Fa.KNr == Ar.KNr

> > If i change the query to

> > select Ar.*, Space(22) as Text1 ;
> >  from Ar; ;
> >  into cursor _test ;
> >  where ;
> >   Ar.EinNr == 30 and Ar.AwNr = '15'

> > and scans through the result "_test" and seeks the Text1 for each record
> > the whole operation takes about 6 seconds.

> > When i activate the rushmore-status - Sys(3054,1) - it showes which
> > indexes there are used.
> > Used are the _deleted indexes and the apropiate indexes in table "Ar",
> > but in table "Fa" the index KNr is not used.
> > The optimization-status says full on both tables.

> > I would appreciate any ideas.

> > TIA

> > Carsten



Sat, 25 Oct 2003 20:18:50 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. VFP Rushmore optimization not kicking in

2. Rushmore Optimization and Sys(3054,1)

3. Rushmore Optimization on Views

4. SYS(3054) - Rushmore Optimization

5. Rushmore optimization

6. Rushmore optimization problem

7. SQL Joins and Rushmore Optimization

8. Help with Rushmore Optimization

9. Optimization question

10. optimization

11. Optimization

12. FoxPro 2.6 for DOS, code optimization

 

 
Powered by phpBB® Forum Software