SQL statement optimization 
Author Message
 SQL statement optimization

Hello everyone,

I dont understand very well about optimization and I would appreciate if
somebody could help me to figure out why the following statement is not
optimized by "Fox" :

Select tbl6.*, tb7.prd_nbr ;
From tbl6, tbl7 ;
Into Cursor arts ;
Where tbl6.prd_id = tbl7.prd_id ;
Group By tbl6.pvl_id

tbl6 - 72,456 records
tbl7 - 317 records
    both tables have a regular index on field "prd_id" and the value
returned by Sys(3054,1) is :

"Rushmore optimization level for table tbl6: none"
"Rushmore optimization level for table tbl7: none"

Thank you in advance.

--
    Carlos Jones Terrones
Programador Analista
Departamento de Sistemas
Maersk Logistics Mxico
5899-9374



Mon, 28 Nov 2005 23:15:14 GMT  
 SQL statement optimization
Hi Carlos,

Try to create an index on each field and that will take care of RushMore
problem.

Regards,

Doron

The Farber Consulting Group, Inc.

The Creator of VisualRep - Report and Query Engine
http://www.dfarber.com/VRep.htm


Quote:
> Hello everyone,

> I dont understand very well about optimization and I would appreciate if
> somebody could help me to figure out why the following statement is not
> optimized by "Fox" :

> Select tbl6.*, tb7.prd_nbr ;
> From tbl6, tbl7 ;
> Into Cursor arts ;
> Where tbl6.prd_id = tbl7.prd_id ;
> Group By tbl6.pvl_id

> tbl6 - 72,456 records
> tbl7 - 317 records
>     both tables have a regular index on field "prd_id" and the value
> returned by Sys(3054,1) is :

> "Rushmore optimization level for table tbl6: none"
> "Rushmore optimization level for table tbl7: none"

> Thank you in advance.

> --
>     Carlos Jones Terrones
> Programador Analista
> Departamento de Sistemas
> Maersk Logistics Mxico
> 5899-9374




Tue, 29 Nov 2005 00:02:22 GMT  
 SQL statement optimization
Do you have SET DELETED ON ?
You probably need an index on DELETED() for each table to make it fully
optimisable.
You should only need indexes on the primary keys, the prd_id fields.
If you also index on tbl6.pvl_id then the grouping could be optimised, too.
Use SYS(3054,11) instead of (,1) to get more detail.


Quote:
> Hello everyone,

> I dont understand very well about optimization and I would appreciate if
> somebody could help me to figure out why the following statement is not
> optimized by "Fox" :

> Select tbl6.*, tb7.prd_nbr ;
> From tbl6, tbl7 ;
> Into Cursor arts ;
> Where tbl6.prd_id = tbl7.prd_id ;
> Group By tbl6.pvl_id

> tbl6 - 72,456 records
> tbl7 - 317 records
>     both tables have a regular index on field "prd_id" and the value
> returned by Sys(3054,1) is :

> "Rushmore optimization level for table tbl6: none"
> "Rushmore optimization level for table tbl7: none"

> Thank you in advance.

> --
>     Carlos Jones Terrones
> Programador Analista
> Departamento de Sistemas
> Maersk Logistics Mxico
> 5899-9374




Tue, 29 Nov 2005 00:20:09 GMT  
 SQL statement optimization
Does the Tbl6 index tag for "prd_id" have a FOR clause?  If so, there's the
problem - Rushmore cannot use filtered tags.

 - Rush


Quote:
> Hello everyone,

> I dont understand very well about optimization and I would appreciate if
> somebody could help me to figure out why the following statement is not
> optimized by "Fox" :

> Select tbl6.*, tb7.prd_nbr ;
> From tbl6, tbl7 ;
> Into Cursor arts ;
> Where tbl6.prd_id = tbl7.prd_id ;
> Group By tbl6.pvl_id

> tbl6 - 72,456 records
> tbl7 - 317 records
>     both tables have a regular index on field "prd_id" and the value
> returned by Sys(3054,1) is :

> "Rushmore optimization level for table tbl6: none"
> "Rushmore optimization level for table tbl7: none"

> Thank you in advance.

> --
>     Carlos Jones Terrones
> Programador Analista
> Departamento de Sistemas
> Maersk Logistics Mxico
> 5899-9374




Tue, 29 Nov 2005 00:25:35 GMT  
 SQL statement optimization
Try Training a Monkey to go and convert your foxpro code to the Monkey's
Natural Language of "EEP-EEP" then use the XML language to create a XSLT
conversion format inorder to easily create a transition to FOX-SQL.   You
will be more likely to get this to work, then to get FoxPro to run fast.


Quote:
> Hello everyone,

> I dont understand very well about optimization and I would appreciate if
> somebody could help me to figure out why the following statement is not
> optimized by "Fox" :

> Select tbl6.*, tb7.prd_nbr ;
> From tbl6, tbl7 ;
> Into Cursor arts ;
> Where tbl6.prd_id = tbl7.prd_id ;
> Group By tbl6.pvl_id

> tbl6 - 72,456 records
> tbl7 - 317 records
>     both tables have a regular index on field "prd_id" and the value
> returned by Sys(3054,1) is :

> "Rushmore optimization level for table tbl6: none"
> "Rushmore optimization level for table tbl7: none"

> Thank you in advance.

> --
>     Carlos Jones Terrones
> Programador Analista
> Departamento de Sistemas
> Maersk Logistics Mxico
> 5899-9374




Tue, 29 Nov 2005 03:50:38 GMT  
 SQL statement optimization
Is SET OPTIMIZE OFF anywhere in your code or settings?

Quote:
>-----Original Message-----
>Hello everyone,

>I dont understand very well about optimization and I
would appreciate if
>somebody could help me to figure out why the following
statement is not
>optimized by "Fox" :

>Select tbl6.*, tb7.prd_nbr ;
>From tbl6, tbl7 ;
>Into Cursor arts ;
>Where tbl6.prd_id = tbl7.prd_id ;
>Group By tbl6.pvl_id

>tbl6 - 72,456 records
>tbl7 - 317 records
>    both tables have a regular index on field "prd_id"
and the value
>returned by Sys(3054,1) is :

>"Rushmore optimization level for table tbl6: none"
>"Rushmore optimization level for table tbl7: none"

>Thank you in advance.

>--
>    Carlos Jones Terrones
>Programador Analista
>Departamento de Sistemas
>Maersk Logistics Mxico
>5899-9374

>.



Tue, 29 Nov 2005 21:55:49 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. ? for FPW2.6 SQL optimization experts

2. SQL Joins and Rushmore Optimization

3. SQL Server optimization thru ODBC

4. SQL Optimization

5. SQL Syntax - Embedded SQL Statements

6. SQL Update statement problem

7. SQL statement

8. SQL - Select Statement and SUM

9. SQL statement too long for RowSource property....

10. Problem with SQL SELECT Statement

11. Problem with SQL SELECT Statement

12. Can FOX do this SQL statement?

 

 
Powered by phpBB® Forum Software