Hi Deshpande, Thanks for the explanation and detail suggestion :) The delete statement means I'd like to delete current record and all records related to their parent, so that I used refId and linkRefId. I've ever separated the delete refid and linkrefid, but it gave me more locked error, so that I combined it in 1 query. I'll try accommodate the second option in my behavior :)
Thanks and Regards, Afin On Thu, Sep 2, 2010 at 9:57 PM, Harry Deshpande <har...@microsoft.com>wrote: > > > You have a problem L I suppose _refId in the first statement and > _reqTrans.RefId in the second statement is same? (at least for some > transactions?) > > > > You have a couple of options: > > > > 1. Put the first delete statement in a separate transaction block. > This has the side-effect that if your process crashes then the records will > not be rolled back. But with master planning data consistency is not a big > issue, if at all the process crashes (very rare) then the user will run it > again any way. > > 2. Create an isDeleted flag on xtrProdBreakDown table and instead of > delete_from set this flag to true -> in the second statement add one more > predicate isDeleted = false and then find a suitable place to issue a delete > statement i.e. delete_from where isDeleted = true. > > > > Regards > > > > harry > > > > *From:* Axapta-Knowledge-Village@yahoogroups.com [mailto: > axapta-knowledge-vill...@yahoogroups.com] *On Behalf Of *thomas 'znal' > ramdhan > *Sent:* Wednesday, September 01, 2010 6:21 PM > > *To:* Axapta-Knowledge-Village@yahoogroups.com > *Subject:* Re: [Axapta-Knowledge-Village] delete_from vs loop delete > > > > > > Hi Deshpande, > > This is my code, I insert below code in Tables/ReqTrans/insertFromreqPO > > *delete_from xtsProdBreakDown > where xtsProdbreakDown.Type == _BreakDownType > && (xtsProdBreakDown.RefId == _refId || > xtsProdBreakDown.LinkRefId == _refId);* > > ...................... > ....................... > ........................ > > *select forUpdate * from xtsProdBreakDown > where xtsProdBreakDown.RefId == _reqTrans.RefId > && xtsProdBreakDown.ItemId == _reqTrans.ItemId > && xtsProdBreakDown.RAFDate == UsedRAFDate > && xtsProdBreakDown.Shift == xtsShift.Shift > && xtsProdBreakDown.WrkCtrIdMachine == > WrkCtrCapResLoop.WrkCtrId; > > > *The meaning of my above code is I like to refresh the xtsProdBreakDown > table every master planning is generated, so that I need to delete it all > first, and then regenerate it again. > The most lock process that I found in SQL activity monitor is in delete > codes. Do you have any suggestions ? > > > On Wed, Sep 1, 2010 at 9:55 PM, Harry Deshpande <har...@microsoft.com> > wrote: > > > > Are u doing anything in the delete method for your customized table? Can > you post the code? Another way would to find out which statement is getting > blocked, you will find a lot of information on the net. I had posted some > info long time back > http://www.systomatics.com/Blog/010ExpensiveQueries/010_ExpensiveQueries.htmnot > sure if this is still valid. > > > > Regards > > > > harry > > > > *From:* Axapta-Knowledge-Village@yahoogroups.com [mailto: > axapta-knowledge-vill...@yahoogroups.com] *On Behalf Of *thomas 'znal' > ramdhan > *Sent:* Wednesday, September 01, 2010 7:43 AM > > > *To:* Axapta-Knowledge-Village@yahoogroups.com > *Subject:* Re: [Axapta-Knowledge-Village] delete_from vs loop delete > > > > > > Hi Harry, > > I didn't delete reqTrans records. It's my own customized table, but the > process is inside the master planning scheduler. > > On Wed, Sep 1, 2010 at 9:31 PM, Harry Deshpande <har...@microsoft.com> > wrote: > > > > Hi > > From your reference to Batch helpers, it looks like you are trying to > delete master planning records > > I further suspect that you are trying to delete reqTrans. Delete_from will > still give you a lock error since deleting one record of reqTrans triggers > another delete statement on another record. > > Say we have 2 record record no 1 and 2, which are related. > > You execute delete_from statement which will > > Delete record no 1 and > > Delete record no 2 > > However, when record no 1 is deleted, it creates a delete statement on > record no 2. > > Now delete_from will try to delete record no 2 which is next in sequence > and you will get the lock error. > > You should you skipDataMethods and skipDeleteActions in such scenarios. > However, you really need to know what you are doing J > > Seeing locks in activity monitor does not mean anything. What is the > problem that you are facing (other than lock error while deleting)? > > Regards > > harry > > > > *From:* Axapta-Knowledge-Village@yahoogroups.com [mailto: > axapta-knowledge-vill...@yahoogroups.com] *On Behalf Of *thomas 'znal' > ramdhan > *Sent:* 01 September 2010 10:34 > > > *To:* Axapta-Knowledge-Village@yahoogroups.com > > *Subject:* Re: [Axapta-Knowledge-Village] delete_from vs loop delete > > > > > > Hi Burtt, > > Thanks for brief explanation :) > I have declare all suitable index for my table, but sometimes still returns > me lock error. If I use batch helpers, for sure I get more lock issue in > activity monitor. If this is the case, is loop delete the righteous solution > ? > > Thanks and Regards, > Afin > > On Wed, Sep 1, 2010 at 4:07 PM, Malcolm Burtt < > malcolm.bu...@touchstone.co.uk> wrote: > > > > Hi > > > > The difference between the two approaches is in the speed of execution. > > > > Assuming that the tables delete() method has not been overridden and that > there is no database logging of deletes for the table then “delete_from” x++ > code will generate a single database round trip which will delete all of the > records matched by the where clause. The looping approach will issue one > database round trip for each record to be deleted in addition to the initial > select and is, consequently, slower than the “delete_from” approach. > > > > It’s worth noting that “delete_from” will automatically revert to a looped > approach if the table’s delete() method has been overridden or AX has been > configured to log deletes for the table. This can be avoided by call the > table’s skipDataMethod() and skipDatabaseLog() passing “true” as the > argument to each of these calls but you can only do that if you are sure > that the code in the delete() method isn’t needed in this instance and that > its okay to not log the delete. > > > > You might have seen table locks with “delete_from” if your where clause has > no viable index. In such cases the database has no choice but to lock the > whole table while it deletes the rows. > > > > My advice: use “delete_from” wherever you can but, as with all database > access, make sure you have suitable indexes. > > > > Regards > > > > > > Malcolm Burtt > > Touchstone Group > > People - Partnership - Solutions > > > > > > > > *From:* Axapta-Knowledge-Village@yahoogroups.com [mailto: > axapta-knowledge-vill...@yahoogroups.com] *On Behalf Of *thomas 'znal' > ramdhan > *Sent:* 01 September 2010 06:20 > *To:* Axapta-Knowledge-Village@yahoogroups.com > *Subject:* [Axapta-Knowledge-Village] delete_from vs loop delete > > > > > > Dear all, > > I'd like to ask about delete issue in AX. > AX has 2 way to delete records by using *'delete_from'* or below code > > *while select forUpdate * from tableA > { > tableA.doDelete(); > } > > *Which one do you prefer to delete some records from both above code ? > > Sometimes, I found locked table if I use *delete_from* code. > > Thanks and Regards, > Afin > * > *-- > ne faites pas une limite pour votre competence > ------------------------------------------------------------------------- > bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit > ------------------------------------------------------------------------- > non faccia un limite per la vostra abilita > ------------------------------------------------------------------------- > --(-@ > > > > > -- > ne faites pas une limite pour votre competence > ------------------------------------------------------------------------- > bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit > ------------------------------------------------------------------------- > non faccia un limite per la vostra abilita > ------------------------------------------------------------------------- > --(-@ > > > > > -- > ne faites pas une limite pour votre competence > ------------------------------------------------------------------------- > bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit > ------------------------------------------------------------------------- > non faccia un limite per la vostra abilita > ------------------------------------------------------------------------- > --(-@ > > > > > -- > ne faites pas une limite pour votre competence > ------------------------------------------------------------------------- > bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit > ------------------------------------------------------------------------- > non faccia un limite per la vostra abilita > ------------------------------------------------------------------------- > --(-@ > > > > > -- > ne faites pas une limite pour votre competence > ------------------------------------------------------------------------- > bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit > ------------------------------------------------------------------------- > non faccia un limite per la vostra abilita > ------------------------------------------------------------------------- > --(-@ > > > -- ne faites pas une limite pour votre competence ------------------------------------------------------------------------- bilden Sie nicht eine Begrenzung fur Ihre Fahigkeit ------------------------------------------------------------------------- non faccia un limite per la vostra abilita ------------------------------------------------------------------------- --(-@
