One important thing you must be aware of is that you force an ORDER
BY when you use the `index'- keyword. If your intention is to pass a
hint to the optimizer then you must use the `index hint'- keyword!
Another thing is you select all records forUpdate in the main loop
on Stable1 you could consider only selecting all the records and
then re-selecting when you need to do updates on STable1.
Best regards/Med venlig hilsen
THY:Development
Karsten Wollesen Clausen
System Developer
--- In [email protected], "Maverick"
<[EMAIL PROTECTED]> wrote:
>
> Stable1 consists of millions of records. And I have to do
conditional updates for every record.
>
> ttsbegin;
> while select forupdate STable1 index RecIdx
> {
> if(stable1.TokenType==TOITokenType::Comments)
> {
> this.changeComments(stable1);
> }
> //ISS00019 starts
> if(TOI_SpecialTokens::find(STable1.TokenName))
> {
> STable3.clear();
> select firstonly STable3 index RecIdx
> where STable3.RecId > STable1.RecId &&
> Stable3.TokenName == ")";
> STable4.clear();
> while select forupdate STable4 index RecIdx
> where Stable4.RecId > STable1.RecId &&
> Stable4.RecId < STable3.RecId &&
> Stable4.TokenType != TOITokenType::Operator
> {
> STable4.TokenType = TOITokenType::SpecialToken;
> STable4.doUpdate();
> }
> }
> //ISS00019 ends
> if(stable1.TokenName==".")
> {
> stable2.clear();
> select firstonly stable2 Index RecIdx
> where stable2.RecId==stable1.RecId+2;
> if(stable2.TokenName == "(" ||
> stable2.TokenName == "()" )
> {
> stable2.clear();
> select firstonly forupdate stable2 Index RecIdx
> where stable2.RecId == stable1.RecId+1;
> stable2.TokenType = TOITokenType::MethodName;
> stable2.doUpdate();
> }
> else
> {
> stable2.clear();
> select firstonly forupdate stable2 Index RecIdx
> where stable2.RecId == stable1.RecId+1 &&
> stable2.TokenType != TOITokenType::Operator;
> if(stable2)
> {
> stable2.TokenType =TOITokenType::FieldName;
> stable2.doUpdate();
> }
> }
> }
> if(stable1.TokenType==TOITokenType::VariableName)
> {
> //Case For Index
> stable2.clear();
> select firstonly stable2 Index RecIdx
> where stable2.RecId == stable1.RecId-1;
> if(STable2.TokenName == "index" ||
> /*ISS00018 START*/
> STable2.TokenName == "hint")
> /*ISS00018 END*/
> {
> stable1.TokenType = TOITokenType::IndexName;
> stable1.doUpdate();
> }
> }
>
> //Case For MethodName
> if(STable1.TokenName == "(" ||
> STable1.TokenName == "()")
> {
> stable1.TokenType = TOITokenType::Operator;
> stable1.doUpdate();
> stable2.clear();
> select firstonly forupdate stable2
> where stable2.RecId == STable1.RecId - 1
&&
> stable2.TokenType != TOITokenType::FunctionName
&&
> stable2.TokenType != TOITokenType::KeyWord;
> if(stable2)
> {
> stable2.TokenType = TOITokenType::MethodName;
> stable2.doUpdate();
> }
> }
> //Case for Primitive Types
> if(TOI_PrimitiveDataTypes::find(STable1.TokenName))
> {
> stable1.TokenType = TOITokenType::DictionaryType;
> stable1.doUpdate();
> }
> //Case for Either Method or Enum
> if(STable1.TokenName == "::")
> {
> stable2.clear();
> select firstonly stable2
> where stable2.RecId == STable1.RecId+2 &&
> (stable2.TokenName == "(" ||
> stable2.TokenName == "()");
> if(!stable2)
> {
> UpdateSTable.clear();
> select firstonly forupdate UpdateSTable
> where UpdateSTable.RecId == STable1.RecId+1;
> UpdateSTable.TokenType = TOITokenType::EnumValue;
> UpdateSTable.doUpdate();
> }
> UpdateSTable.clear();
> select firstonly forupdate UpdateSTable
> where UpdateSTable.RecId == STable1.RecId - 1;
> UpdateSTable.TokenType = TOITokenType::DictionaryType;
> UpdateSTable.doUpdate();
> }
> }
> ttscommit;
>
>
> I was thinking of breaking this code in several different
functions for every condition, what do you suggest ?
> Going for select case for every condition will be good or going
the way it is , is good?
>
> Maverick
>
> ----- Original Message -----
> From: Bayliss, Barry
> To: [email protected]
> Sent: Friday, January 06, 2006 3:39 AM
> Subject: RE: [development-axapta] Performance Issue
>
>
> Maverick,
>
>
> Can you post the concerned piece of code?
>
>
> Barry.
>
>
> -----Original Message-----
> From: [email protected] [mailto:development-
[EMAIL PROTECTED] On Behalf Of Maverick
> Sent: Thursday, 5 January 2006 3:55 PM
> To: [email protected]
> Subject: Re: [development-axapta] Performance Issue
>
> Thanks Jacob,
>
> In the case of ttscommit:
> What if runtime memory gets full and still updates have to be
done....Since I think that updates are done in run time memory.
> Maverick
>
> ----- Original Message -----
> From: Jacob Hjelmer Nielsen
> To: [email protected]
> Sent: Wednesday, January 04, 2006 7:47 PM
> Subject: RE: [development-axapta] Performance Issue
>
>
> Hi
> Regarding recordset operations:
> Note that this only works if no code exist on the
insert/update events methods on the table, otherwise it will fall
back to the traditional 1 by 1 insertion/update.
> However this can be bypassed by using the skipDataMethods()
method -but careful now you must be 100% sure on what you're doing.
> Alternatively, you could try using the recordInsertList, this
is actually documented in \System
Documentation\Classes\RecordInsertList
>
> ttsCommit
> Should be called after the last update,
>
> Client/server:
> Make sure that your code is running on the server, thus
eliminating cross-tier calls.
>
> Primary Index:
> I assume you have a primary index!
>
> Cluster Index:
> When used, this is often your primary index.
> If your primary key's values are consecutive the data is
handled ok - however if the values assigned to the primary key are
not consecutive every insert will cause the database to reorganize
data for optimal fetch of the records afterwards.
>
> If the problem persists, you should tell us a bit more about
the environment.
>
>
> Med venlig hilsen/Best regards
>
> Jacob Hjelmer
>
>
> thy:development
> Søvej 13B
> DK-3460 Birkerød
>
> Tlf.: +45 96 170 470
> Fax: +45 45 824 544
> Mobil: +45 24 474 032
> E-mail: [EMAIL PROTECTED]
>
> www.thydevelopment.com <http://www.thydevelopment.com/>
> www.x-masterclass.com <http://www.x-masterclass.com/> High-
end education program for Axapta developers.
> ________________________________
>
> From: [email protected] [mailto:development-
[EMAIL PROTECTED] On Behalf Of Dilip N
> Sent: 4. januar 2006 13:39
> To: [email protected]
> Subject: Re: [development-axapta] Performance Issue
>
> Maverick:
>
> Try using INSERT_RECORDSET / UPDATE_RECORDSET..This will
speed up the operation.
>
> Good luck
>
> Regards,
> Dilip
>
> Maverick <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have some code which is processing thousands of update on
the table. This process is doing all the updates on one table.
Updates consists of inserting new record and updation of a record.
>
> The process consist of a loop, doing updation on a table on
every record conditionally. And this is done several times. And
table consists of million of records.
>
> What are the ways to optimize the process. Please do tell me
all the points to be taken care in the CODE,TABLE and in the SQL.
>
> Like:
>
> When should TTSCOMMIT be done. If it is done after every
update it will take too much time. And if it is after thousands of
update, it takes runtime memory.
>
> What about Index, should it be primary OR cluster.
>
> What properties of SQL Server should be set for better
updation.
>
> If you would like to put light on any other point, it willl be
very helpful.
>
> Thanks
>
> Maverick
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> SPONSORED LINKS
> Computer part Programming languages Microsoft
axapta Support exchange
>
> ---------------------------------
> YAHOO! GROUPS LINKS
>
>
> Visit your group "development-axapta" on the web.
>
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms
of Service.
>
>
> ---------------------------------
>
>
>
>
>
>
> ---------------------------------
> Yahoo! DSL Something to write home about. Just $16.99/mo. or
less
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> ________________________________
>
> YAHOO! GROUPS LINKS
>
> * Visit your group "development-axapta
<http://groups.yahoo.com/group/development-axapta> " on the web.
>
> * To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]
subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo!
Terms of Service <http://docs.yahoo.com/info/terms/> .
>
> ________________________________
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> -----------------------------------------------------------------
-------------
> YAHOO! GROUPS LINKS
>
> a.. Visit your group "development-axapta" on the web.
>
> b.. To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> c.. Your use of Yahoo! Groups is subject to the Yahoo!
Terms of Service.
>
>
> -----------------------------------------------------------------
-------------
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>
> SPONSORED LINKS Computer part Programming languages Microsoft
axapta
> Support exchange
>
>
> -------------------------------------------------------------------
-----------
> YAHOO! GROUPS LINKS
>
> a.. Visit your group "development-axapta" on the web.
>
> b.. To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms
of Service.
>
>
> -------------------------------------------------------------------
-----------
>
>
>
> [Non-text portions of this message have been removed]
>
SPONSORED LINKS
| Computer part | Programming languages | Microsoft axapta |
| Support exchange |
YAHOO! GROUPS LINKS
- Visit your group "development-axapta" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
