Dynamic Drop-Down Control

Hi Everyone, Usually Acumatica Drop-Down controls have fixed lists of values. Of course you can use PXStringListAttribute.SetList<…>(…) method do change list of values dynamically, but how to make it more handy. The way I recommend you is to create an attribute that can encapsulate all the field related logic, so to use it you can just put the attribute on the proper place. So the plan: Create an attribute that will be inherited from PXStringList and provide the values Create IPrefetchable class that can be automatically load all possible list from DB and cache it. In the same time Acumatica will auto-reset when the linked table is changed. This is… Read more

Optimizing Large Import

Hi All Want to share with you some good approaches to Import a large amount of data. If you ever tried to import some large set of data from Excel you actually know that it is not as fast as importing data into the SQL table. There are plenty of reasons why it is like this: Data validation Defaulting empty field Running of business logic Data integrity Updating of referenced data Security tracking Audit Of course all these rules have different effects in different modules. It is obvious that GL is much faster than SO Orders. At the same time, AR is faster than SO, but slower than GL. Also… Read more

Disable Discounts Calculation for API Calls

Hi All, When you creating a multiple AR/SO documents with multiple lines through the API, you may have a nice trick to little-bit optimize system performance. By default Acumatica business logic is optimized for entering data from UI, so all variables as taxes and discounts must be recalculated on each document line. But during bulk load you actually can do it just once – before document save. Here you have extension that will disable automatic discounts calculation if you loading data from API or Integration services. public class SOOrderEntryExtension : PXGraphExtension<SOOrderEntry> {        [PXOverride]        public virtual void RecalculateDiscounts(PXCache sender, SOLine line, Action<PXCache, SOLine> del)        {               if (!Base.IsImport)… Read more

Restricting the list of fields selected from database

Hi Everyone Sometimes you need better control on how Acumatica selects data from database. And here Acumatica Framework provides you some ways to optimize data query. Using PXFieldScope you can specify what fields will be selected form database. This can save some traffic and processors time in large and extra-large statements. Also it can improve performance in case you have plenty of data, but use very few fields from the selected tables. And of course excluding of some complex sub-selects may significantly improve data selecting on SQL servers side. No RowSelecting events (e.g. from attributes) will be fired for the fields not fed into the FXFieldScope constructor. Note, that for… Read more

AP Bills Import Performance Measurement

Hi All, Want to share with you some good news. Acumatica constantly improve quality and functionality, but we also spend a lot of time on performance optimization. And we 5.3 update 3 we get to new height. 5.3 version gets 25% faster in importing of Accounts Payble bills and now Acumatica can import, validate and save 2 Bills per second per CPU Core. So with adding more cores this chart will grow nearly linearly. But here we have one limitation. Due to performance optimizations, functionality for automatically loading payment documents to bill was disabled. As a side effect this will make Auto-Apply button non-functional when importing bills. Have a nice… Read more

Recommendations for Application Server Memory

Hi All, Some times ago we had these recommendations for hardware resources: Small – 2 Cores, 4 GB Ram Medium – 4 Cores, 8 GB Ram Large – 8 Cores, 16 GB Ram These numbers was mapped to Amazon EC2, C3 Instance type. But after latest performance investigation we have found that 4 GB is too small for normal run of Acumatica. 8 gigabytes should be minimum requirements for any Acumatica deployment. So I recommend you increase server memory if you have less than 8 GB. This is important especially for small instances and Amazon deployments. New recommendations are: Small – 2 Cores, 8 GB Ram Medium – 4 Cores,… Read more

Database Data Saving Test

Hi All, Some time ago, one client asked me: “How fast can you upload data into the database”. Unfortunately I had no answer for this question and actually no one has answer for this question. The problem here is just in the question itself – it has not enough information: What data (documents) do you want to upload? How many documents do you have? How many lines per document do you have? How many field per single line do you have? How many Business Accounts do you have in the system? How many Documents per Business Account? Do you have Field-Level Audit? Do you need to release documents right after… Read more

Development Tools in Acumatica

Hi Everyone, Today I want to speak about tools and application that Acumatica team uses during development. Development Microsoft Visual Studio 2012+ ReSharper from JetBrains for faster development Databases and SQL Microsoft Sql Server Express + Microsoft Sql Server Management Studio Microsoft Sql Server Profiler My Sql + SQLyog MySQL GUI for MySQL Apex SQL Refactor – SQL Formating Tool Performance DotTrace from JetBrains for performance testing Memory .Net Memory Profil for memory analysing Windbg for deep process dump analysing Sources Git + Stash for sours control and analysing SourseTree + GitExtension as GUI tools for Git. Documentation and Tracking Jira for bugs tracking Confluence for specification, features and documents traking Mockups and Flow WireFrameSketcher… Read more

Analyze Running Queries on Database

Hi Everyone. If you face database performance issues, you might need some way to get running queries from there. SQL profiler is good, but some times you do not have it or you don’t have access to it. But if you can run SQL queries, you can use this script. It will show you immediate snapshot of all running queries. Script: View the code on Gist. If you are searching for statistical information you can use that query. This one will show you to 30 heaviest queries on the sever. Script: View the code on Gist. Have a nice development!

Analyse Size of Acumatica Database

Hi Everyone, Sometimes, when you have big Acumatica database, you might want to know how much space does it taken by what table. This script can help you: View the code on Gist. The result of this script on my demo database is here: You can see that I have highlighted some tables that contains a lot of data. Actually some of them does not contains important information. Let me point you on some tables that you can archive and clear if you aware about space. This can be helpful if you use cloud hosting and the size of database is not cheep. Logs Tables LoginTrace – all login, logout, screen opening, session expired,… Read more

Cache Data in Memory using Database Slots

Sometimes you need to use some dynamic data from the database, but you need it so often, so retrieving data process becomes a big performance issue. What you need to do? Cache the data? But your date is dynamic and can be modified by the user. So you need to implement some mechanism to reset your cache? Another bicycle… Acumatica Framework can address this issue with Database Slots Technology: Framework stores your data in the HttpContext in special dictionaries. Framework automatically calls Prefetch method on first access to cached data. Framework automatically monitors dependant tables and will reset cache when someone has updated it. Framework automatically handles cluster mode. Access… Read more