Server Resources for Addins

I haven't heard from the tech side, but I have heard of Add-ins processes taking 1 second per record and people trying to run thousands of records at a time. The connection is very inefficient to begin with, so if users try to pull a lot of data or join a number of tables it is going to be a hog.

Does your company have any other query tools that the users could use? Even Access could be less resource intensive.

The Add-Ins will most likely *prevent* a bad join, since they understand Lawson's database relationships.
Access and/or other SQL tools, in the wrong or untrained hands, can quickly bring a server to its knees...

We have a "Data Warehouse" that gets refreshed nightly with most of the tables and users must have a Crystal License to get anything. Crystal is a bit more difficult to use than addins. And the joins are not automatic, so is causing some issues.

We are trying to direct most users there for general querying of historical and querying of data, but some users feel the need for Real Time, up to the second data-PR, HR, and GL mostly. AP and MM seem to be OK with day old data.

We don't allow Access ODBC connections against the PROD database.

John,

While addins won't allow a "Bad" join, multiple tables and field with a large criteria range evidently can return more data than the Java Virtual memory segment can handle. Evidently no "Select" statement occurs until after ALL the records are returned to the Java memory segment. Lawson has no recomendations other than standard configuration for the Java memory, and we never have any issues until MSAddins are used.

 

I love Addins and don't won't the users to end up not being able to use it as they feel they need to, so looking for anyone who has experienced similar statements from the server team and what they did to work through it. I am in AppSupport, so kind of in the middle of the users and the server team.

We are currently watching every addins query that gets submitted to see what it's doing. Very time consuming approach, but without any knowledge to start with, it's the best we could think of to start our analysis.

Thanks

Thanks for clarifying the issues; I have seen these types of issues before with the Addins.  In particular, things like querying against APINVOICE with a date range, which results in a table scan, etc.

There is a timeout on the query wizard which should provide some "feedback" that the query is too complicated/slow and the user really should look at a different approach.   I have found that some user education on things like indexes and conditions, etc. with regard to the Lawson table structures does solve some of these types of issues, as does some prototyping by internal App Support groups (like yours), and in fact is one of the reasons I've added an "Add-In Library" (https://www.lawsonguru.co...icrosoftAddIns.aspx) on LawsonGuru.com. 

BTW, are your users using table or application queries? 

Yes, that APINVOICE is a good example, and when you throw in the mainvdtl and a po related table on top of it, it is a bear.

The default timeout is 60 seconds, from using the tool, this appears to be pinging the server along the datastream line and when there is either no additional data coming back, or the server quits accepting calls for the query, it begins it's counting? Very simplistic explanation I'm sure, but does that sound about right for that parameter.
Is your experience to leave this at 60 seconds or to change it? I have tried increasing to 120 and down to 30 and have seen no different response, so that's where I get my assumption that it just begins measuring when there is inactivity between the client and server.
Yes, our users do need some additional education, but it is hard to come by cost effective training on the product as the usages vary so much.
I will take a look at those addins in your library.

Most users use table queries so they can get all the fields in 1 query.
May end up just IS being a roadblock to adhoc querying, and all queries must be tested and saved on a shared drive prior to use.

Are you using the latest version (MOA_09000301P19010)--it had some enhancements to deal with large result sets...

We are on 9.0.3.1380... I don't see that version available to us on the Downloads page??

After some investigating and case with Lawson Support, here is what we have found out:

Looking at the sql calls from the same query that has multiple tables, and only changing the Total Primary Records to Return value, the tool processes 2 different ways.

If the Records to Return value is less than 1000, MSAddins places the "join" within the sql statement. All the "processing" is done within the Database.

If the Records to Return value is greater than 1000, MSAddins executes multiple sql statements, placing the returned records in the JVM memory and does the "join" there. Using the JVM Memory segment to buffer the records until all records are returned, then does the processing and the join, sending the records to the user.

The above happens ONLY when the One To Many Related records to return value is 1. If the OTM value is greater than 1, this MSAddins version processes in blocks of 1000 with the "join" in the sql statement and uses the JVM to hold the records only. Again, all processing is done within the database. I think that is the "enhancement" that you are referring to.

Since we are experiencing issues with the amount of JVM memory being used, we are pretty confident that this is the culprit. We have dual JVM 1gb segments and on PR weeks, MSAddins is almost bringing us to our knees due to this.

Lawson support recomends reverting back to v 2.0.5 as a possible solution, but from what I have seen from others, this is not a very good solution. There are some issues with LSF 9 and that version, don't know what they are, but any mention of LSF9 and Addins and the immediate response is you HAVE to be on 9.0.3.1380 if you want it to work right.

Thanks to anyone who may have suggestions or comments.

I suppose I am now looking for ways to control what a user does with Addins. Energy is so high around here about it, it may be taken from all but the most experienced user. I don't want that as I love the tool.

Have a GREAT weekend everyone!

Thanks, that's very informative!

Would it be possible for you to setup some views or standard queries in both your DW and off of Lawson PROD tables, then have the users hit these? That would clear up the users doing joins in Crystal for the DW crowd.

For the Addins queries, are people generally pulling the same fields over and over but with different limits? Maybe some advanced training for the users on what types of queries run better would be helpful. I gave a training like this to many members of a company I used to work at, and the users found it very helpful.

Glad you found it useful as well. My head hurt a little yesterday after all of that, but I definitely feel I have a better understanding of the tool!

 

k-rock...

You do bring up a good point, we might be able to design something that hits the prod tables but prevents the users from modifying in either Access or Crystal. Simply an "On Demand" type query instead of report. They basically just want the data in excel so they can sort, filter, manipulate etc to look for erroneous data after importing time records and during the payroll run. They basically run the same queries all the time, with minor changes in variables, those can be programmed into Crystal I know, and from what I remember about Access, you can set a condition to ask for a value on a field when you go to run a query, so...

Although ODBC connections to PROD are not allowed here, they might make an exception in this case if we could ensure them that nothing can be modified.

It will be a battle of the Server Folks vs the Database Folks... Round 1 is in favor of the Server as currently "System Down" is more of an issue than any possible issues with a locked down ODBC!!! Will keep you aprised as the struggle continues!

 

 

To It's just me,
Have your issues come to a successful resolution yet? I see your last post was almost 2 years ago but I am experiencing these problems right now and would appreciate any information or advice you may have. Thank you.