Performance Impacting Mistake No 3: Formatted Search Proliferation

The third most common issue I have encountered when diagnosing performance issues relates to a very powerful feature of SAP Business One and that is the ability to use formatted searches in Business One to performance data manipulations and calculations based on data values retrieved via SQL Server or displayed on an SAP Business One screen.

Often times, when we first find a feature such as formatted searches that is simple, powerful and gets us out of a bind from time to time when trying to deliver on a customer or users expectations of functionality, we tend to fall in love with that feature and start to use it all the time.

This, unfortunately, leads to performance issues as each formatted search requires and takes up its own system overhead, particularly as calculations become more complex and data values to be retrieved become more numerous.

Don’t let too many formatted searches make your system run like a dog

The trick is to remember that you have more tools at your disposal and you need to know when to use the right tool to solve the right problem.

Of course you then need to have knowledge of those other tools that you have at your disposal and know how to use them – then when to use them answer then becomes much easier and with experience gets even easier.

There’s an old saying that “when all you have is a hammer then everything looks like a nail” and this really applies in the case of formatted searches.

Each formatted search establishes a connection to the database , executes its data retrieval and then closes the database connection….in and of itself , not an overly performance crushing activity.

But when you end up with a sales order entry screen that is populated full of user defined fields(UDF’s) and multiple formatted searches that use the values from those UDF’s or worse, scan back through multiple lines on the sales order to receive values and perform calculations the suddenly you end up with a solution that whilst doing the job, runs like a dog and worse causes your users to start complaining about system performance.

So whats the answer?

Like I said in my previous post about system design, think carefully about what you are trying to achieve – would you be better off with a piece of SDK code, maybe a report could provide the information the formatted searches are retrieving or aven auser query or maybe a query using an external tool such as Excel or even Microsoft Access.

In my past life as an ERP implementor for small businesses, I would install a copy of Microsoft Access on every customers site and build them an Executive Information system that allowed them to run all sorts of data queries of the SQL back end wihout impacting on the transactional systems. Of course, we have many more tools today that could do the job even better, but only you can decide which is the right one to use.

Save formatted searches for those quick and simple requests that you get and use the big guns like the SDK when things get complex – your users will thank you for it!