Speed up query

[ppohlmann]

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.Clear;
      SQLQuery1.SQL.Text:= 'SELECT * FROM orders_batch WHERE invoice_no=:elorder_no';
      SQLQuery1.params.parambyname('elorder_no').Asstring := elorder_no;
      SQLQuery1.open;

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

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

                  elqty:=SQLQUery2.FieldByName('qty').asInteger;

            SQLQuery1.Next;

[k99rs]

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

[rproffitt]

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 *

[k99rs]

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>

[ppohlmann]

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