Often we find ourselves scratching our heads as programmers… especially when learning new products and technologies. We make assumptions and mistakes like everyone else but sometimes, just sometimes we get stuck – removing my tongue from my cheek. Personally, I get stuck more often than I care to admit, frankly.
My goal in this post is to help you avoid some of the issues that have seen over the past several months that are common mistakes developers are making. I’ll be focusing on some basic things to keep in mind when you set out to take advantage of our Platform in exchanging data between Acumatica and your applications using Web Services, whether using SOAP or our new REST APIs. I will also discuss basic performance considerations and testing advice.
Before we jump into exploring this week’s topic, let me give you a bit of an introduction to our web services integration.
Brief Web Services Overview
Acumatica Cloud ERP was architected for the Cloud and hence the world-wide-web. It is very easy for a developer to exchange data using web services on our platform. If you are unfamiliar with our integration using web services continue reading – otherwise move on to the next section.
The mapping between our screens/forms and web services endpoints is natural and easy to use. Nearly every single form is mapped for you with the Web Description Language (WSDL) accessible. Let me show you an example. Below in Figure 1, you will notice that the Bills and Adjustments screen under FINANCE > Accounts Payable. To view the WSDL, you select Help > Web Services.
After selecting Web Services from the drop-down box, a new webpage is opened and you will see the following in Figure 2.
A listing of web services operations is listed and circled in red above is a link to the WSDL. Clicking on this link shows you the WSDL for the AP.30.10.00 form which can be referenced by the programmer for data exchange with their application as can be seen below in Figure 3.
In Visual Studeo, you can create a web reference for your application by simply right-clicking on the App_WebReferences folder [1.] in the Solution Explorer of your Web Application and coping the URL from the web page that is opened displaying the WSDL above in a likewise manner [2.] in Figure 4 below.
For more detail, please refer to an excellent post done by Douglas Johnson here. Doug guides you through connecting a local website to show web services in action.
Common Issues and Solutions
A common root issue is the failure to realize that updates ultimately go through our integration services, which will update the values and save records as if they were done by a user. This bears repeating… but in a slightly different manner. We have modeled our integration to match user activities. If you get stuck on something, try doing the same thing manually – as the user would in the application and see if you get the same error. Developers often submit a case with an error message which unfortunately doesn’t help them identify the root cause.
Here are some recent issues we have seen over the past few months:
A developer was trying to update an existing payment and change the reference number in the process by accident. An exception was thrown on Save, which reported that the customer location was missing. This happens because the reference number is a key field. Changing it from the user interface causes the system to add a new record. Saving then fails because other required information is missing.
Another example is when developers are trying to add an inactive item to a Sales Order and saves it. The system complains that ‘UOM‘ field may not be empty or null. This happens in the same way as it does when a user does this within the application, manually. It should be noted that the application U/I also shows other visual cues that this item is inactive, but the only dialog box you get is on save.
With respect to Web Services Endpoints, we have seen attemps to load a Sales Order with multiple lines from their custom web services endpoint and were getting an exception thrown. The problem was created because they had the branch information at the Sales Order Entity level. The document detail for this order was returning two detail objects. The solution was simple enough – but not necessarily intuitive. By moving the Branch Field from the Sales Order Entity to the Detail Entity, made it so that “only one entity satisfied the condition” rather than two which is what caused the exception error.
To simplify things and show an analogous example of the above scenario, consider the following:
Having a detail field at the top-level entity forces the system to create a different top-level entity for every detail an item has.
As an example, let’s say you have a Sales Order with ID SO0001 that has a Branch Red detail with a price of 15 dollars and a Branch Blue detail with a price of 10 dollars. If you try to retrieve the Sales Order SO0001, both details – Red & Blue Branches cannot be included in the same list of the top-level Sales Order because they do not have an identical top-level item for the Sales Order.
One of the top-level items will be:
ID = SO0001 Color = Red Branch
While the other will be:
Id = SO0001 Color = Blue Branch
You cannot put the Blue Branch detail in the list of the Sales Order with the Red Branch since it does not have the same Branch. Thus, to correct this problem, the system creates another entity and you are returned two Sales Orders instead of one.
Another dimension to consider when working with web services is performance. Some basic advice in any performance discussion is limiting your data working sets to only what you need and do the appropriate load testing. When I worked with ISV partners in a previous role, one of the most valuable services we offered for our partners was performance testing in our labs, where they could bring in their applications and do load testing on some serious hardware that approximated real-world workloads and have dedicated performance engineers work with their engineers/developers on tuning their applications. Although, I can’t claim to be an expert in perf testing myself, I sure learned a lot through osmosis. Time and time again, I watched nearly every ISV over the years make the same mistakes. The number one culprit to their performance woes and subsequent gains upon removal… duplicate indexes. Number one by far. Cleaning up their queries – doing complex crazy joins in the database that were not necessary. And finally – not working with a limited working data set. If you need all that data, fetch it in chunks. All basic stuff that lie hidden until you do load testing.
Load testing is fundamental to performance tuning – surfacing bugs and exposing less than optimal design.
Sure enough and unsurprisingly, we see these same mistakes surface with some of our partner developers. Thusly, if I were to give one piece of advice, it would be to do some load testing with a large set of data – real customer data loads before deploying in a production environment and if you can do testing on similar hardware configurations, all the better.
Also, there are some simple things you can check in terms of correctly configuring SQL Server databases. Several configuration settings in SQL Server may not be configured properly, leading to the performance slowdown.
Check for memory usage restrictions:
Open SQL Server Management Studio and right-click the database server instance and select Properties. In the Server Propertiesdialog box, click Memory. Set the value in the maximum server memory box to 75% to 80% of the total physical memory of the server, leaving at least 4 GB available for the operating system. For example, if your server has a total memory of 16 GB, you can set the maximum server memory to 12 GB (which is 75 percent but leaves 4 GB for the operating system).
Schedule the weekly maintenance plan by using the Maintenance Wizard in SQL Server Management Studio, scheduling the following tasks:
- Database Integrity Check: Checks the logical and physical integrity of the database
- Rebuild Index: this helps remove gaps in data pages and eases the data retrieval process
- Shrink Database: Removes space by moving pages from the end of the file to the front, and then deallocates the excess space back to the file system
- Update Statistics: Internally updates statistical information about tables and indexes used by SQL Query Optimizer during data retrieval requests
You can find more information on creating maintenance plans in https://msdn.microsoft.com/en-us/library/ms189953.aspx.
Find the top resource-consuming SQL queries
Run the following report in SQL Server Management Studio:
- In the Object Explorer of Microsoft SQL Server Management Studio, right-click the server instance, and select Reports > Standard Reports > Performance – Top Queries by Average CPU Time. SQL Server Management Studio opens the report.
- Right-click the report, select Export, and select the format of the exported report.
or manually, you can run the following query to find which process is taking the longest and which process is being executed the most:
SELECT top 100 , total_worker_time AS TotalCPU , total_elapsed_time/execution_count AS AvgDuration , total_elapsed_time AS TotalDuration , (total_logical_reads+total_physical_reads)/execution_count AS AvgReads , (total_logical_reads+total_physical_reads) AS TotalReads , execution_count , SUBSTRING (st.TEXT, (qs.statement_start_offset/2)+1, ( (CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+ 1 )AS txt , convert(nvarchar(max),query_plan) , CONVERT(int, depa.value) FROM [master].[sys].[dm_exec_query_stats] AS qs WITH (NOLOCK) cross apply [master].[sys].[dm_exec_sql_text](qs.sql_handle) AS st cross apply [master].[sys].[dm_exec_query_plan] (qs.plan_handle) AS qp cross apply [master].[sys].[dm_exec_plan_attributes](qs.plan_handle) depa WHERE (depa.attribute = 'dbid') ORDER BY 1 DESC;
Check the SQL Activity Monitor for Locks:
- In the Object Explorer of SQL Server Management Studio, right-click the instance name, and then select Activity Monitor.
- Review the Recent Expensive Queries section.
If you are not sure what to do with this information, we can help you. Just open a support case with Acumatica.
Web Services allow developers to exchange data between applications – with our Platform, we have a wide spectrum of integration opportunities for partners to explore – from simple data exchange between applications using web services to deep integration using our integration engine and framework and a mix in between. I’ll be discussing this spectrum of integrating with our Platform in future blog posts and video demonstrations.
The discussion here, however was to touch upon some of the common issues you may run into when working with web services that we have seen ourselves – and to provide some proactive advice to circumvent these issues, if possible. Documentation is never enough and frankly not everyone reads the documentation until after they have issues, if at all. I hope you have found this information helpful. Report back to us in the comments or send me an email – I would love to hear from you.
Also, if you have any topics you would like me to cover in the future, please let me know.
Please consider these recently related posts: