0

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;
3
Contributors
4
Replies
26
Views
6 Months
Discussion Span
Last Post by ppohlmann
1

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

Votes + Comments
Exactly.
0

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 *

1

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>
Votes + Comments
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.
Cheers
Peter

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.