How can I speed up this query

[tekytippy]

This query takes over 3 seconds to run

z_sales_unalloc[1] = 0.00
SELECT SUM(check_order_line_item.l_qty_unallocated);
FROM check_order_line_item;
LEFT JOIN check_order_header;
ON check_order_header.h_order_no == check_order_line_item.l_order_no;
WHERE check_order_header.h_type == "O";
AND check_order_header.DIRECT == .F.;
AND check_order_line_item.l_direct_product == .F.;
AND check_order_line_item.l_productid == cur_distinct_products_and_locations_to_recalculate.product_id;
AND check_order_line_item.l_stock_location_id == cur_distinct_products_and_locations_to_recalculate.sl_id;
INTO ARRAY z_sales_unalloc

check_order_line_item has 140,000 records
check_order_header has 25,00 records

the following fields are indexed:
check_order_line_item.l_qty_unallocated
check_order_header.h_order_no
check_order_line_item.l_order_no
check_order_header.h_type
check_order_line_item.l_direct_product
check_order_line_item.l_productid
check_order_line_item.l_stock_location_id



This query takes 0.0012 seconds to run or 5000 records

SUM check_product_location.pl_stock_qty,;
check_product_location.pl_purch_on_order_qty,;
check_product_location.pl_sales_allocated_qty,;
check_product_location.pl_sales_unallocated_qty,;
check_product_location.pl_bom_allocated_qty;
FOR check_product_location.pl_product_id == cur_distinct_products_and_locations_to_recalculate.product_id;
TO z_current, z_purch_on_order, z_sales_alloc, z_variable_sales_unalloc, z_bom_alloc

All fields in this query are indexed too.



Allowing for the record size difference in the table I still think that 3 seconds for the top query is unacceptable. How can I speed it up?

 

[MirekZvolsky]

If it is query into native VFP data (DBF), then it is good to have indexes in tables.
Create index tag on
-- each expression on left side in WHERE
-- each expression on both sides in JOINs
-- DELETED() if SET DELETED is ON while running query

So in your case index Check_order_header on h_type, DIRECT, DELETED(), Check_order_line_item on l_direct_product, l_productid, DELETED(),...

Index tags shell be made with same collating sequence as is active while running SELECT command, and best performance you get with SET COLLATE TO 'machine'. Index tag key (+ collating sequence) is important only, not index tag name (however, if you index based on one field, you usual use same tag name as the field name is).