Hi, Yong,
I read your code and found a non stable result at line:
from _ProdBom join _ProdTable
group by ItemId
Because, You want to sum ProdBom table, joined with ProdTable that is grouped by ItemId, but you don't specify which prodTable.ItemId is. So, I change the code into:
join tableId from _ProdTable //group by ItemId -> which itemId ? All ?
If you want to sum the quantity for all prodTable which having relation and conditions you have written (all filter except ItemId).
So, if you want to speed up the query, this is my suggestion:
Select Sum(QtyBomCalc) from _ProdBom
where _ProdBom.ItemId == _itemId //probably you may add ProdStatus filter (see: ProdStatusIdx)
join tableId from _ProdTable //group by ItemId -> which itemId ? All ?
where _ProdBom.ProdId == _ProdTable.ProdId
&& _ProdTable.ProdStatus >= ProdStatus::Scheduled
&& _ProdTable.ProdStatus <= ProdStatus::StartedUp
&& _ProdTable.ProdType == ProdType::Vendor;
For reducing memory usage, I still don't have a time to check that.
Other suggestions may give a better result, though.
Regards,
Sonny Wibawa Adi
pepejeans2k <[EMAIL PROTECTED]> wrote:
Hi,
I've created a static class that are reuseable in many reports. The
problem is that it's slow and causing SQL to eat up a lot of physical
memory.
Can anyone of you pls take a look at these static class and advice
what is the best practice to reduce the memory usage in SQL.
Below is one of the example where I select all ItemId from Item
Master, call below static class and pass in ItemId one by one as I
need to display each item in rows.
static InventQty GetExtWIPQty(ItemId _itemid)
{
ProdTable _ProdTable;
ProdBom _ProdBom;
;
Select ItemId, Sum(QtyBomCalc)
from _ProdBom join _ProdTable
group by ItemId
where _ProdBom.ItemId == _itemId
&& _ProdBom.ProdId == _ProdTable.ProdId
&& (_ProdTable.ProdStatus == ProdStatus::Scheduled
|| _ProdTable.ProdStatus == ProdStatus::Released
|| _ProdTable.ProdStatus == ProdStatus::StartedUp)
&& _ProdTable.ProdType == ProdType::Vendor;
return _ProdBom.QtyBOMCalc;
}
Thks in adv,
Best rgs,
Yong.
Yahoo! Groups Sponsor
Get unlimited calls to
U.S./Canada
---------------------------------
Yahoo! Groups Links
To visit your group on the web, go to:
http://groups.yahoo.com/group/development-axapta/
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
[Non-text portions of this message have been removed]
| Yahoo! Groups Sponsor |
| Get unlimited calls to U.S./Canada |
Yahoo! Groups Links
- To visit your group on the web, go to:
http://groups.yahoo.com/group/development-axapta/
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
