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
-------------------------------------------------------------------------
--(-@
