HELP NEEDED ON SPEED OF QUERY Please!

HELP NEEDED ON SPEED OF QUERY Please!

Post by Zhidong L » Tue, 02 Jan 1996 04:00:00



Hi everybody,
Thanks to Alan Kwan and Shar Feldheim,my last problem is solved.
Thank you very much!!!
Now my question is :

 I don't know why my query seems very slow, not like a computer
 program running!usually takes far longer than the user can stand.
 That Rushmore technology doesn't seem work effectively.
 My tow main queries are as following:

SELECT Customer.*, Orders.order_numb, Orders.order_date,;
  Shipment.shipment_n, Shipment.shipping_d, Shipment.invoice_da,;
  Shipment.shipping_c, Shipment.amount_due, Shipment.replacemen,;
  Pricing.unit_size, Pricing.pricing_sc, Pricing.measuremen,;
  Pricing.unit_price, Order_deta.item_numb, Order_deta.product_na,;
  Order_deta.quant_ord, Order_deta.quantship, Order_deta.qntbakord,;
  Order_deta.productfom, Orders.purchase_o;
 FROM Customer, Orders, Shipment, Pricing, Order_Details Order_deta;
 WHERE Orders.compliance = Customer.compliance;
   AND Shipment.order_numb = Orders.order_numb;
   AND Pricing.non_profit = Customer.non_profit;
   AND Shipment.replacemen = Pricing.replacemen;
   AND Order_deta.order_numb = Orders.order_numb;
   AND Pricing.productfom = Order_deta.productfom;
   AND (Pricing.pricing_sc > CTOD("12/30/91");
   AND Orders.order_numb = transf);
 GROUP BY Order_deta.item_numb;
 INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Getprice.dbf"

and

SELECT Getprice.compliance, Getprice.last_name, Getprice.first_name,;
  Getprice.institute, Getprice.department, Getprice.address,
Getprice.city,;
  Getprice.region, Getprice.post_code, Getprice.country,
Getprice.bill_add,;
  Getprice.bill_city, Getprice.billregion, Getprice.bill_post,;
  Getprice.bilcountry, Getprice.phone, Getprice.fax,
Getprice.invoice_cp,;
  Getprice.non_profit, Getprice.add_po, Getprice.acct_phone,;
  Getprice.acct_fax, Getprice.comments, Getprice.order_no,;
  Getprice.email_add, Getprice.fedxnumb, Getprice.order_numb,;
  Getprice.order_date, Getprice.shipping_d, Getprice.invoice_da,;
  Getprice.shipping_c, Getprice.amount_due, Getprice.replacemen,;
  Getprice.unit_size, Getprice.pricing_sc, Getprice.measuremen,;
  Getprice.unit_price, Getprice.item_numb, Temtable.*,
Getprice.product_na,;
  Getprice.quant_ord, Getprice.quantship, Getprice.qntbakord,;
  Getprice.productfom, Getprice.purchase_o, Getprice.shipment_n;
 FROM Temtable, Getprice;
 GROUP BY Getprice.item_numb;
 INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Combine.dbf"

Is there anybody know what I should be careful when I set the
conditions for query ? and anything I should keep away from ?

  --->>>  I am using FoxPro 2.5 on Power Macintosh  <<<---

Thanks again! And have a happy new year!!!

 
 
 

HELP NEEDED ON SPEED OF QUERY Please!

Post by RICHAR » Wed, 03 Jan 1996 04:00:00



> Hi everybody,
> Thanks to Alan Kwan and Shar Feldheim,my last problem is solved.
> Thank you very much!!!
> Now my question is :

>  I don't know why my query seems very slow, not like a computer
>  program running!usually takes far longer than the user can stand.
>  That Rushmore technology doesn't seem work effectively.
>  My tow main queries are as following:

> SELECT Customer.*, Orders.order_numb, Orders.order_date,;
>   Shipment.shipment_n, Shipment.shipping_d, Shipment.invoice_da,;
>   Shipment.shipping_c, Shipment.amount_due, Shipment.replacemen,;
>   Pricing.unit_size, Pricing.pricing_sc, Pricing.measuremen,;
>   Pricing.unit_price, Order_deta.item_numb, Order_deta.product_na,;
>   Order_deta.quant_ord, Order_deta.quantship, Order_deta.qntbakord,;
>   Order_deta.productfom, Orders.purchase_o;
>  FROM Customer, Orders, Shipment, Pricing, Order_Details Order_deta;
>  WHERE Orders.compliance = Customer.compliance;
>    AND Shipment.order_numb = Orders.order_numb;
>    AND Pricing.non_profit = Customer.non_profit;
>    AND Shipment.replacemen = Pricing.replacemen;
>    AND Order_deta.order_numb = Orders.order_numb;
>    AND Pricing.productfom = Order_deta.productfom;
>    AND (Pricing.pricing_sc > CTOD("12/30/91");
>    AND Orders.order_numb = transf);
>  GROUP BY Order_deta.item_numb;
>  INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Getprice.dbf"

