Timeout (script is taking to long)

Paul Witmond

Hello,
We have build a script to adjust the price of the Bill of Materials.
Based on the results of level 1 we have to perform calculations on level 2.
And then we go to level 3 , 4,5,6,7
On every level we have more items
The script is fine but it's only working until level 3.
In level 4 the script is running for several minutes and finally we have a timeout crash.
Who has a solution ?
Extending the timeout count would solve the problem but how can we do this
Please help
Regards,
Paul

Anders Olsson

Hi Paul,

Can you please provide some details as to exactly what is taking a long time? DB queries/DI API fetches/inserts/updates?
How many transactions in total and how long time have you measured each to take? Can you post your code?

Regards,
Anders Olsson

Paul Witmond

Hi Anders,

I don't know what is causing the timeout.
The error is something about the groups are busy ??

please see the attached code

Thanks,

Paul

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-file-uploads/paul-witmond/nap2.cocu]nap2.cocu[/url]

Paolo Manfrin

Hi Paul,
if you think your problem is a timeout you could use a structure like the following, setting the property CommandTimeout. (It's in C# but changing it to VB should be straightforward).

SqlCommand cmd = new SqlCommand();
cmd.CommandText = 0; @"SELECT T0.DocEntry FROM ODRF T0"; // your query here
cmd.CommandTimeout = // set your desidered timeout in seconds here (ps. CommandTimeout=0 means infinite...)
using (SqlDataReader sdr = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery(cmd))
{
while (sdr.Read())
{
string DocEntry = sdr.GetString(0);
// TO DO....
}
}

I'm not sure about which version of SwissAddonFramework you're using. Latest one has a timeout set to 10 minutes while in the past it was 30 seconds by default.
Hope that would help.
Kind Regards,
paolo

Anders Olsson

Hi Paul,

Some thoughts about optimising for performance:

1. The number of times you "touch" the database can be optimised. You can for example build a single query to pull out the hierarchy of items at the beginning of the rule and place the data in a DataTable or some sort of dictionary. You would then access these objects from RAM rather than querying the database in each loop. So instead of querying the db hundreds of times, you query it once.

2. After implementing the above, any performance problems should be related to either fetching or updating DI objects. To find out the culprit(s), you can profile your code by printing debug statements with the current time/or calculated time spans before/after a DI operation.

3. If your product tree tables are very large it could be worthwhile to look into fragmentation of indexes on the relevant tables and rebuild/defrag indexes/rebuild stats if necessary.

Regards,
Anders Olsson