Removing SYSPRO Business Object Dependencies

Summary

Recently I was assigned the task of removing all dependencies on SYSPRO business objects inside an Asp.Net 2.0 application. These are the steps to find underlying SQL calls made by the business objects. 

 

SYSPRO Business Objects

SYSPRO Business objects are contained in the Interop.Encore.dll. The returned result is XML for the calls I dealt with.

 

The data items were, eventually, pulled out of the XML and used as single data points or as lists. You may choose to return XML from the new SQL calls you write.  This allows you to plug in the results and leave the rest of the legacy code.

 

Since the purpose of my task was to find any and all speed improvements, I removed the XML and dealt directly with SQL results. This required more data layer build-up but the benefit, beyond speed, was that I learned exactly what data was required and how it was ultimately used since I had to reverse engineer all the way to the level of usage. 

 

 

The most interesting part of this reverse engineering was that there was so much unnecessary data retrieval that even the barest of changes would have made a significant improvement. 

 

Working with the Code

Open the Visual Studio project and set break points right before and after all SYSPRO calls.

 

XML Results

Once the call returns from the SQL Server via the SYSPRO business object, you will need to capture the results in order to verify what data you need, versus what was returned.

 

Tip: Finding the SYSPRO Business Object Usage

If you aren’t sure where the SYSPRO calls are, remove the reference to the DLL and build. The Error Window list should now be related to just SYSPRO. Make sure to add the reference back. 

 

SQL Server Profiler

Open SQL Server Profiler and set up a new trace. Set the server name and database name. Don’t start the trace just yet. You want as small a sampling as possible in order to make finding the queries easier. You will probably know the parameters that were used for the SYSPRO request. That will help find the queries. 

 

Run to the First Break

Run the code to the first break point.

 

 

You are going to start the trace, and flip back over to Visual Studio as quickly as possible to run to the second breakpoint, then switch back to Profiler and stop the trace. Arrange you windows to make this as easy as possible.

 

Find The Queries

Use the Find tool inside of Profiler to choose either the unique parameter used by the SYSPRO business objects in the TextData field, or search for the Application Name of Microsoft Windows Operating System.  The bottom window of the Profile should now contain a query used by SYSPRO. Don’t stop at just once though. Keep looking. One of the calls to the SYSPRO business object I tracked down had many calls because it found the customer record then all invoices (maybe just open invoices) for the customer. That was a lot of unnecessary calls!

 

 

Save all the SQL queries and the resulting XML and put them in a folder with a single name. Verify the data points you need and rewrite the SQL queries to get just those. Several SYSPRO queries used poorly structured WHERE clauses bringing back many rows when only the top 1 row was the relevant row. If you don’t have the ability to rewrite the query, make sure to just return the top 1, when you only want one row.

 

Tip: SQL Server Analysis

If you use a product like Ignite to identify performance issues, make sure the ODBC connection string’s Application Name is set. This will allow queries that were previously attributed to Microsoft Windows Operating System to be associated with the correct application. This will help you identify what new queries per program, after removing the SYSPRO business object dependency, still need to be tuned.