> and

> SELECT Getprice.compliance, Getprice.last_name, Getprice.first_name,;
>   Getprice.institute, Getprice.department, Getprice.address,
> Getprice.city,;
>   Getprice.region, Getprice.post_code, Getprice.country,
> Getprice.bill_add,;
>   Getprice.bill_city, Getprice.billregion, Getprice.bill_post,;
>   Getprice.bilcountry, Getprice.phone, Getprice.fax,
> Getprice.invoice_cp,;
>   Getprice.non_profit, Getprice.add_po, Getprice.acct_phone,;
>   Getprice.acct_fax, Getprice.comments, Getprice.order_no,;
>   Getprice.email_add, Getprice.fedxnumb, Getprice.order_numb,;
>   Getprice.order_date, Getprice.shipping_d, Getprice.invoice_da,;
>   Getprice.shipping_c, Getprice.amount_due, Getprice.replacemen,;
>   Getprice.unit_size, Getprice.pricing_sc, Getprice.measuremen,;
>   Getprice.unit_price, Getprice.item_numb, Temtable.*,
> Getprice.product_na,;
>   Getprice.quant_ord, Getprice.quantship, Getprice.qntbakord,;
>   Getprice.productfom, Getprice.purchase_o, Getprice.shipment_n;
>  FROM Temtable, Getprice;
>  GROUP BY Getprice.item_numb;
>  INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Combine.dbf"

> Is there anybody know what I should be careful when I set the
> conditions for query ? and anything I should keep away from ?

>   --->>>  I am using FoxPro 2.5 on Power Macintosh  <<<---

> Thanks again! And have a happy new year!!!

had this problem on site recently you must make sure that your indexes
have exactly the same syntax as the select statement

ie index on dtos(date) to date
will need HAVING BETWEEN(dtos(date),dtos(date1),dtos(date2)) (i think)

RICH  <*********** Relax Be happy **************>


                             *********************
                             * BAMBER BRIDGE     *
                             * PRESTON           *
                             * LANCS             *
                             * Tel 01772 316632  *
                             *********************

 
 
 

HELP NEEDED ON SPEED OF QUERY Please!

Post by WILLIAM C » Mon, 08 Jan 1996 04:00:00


:  FROM Customer, Orders, Shipment, Pricing, Order_Details Order_deta;

A quick speech on querying. When a SELECT command accesses more than one
table, the default result is a cross-product. What this means is that
every record in the first table will be combined with every record in the
second table in all possible combinations. (Hence, if you have 1000
records in one table and 10,000 in another, you end up with 10,000,000
records as your result.)

Quote:>  WHERE Orders.compliance = Customer.compliance;
>    AND Shipment.order_numb = Orders.order_numb;
>    AND Pricing.non_profit = Customer.non_profit;

The next part of this sermon is about filtered cross-products. There are
two ways you come to your desired results. You can produce a cross-product
of all possible combinations and then extract the records you want. The
other, more intelligent method is to filter your source tables and then
do a cross-product.

The problem is that you are depending on FoxPro to do decide which method
to use and the order of implementation. (I.e., it's obvious you should
first extract the results of your smallest databases and then use that to
filter your larger databases.) FoxPro will probably make the correct
decision for most queries. However, there is no guarantee -- without
knowing the details of Rushmore optimization, you have no way of knowing
whether FoxPro is * on your query. (You probably hit one of these
instances with your query.)

Unfortunately, if FoxPro can't handle your query at a decent speed, your
only solution is to break down the query into a set of smaller queries.
(Like all things in life, there are trade-offs.)

 
 
 

HELP NEEDED ON SPEED OF QUERY Please!

Post by Gail Co » Mon, 08 Jan 1996 04:00:00



> Hello,
> Thanks a lot for your great help !Thanks also to Ed Watkins,Cliff
> Bailey,Andrew Coates.Thank you all!

> According to all of you guys' help,I indexed all the fields in the
> WHERE clause,but the speed doesn't improve much,so I try to cut the
> big query into small queries.

> My question is the way to do it(sorry I am a new FoxPro programmer):
> Is that I should use SELECT on two tables into a temporary table,say
> A,then SELECT on table A and the third table from my previous big
> query into the second temporary table,say B,and so on until running
> out of the tables in my previous big query.Am I correct ?
> Thanks again!
> Zhidong

You should plan on reducing the size of your result set as much
as possible on the first query.  Once you have done that you may
be able to combine the other tables in the second select.  You
might try this approach and then further fine tune if your
results are not satisfactory.

