Optimize database communication using Fieldlist

Question

I am reading Development 3 course and at some point, this discussions comes into place - optimize communication with database using fieldlist.

Ex:

while select AmountCurCredit from ledgerJournalTrans
{
    amountCurCredit += ledgerTrans.AmountCurCredit; 
}

I understand this, we are selecting only the AmountCurCredit field and not the others. So if we try to sum up the AmountCurDebit we will have 0 as a result.

OK, but what about this :

while select recId from inventTable
   join qty from inventTrans 
     where inventTrans.itemId == inventTable.itemId
     {  
         qty += inventTrans.qty;
     }

itemId is not part of the select statement. How does it get a value ? Is it because it's defined as an index for both InventTable and InventTrans tables?

I mean, shouldn't we use:

while select ItemId from inventTable
   join qty, ItemId from inventTrans 
     where inventTrans.ItemId == inventTable.ItemId
     {  
         qty += inventTrans.qty;
     }

Matej Matej · Accepted Answer · 2016-01-15T12:35:18

You don't need to select ItemId as you don't use it in while body.

Your query translates to SQL:

SELECT InventTable.RecId
    , InventTrans.Qty 
FROM InventTable
    INNER JOIN InventTrans
        ON InventTrans.ItemId = InventTable.ItemId

More optimal statement will be:

while select qty from inventTrans 
    exists join recId 
    from inventTable
    where inventTable.ItemId == inventTrans.ItemId 
{  
    qty += inventTrans.qty;
}

which translates into

SELECT T1.QTY
FROM INVENTTRANS T1
WHERE EXISTS
(
 SELECT 'x'
 FROM INVENTTABLE T2
 WHERE T2.ITEMID=T1.ITEMID
)

Even more optimal:

select sum(Qty)
    from inventTrans 
    exists join recId 
    from inventTable
        where inventTable.ItemId == inventTrans.ItemId
;

which translates into

SELECT SUM(T1.QTY)
FROM INVENTTRANS T1
WHERE EXISTS
(
 SELECT 'x'
 FROM INVENTTABLE T2
 WHERE T2.ITEMID=T1.ITEMID
)

Optimize database communication using Fieldlist

1 Answers