Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Performance & Tuning (http://www.baanboard.com/baanboard/forumdisplay.php?f=61)
-   -   Slow sessions (http://www.baanboard.com/baanboard/showthread.php?t=25624)

Carles 14th November 2005 17:52

Slow sessions
 
Hello,

Since April of this year, some sessions go more and more slowly everyday.

Due to this problem, we had carried several tests, in both hardware and software, without success:

1.First thing we did, was to the check integrity of DB. No problem was found.
2.Then, we re-installed Baan and DB in different computers. So now, we are running Baan ERP 5.0C service pack 10 in one machine with Windows 2003 OS and MS-SQL 2000 – LEVEL 2 in other machine with Windows 2000 OS.
3.We audit our internal network and no problem was found.
4.We traced some sessions that go slowly. For example, we traced tdsls4201s000 (copy sales orders lines) when we copied only 10 lines and we found a lot of SQL sentences (102968 selects of table whinr110 or 91279 selects of table tdsls401, between others).

In our opinion, the only conclusive test was the trace, because it seems that something is done wrong in the program.

Besides if we erase all entries in table whinr110, the time to copy 10 lines in the mentioned session drops from 2 minutes 30 seconds to 15 seconds only.

Is there anyone who had similar problems before? Is so, what was the solution?

Please, do not suggest to run the delete sales order history session because it is not working well with service pack 10 and due to the personalizations it is difficult to update to service pack 15.

Thank you.

Carles

lbencic 14th November 2005 18:09

There can be lots of causes.

One thing we found is putting field level authorizations on user roles slows things down dramatically. It creates lots of sql calls as you mentioned seeing when you trace, making it look like a coding problem, index problems, etc. One easy way to check if this is your problem is to run as a superuser and see if the slow down still occurs.

Carles 15th November 2005 13:15

Lisa,

Thank you for your information.

Unfortunately, all users are defined as superusers. So, this is not the problem.

Regards,

Carles

Dikkie Dik 16th November 2005 02:14

If you read chapter 2 of the Performance Tracing and Tuning guide you see the Call Graph Profiler. This shows the times of all your queries and other activities. This can help you to identify to find your problem.

It helped many users, so I hope it will help you as well.

Oh bye the way, if you find the problem, feel free to post it here so we can help you to look for a solution.

Good luck,
Dick

dave_23 16th November 2005 02:54

Quote:

Originally Posted by Carles
Hello,

Since April of this year, some sessions go more and more slowly everyday.

In our opinion, the only conclusive test was the trace, because it seems that something is done wrong in the program.

Don't those two statements seem to contradict eachother?

I don't think that software degrades with time. =)

You need to look further into the trace, you might find your answer there, but you can't really tell if there is a problem with the session or is it just an indication that, perhaps, you need to close or archive some wherehouse orders...

Think about new Company level changes that happened in april, did you start shipping a new product line? did you take on some new business? etc.

Good luck!

Dave

Dikkie Dik 16th November 2005 03:13

Quote:

Originally Posted by dave_23
I don't think that software degrades with time. =)

Sure it is! You need the latest hardware to support the latest software as the latest software is just the previous version with an additional layer :).

Dick

Carles 16th November 2005 16:35

Dick,

The same day I posted my question, I found the Performance Tracing and Tuning guide, so now, further to the initial MSQLPROF trace I mentioned in my post, I have the profiling of the same session.

The information I have is in essential the same. This means, that I do not yet what’s the problem.

Do you have any idea who can help me to analyze all the trace information?

Thank you

Carles.

Carles 16th November 2005 17:27

Quote:

Originally Posted by Carles
Since April of this year, some sessions go more and more slowly everyday..
In our opinion, the only conclusive test was the trace, because it seems that something is done wrong in the program.

Quote:

Originally Posted by dave_23
Don't those two statements seem to contradict eachother?

I don't think that software degrades with time. =)

Dave

I don't think these points are opposed.

If the problem is a bad select in the program, when you have small tables the program could work. As soon as table grows, the program will go more slowly.

But, please, don’t think I assure that the program is not well done. It was my first idea after making all tests and it is an opinion of non Baan expert.

I posted my problem to have opinions of Baan gurus, because maybe the problem could be very different from what I thought.

In any case, thank you for your reflection.

Regards

Carles

dave_23 16th November 2005 18:48

I was just more or less trying to point out that you had further to go.
Don't just rest on it bug in the program..

I think you're on the right track with the Performance tuning guide.

Dave

Dikkie Dik 16th November 2005 19:00

Carles,

Please do an Call Graph Profile as it will give you much more info on only 1 HTML page. Drop it here so we all can figure out what goes bad.

Dick

Carles 18th November 2005 11:21

1 Attachment(s)
Dick,

Attached you can find the CGP of session tdsls4201s000 ( copy order lines )

This file was been generated after copying 10 lines and the time it took to do that was 3 minutes.

I hope someone can find out what happen.

By the way, is it normal that profile generates 85 files of session tdcms0141m000? ( Other times I use the profile always generates a lot of this session files )

Regards

Carles

Dikkie Dik 18th November 2005 17:54

Problem area
 
This helps. Your problem area is function 56 in dll whinpdll0001. I don't know the name yet, but if you compile this dll in debug mode you can find out or just search for one of the queries that are in this function.

Code:

    63300    sql.fetch:owhinpdll0001
        10    sql.exec:owhinpdll0001
        1    sql.parse:owhinpdll0001        [763]    query id 332

        select        whinr110.*                                       
        from        whinr110
        where        whinr110._index1 = {:i.item}
        and        whinr110._compnr = :i.site
        and        whinr110.chan inrange :chan.f and :chan.t
        and        whinr110.trdt >= :i.dtef
        and        whinr110.trdt <= :i.dtet
        and        whinr110.cprj inrange :i.prjf and :i.prjt
        order by whinr110._index1

This function is called 10 times, but it explodes in calling other functions. upto more than 55.000 times. So, I am afraid that there is something going wrong in this area. Why is it calling other functions upto so many times and what is it doing? I assume that query 332 is the reason for this, but why does it give so much data? Is this standard code or customized?

So if you can explain why this explosion of data is there, you can explain why your session is taking longer over time. If you are not sure, you can probably post the code here.

If you can't do anything about this session, you can try to do something about the queries. Not improving them, but executing them less by checking if they look for the same results as before.

Hope this helps,
Dick

Carles 21st November 2005 13:31

Thank you Dick,

We have checked the dll you told us and it seems it is the cause of slowing sessions.

We will contact Baan to know if there is solution to this problem.

As soon as we finally fix the problem, we will post the solution.

Regards

Carles :)

Carles 24th November 2005 19:23

For your information, we finally solved the problem of slow sessions in order lines.

The big amount of selects was due to a parameter in EP. We unmarked On line ATP update in EP and now sessions work correctly.

Thank you.

Regards

Carles

Dikkie Dik 12th October 2013 17:52

This topic was specific on tdsls4201s000.
But indeed many sessions could have issues and many solutions have been made. As this answer was from 2005, I recommend to start a new topic if you have sessions that are not performing well.
And of course, read solution 22881401 as it has lots of ideas on how performance can be measured, traced and improved.


All times are GMT +2. The time now is 22:39.


vB.Sponsors
©2001-2017 - Baanboard.com - Baanforums.com