Sage 100

Does anyone know what the SY_SQLOptimize.M4t file is and what it does? Can it be modified to improve performance on certain areas in a premium install?

Yes, yes, and not really. Will essplain in a little bit.

It's used for all the canned worktable reports. Each report has a record for the Driving table and each secondary table. In the .rpt you see 1 single worktable but it's a composition of many tables as you know. The field called OptimizationType stores a pvx Cache Level for every secondary table to ""optimize"" the way the data is selected from the SQL table. You can DFDM change that field to make it go faster in some cases but it could result in issues. Ex: In 4.45 the AR Aged Invoice Report was not selecting all customer invoices but it was a fast report. Sage fixed it in 4.50 by changing the OptimizationType value (for TableName= AR_TransactionPaymentHistory). However this slowed down the report considerably and resulted in slow performance calls to Support.

In short the SY_SQLOptimize is an interesting scheme to avoid stored procedures like there were in 3.x SQL (my opinion) but once your MAS SQL data reaches a certain large size, no matter how you select it via pvx and despite the normalization (unlike 3.x) it gets slow. Most customer data I've seen isn't that size but I've seen the slowness you're implying in many cases.

Issues in this case in particular is relating only to a MAPADOC process. The process in pvx took 4 minutes and in sql took 19. I've done everything I can do at this point for their server/sql configuration. Doubled RAM on the box, set max memory in SQL, increased the size of the temp DB, set maintenance routines. While SWK says that some processes in MAS are slower in SQL I still feel that this is extremely slower and was now grasping. I know that the issue now only remains with processing in MAPADOC but I feel that this is merely just where its aparent at this point (as MAPADOC is the most resource intesive task they have there). Ultimately my gut leads me to the fact that this machine is one of 9 VMs on this server and while the VM states that there is 16 GB of RAM... I don't know that this is truly the case NOR do I know if the VM is configured properly. Was just kind of hoping that I could tweak something to speed it all up. There isn't a magic switch somewhere in Sage 100 Premium that I can flip from turtle to bunny rabbit?

Asking IT to screenshot the vSphere console is a good way to know for sure how many processors and RAM is allocated to the VM. If you can get access to the host o/s yourself then start vSphere yourself. Side Note: If MAS App Server and SQL Server are on same VM, you don't want to leave SQL memory defaulted at max because it doesn't share well with other apps. 60-65% of total memory is what I use myself. Also to make sure your maintenance plans are running well particularly in area of rebuilding or reorganizing indexes, go here to validate: http://goo.gl/9hRP89 - Copy the T-SQL at the bottom of the page and run it in SQL Mgmt Studio on MAS_XXX and MAS_SYSTEM. Check for EC and SO_254 tables in particular. Gut feel tells me if rest of MAS is okay then next question could be what process in Mapadoc are we talking about - Receive Inbound EDI, ASN Selection, ASN Creation, one of those or is it one of the reports

The process where we are seeing the lag in particular are with the Receive Inbound EDI and Validate SO Creation. When running tests we saw that the file took the following time - Validate TIme: 48sec/ pvx - 7 min/SQL Creation Time: 3 min/ pvx - 12 min /sql Marthe ran a test on their system with a similar file size - Validate Time: 32sec/pvx - 5 min/sql Creation Time: 1 min/pvx - 9 min/sql I will see if I can get info on the vSphere configuration. Thanks!

Having watched the Mapadoc presentation yesterday and listening about the quality of their support team, I have to ask about their response to the issue.

They have been very responsive and have stated that MAPADOC is just slower in SQL but what I gather from them is that it is more of an issue with how MAS writes the data to the SQL tables. I get that.

Attached is what their IT department sent me in regards to their VM configuration. I asked if they have reviewed VMWare's ""SQL Server on VMWare Best Practices"" and the response to me was ""Are there specific things that you are looking for?

Real quickly which of those guest o/s's is the MAS server and which one is the SQL Server, or if they're both on the same VM which one of those (e.g. APP01 ?)

They are both on the LIMAGE-APP1

So this means there are 5 processors for CPU allocation (32% of 16 total processors). For the RAM if that 3rd screenshot is for LIMAGE-APP1 then it does have 16GB allocated. These are fair - good starting points for a combined system. I don't know the current size of the MAS data and growth projection a year from now and what other apps are running on the VM, and how fast or slow the disks on the RAID array are, and what RAID level is in play, but it could require more depending on those factors. From what you've said, I usually start with 6 - 8 processors myself and click the Reservation column to guarantee that much is always available. But having said that and you having said that MAS itself is not running slow otherwise and that you have SQL DB maintenance plans in place, my gut feel tells me it may be Mapadoc related.

*** UPDATE *** turns out my initial concerns regarding the SY_SQLOptimize.M4T file were correct. Turns out, one day shortly after their go-live someone from their company contacted another person in our company regarding an unrelated issue. There were duplications of accounts on the financial reports. Support person checked, there was a KB article advising to install a fresh copy of the SY_SQLOptimize file. But what did that do? It wiped out the records that MAPADOC writes to this file. This was of course thanks to SWK's continued support and involvement that we finally determined the issue. Order validation and creation is still twice as slow as it was in providex but prior to having the correct file on their system it was about 4 times as slow. Overall in providex a file would take 4 minutes, in SQL w/o Optimize file 19 minutes now with correct SY_SQLOptimize file we are down to 9 minutes.