One thing that would be of interest to me is trying your
original query with your DELETED set to OFF.  I know that is
probably not what you want but I have experienced terrible
performance with a combination of SET DELETED ON and an index
tag of deleted() on one or more of the tables.  

Gail

 
 
 

HELP NEEDED ON SPEED OF QUERY Please!

Post by Zhidong L » Tue, 09 Jan 1996 04:00:00


Hello,
Thanks a lot for your great help !Thanks also to Ed Watkins,Cliff
Bailey,Andrew Coates.Thank you all!

According to all of you guys' help,I indexed all the fields in the
WHERE clause,but the speed doesn't improve much,so I try to cut the
big query into small queries.

My question is the way to do it(sorry I am a new FoxPro programmer):
Is that I should use SELECT on two tables into a temporary table,say
A,then SELECT on table A and the third table from my previous big
query into the second temporary table,say B,and so on until running
out of the tables in my previous big query.Am I correct ?
Thanks again!
Zhidong

 
 
 

HELP NEEDED ON SPEED OF QUERY Please!

Post by Zhidong L » Tue, 09 Jan 1996 04:00:00


Hello,
Thanks a lot for your great help !Thanks also to William Yu,Ed Watkins,
Cliff Bailey,Andrew Coates.Thank you all!

According to all of your help,I indexed all the fields in the
WHERE clause,but the speed doesn't improve much,so I try to cut the
big query into small queries.

My question is the way to do it(sorry I am a new FoxPro programmer):
Is that I should use SELECT on two tables into a temporary table,say
A,then SELECT on table A and the third table from my previous big
query into the second temporary table,say B,and so on until running
out of the tables in my previous big query.Am I correct ?
Thanks again!

Zhidong

 
 
 

1. MORE HELP ON QUERY SPEED Please!

Hi everybody,
According to many help on my query speed problem,I changed my previous
code into the following :

SELECT DISTINCT Customer.*, Orders.order_numb, Orders.order_date,;
              Orders.purchase_o;
FROM Customer, Orders;
WHERE Orders.order_numb = transf;
  AND Orders.compliance = Customer.compliance;
 ORDER BY Orders.order_numb;
 INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Getprice1.dbf"

SELECT DISTINCT Getprice1.*, Shipment.shipment_n,Shipment.shipping_d,;
  Shipment.invoice_da,Shipment.shipping_c,;
  Shipment.amount_due,Shipment.replacemen;
FROM Getprice1,Shipment;
WHERE Shipment.order_numb = Getprice1.order_numb;
 ORDER BY Getprice1.order_numb;
 INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Getprice2.dbf"

SELECT DISTINCT Getprice2.*,Pricing.unit_size,Pricing.pricing_sc,;
       Pricing.measuremen,Pricing.unit_price,Pricing.productfom;
FROM Getprice2,Pricing;
WHERE  Pricing.non_profit = Getprice2.non_profit;
  AND Getprice2.replacemen = Pricing.replacemen;
  AND Pricing.pricing_sc > CTOD("12/30/91");
 ORDER BY Getprice2.order_numb,Pricing.productfom;
 INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Getprice3.dbf"

SELECT DISTINCT Getprice3.*,Order_de.item_numb,Order_de.product_na,;
  Order_de.quant_ord, Order_de.quantship, Order_de.qntbakord;
FROM Getprice3,Order_de;
WHERE Order_de.order_numb = Getprice3.order_numb;
  AND Order_de.productfom = Getprice3.productfom;
 ORDER BY Order_de.order_numb;
 INTO TABLE "Macintosh HD:FoxPro:DSHB FOXPRO:Getprice.dbf"

and the first three SQL works fine,just cost reasonable seconds,
then the Getprice3.dbf has 7 records and Order_de has 5812 records,but
THE LAST QUERY costs a lot time to get answer,over 30 seconds,I wonder
how to SPEED IT UP,can anyone give me any suggestions?

By the way,the Customer has 2648 records,Orders 2454,Shipment
2622,Pricing 56.
I didn't use cursor.

Thanks a lot in advance!!!
Have a nice day!

Zhidong

2. Houston- Programmer Analyst Wanted

3. need help increasing speed of queries

4. Unattended Reporting

5. Need help in speeding up this query!!

6. Can I backup across the network?

7. Need Help speeding up Foxpro file queries in VB

8. Installing SQL Server 2000 Over Win 2000 Server

9. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

10. HELP PLEASE: URGENT: Need help forming a multi table query

11. APPROACH QUERY-PLEASE PLEASE PLEASE HELP

12. Please, Please, Please I need help working with dates

13. Please Please help !!! Crystal Report Help Needed