Hello SQL Gurus,

This Query below is doing the job but it is horrible slow. Is there any way to make the query faster ?
Perhaps a join .. inner outer, left ?
I am looooost.
Thanks for any help.

      SQLQuery1.SQL.Text:= 'SELECT * FROM orders_batch WHERE invoice_no=:elorder_no';
      SQLQuery1.params.parambyname('elorder_no').Asstring := elorder_no;

         while not SQLQuery1.EOF do
               elsku:= SQLQuery1.FieldByName('itemno').asString;

                  SQLQuery2.SQL.Text:= 'SELECT * FROM stock_qty WHERE sku=:sku';
                  SQLQuery2.params.parambyname('sku').Asstring :=elsku;



Instead of returning all the fields of the table you are querying, only return the fields you are interested in.

commented: Exactly. +12

Looking at line 11. Do you need "SELECT *"?

Why I ask is that line 15 appears to only effect 1 column yet you used SELECT *

You can also try the below SQL.

SELECT b.qty
FROM orders_batch a 
        INNER JOIN stock_qty b ON a.itemno = b.sku
WHERE a.invoice_no = <Parameter Value>
commented: My bet this would be faster due to selecting only what is needed. +0

Hello and thanks everyone. Yes selectiong only the required fields did speed it up a lot.
Thank you again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.