Maverick,
Seeing as you were unable to provide any of the information I requested, he is what I would suggest.
1. Do not use RecId as the index for the table. Unless you were the sole person populating the table, to my knowledge, there is no guarantee that the RecId will be in the sequence you expect. It is easy enough to add a number sequence to a table to ensure the order you require.
2. Check the indexes, and make sure you have an index for your Find.
3. Do not update fields that make up part of the index (where possible). I have found this slows execution.
4. Make use of update_recordset where possible.
5. I have found code runs slower when Index or Index Hints are added to select statements. Remove where possible. If sequence order is important then
A) Use Index
B) Use Order By
C) Use primary index on the table definition. (if your case, this is likely to be the best option).
6. You seem to have unnecessary calls to .Clear(). Remove them.
7. Consider using joined select statements.
8. Minimise the amount of information transferred. Eg Modify the code
select firstonly STable3 index RecIdx
where STable3.RecId > STable1.RecId &&
Stable3.TokenName == ")";
To (I have left the index statement in on purpose for this example)
select firstonly RecId
from STable3 index RecIdx
where STable3.RecId > STable1.RecId &&
Stable3.TokenName == ")";
9. If update() has not been overloaded use update() instead of doUpdate(). You never know what changes will be required in future. You can always add the Skip* series of methods if required.
10. Before doing an update() you should check that the record exists. Ie what happens when the code used in the example for point 8 returns no records? The check can be as simple as if (Stable3) { }. You have done this in a few places but not all.
11. Personally, I would modify the code to use switch statements were possible. I find it easier to check the logic of the code.
12. Try and do the update only once. If the situation occurs where only one update is occurring, why continue with the rest of the code in the loop.
13. When you post code for people to examine, also include the declaration / header statements. This way we know if Stable1, Stable2, Stable3, Stable4, UpdateSTable are actually defined as pointing to the same table structure or not. It was one of the pieces of information I did ask to be provided.
14. Indexes on the tables are important. How many do you have? How are they defined? Incorrectly indexing, or having to many indexes can adversely effect code performance, especially when doing inserts and updates. Indexes were one of the pieces of information I asked to be provided.
15. Modify your code to minimise the tts log that is generated. Is it your intension to lock the entire table for your own use while this process is being done? This can be done by creating a series of methods and calling them where needed. The methods can then have the necessary tts log. NOTE: before changing your tts set-up, ask yourself, if the code fails, do I need all the changes made to roll-back? If No, then modify your tts usage.
16. Reread the comments by Jacob Hjelmer. (Contained in the message below).
Hope this helps.
Maverick, in your original email you stated
"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."
And yet, the code you supplied does not do any insertion. Was this
1) An accident mistake.
2) An indication that not all the code concerned was posted?
Barry.
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Maverick
Sent: Monday, 9 January 2006 8:57 PM
To: [email protected]
Subject: Re: [development-axapta] Performance Issue
Dear Barry,
Code is updating the records on the basis of the sequence of the recid.....so i have made an index on recid.
Changecommments() is a method doing update to the table.
find() is a normal method, which you can find in Axapta.
I had not changed update() method of the table. It is because, I donot want it to perform any type of checks, since
doupdate() forcefully updates it.
Other things are confidential. I cannot tell what we are doing.
I had changed the code in moduler form. And it is comparitively better.
What else you can suggest?
Thanks in Advance.
Maverick
----- Original Message -----
From: Bayliss, Barry
To: [email protected]
Sent: Monday, January 09, 2006 4:18 AM
Subject: RE: [development-axapta] Performance Issue
Maverick,
Thanks for the first piece of code, now for the rest.
What are the declarations of Stable1, Stable2, Stable3, Stable4? (If non-standard, what is your table definition?)
What indexes are on tables used?
What is the code in the function this.changeComments()?
What is the code for the function TOI_SpecialTokens::find?
What is the code for the function TOI_PrimitiveDataTypes::find?
To try and speed up this piece of code:
Why are you using RecID as part of your select statement?
What are you trying to achieve in this piece of code?
Have you modified the update function on the table, ie requiring the use of doUpdate?
Barry.
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Maverick
Sent: Friday, 6 January 2006 6:16 PM
To: [email protected]
Subject: Re: [development-axapta] Performance Issue
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:[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:[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]>
* 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]
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]
Yahoo! Groups Links
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.
