michael
12/9/2005 6:32:00 PM
Hi Rob
Thanks for the help. I didn't even notice that I took the wrong one. I
thought the reason it didn't work was because I was using group by's wrong or
something.
But thanks for the help, it works now
Michael
"rheu" wrote:
> Hi Michael,
>
> In your original code your checking the item type of the item from the BOM
> table, in your optimized code your checking the item type of the ProdTable,
> which is not the same.
> I'm also wondering whether your should use ProdBOM table instead of BOM table.
>
> Regards,
>
> Rob
>
>
> "Michael" wrote:
>
> > Hi
> >
> > I am doing some optimization and I can't quite get it to work and I was
> > therefore wondering if there where any sql experts here because I am doing
> > something wrong.
> >
> > Original code:
> > --------------------------------------------
> > prodTable = prodTable::find(_prodId) ;
> >
> > while select BOM
> > index hint BOMId
> > where BOM.BOMId == prodTable.BOMId
> > {
> > if (BOM.inventTable().ItemType() == ItemTypeBase::Recipe)
> > {
> > itemId += BOM.ItemId ;
> > }
> >
> > }
> > --------------------------------------------
> > Obviously this is not the best way to do it so I have tried to do it all in
> > one sql call.
> >
> > This is what I am trying to do to optimize it:
> >
> > select firstonly ItemId
> > from BOM
> > index hint BOMId
> > group by ItemId, BOMId
> > join ProdTable
> > group by BOMId, ProdId
> > where ProdTable.BOMId == BOM.BOMId &&
> > ProdTable.ProdId == _prodId
> > join InventTable
> > group by ItemId, cbgItemType
> > where inventTable.ItemId == ProdTable.ItemId &&
> > inventTable.ItemType == ItemTypeBase::Recipe;
> >
> > --------------------------------------------
> > But this is not working. I could split it up in a couple of calls instead of
> > one, which would be faster than the old code, but I would like to get it to
> > work in one call since this a called a lot of times and therefore needs to be
> > very effective
> >
> > Anybody have any ideas what I am doing wrong?
> >
> > Thanks,
> > Michael
> >
> >