Application Performance – Agresso

We regularly have problems with the performance of our Agresso system.  I’ll rephrase that – we are regularly asked to look into the performance of our Agresso system.

(Disclaimer: We may not be running the most up-to-date version of the software; however, there are always lessons that can be learned from examining the behaviour of applications.)

The focus of my investigation in this instance was not the same as that of my predecessor’s predecessor, but more on understanding the way the application accessed the database.  So I fired up SQL Server Profiler and aimed it at the Agresso database.

But first, a couple of observations:

  1. As is documented elsewhere, there are no stored procedures in this version of Agresso’s database, which merits a WTF all to itself.
  2. Secondly, the good folks of Agresso have decided that the normal SQL-Server-provided TempDB isn’t good enough, and so it they use their own TempDB.  It’s busy, I’ll grant you, but even so…  Apparently, it is possible to reconfigure the Agresso system to use the real TempDB, but this hasn’t been done for whatever reason.  (I suppose there is an argument to be made for using a dedicated temporary database in an environment where the database server is serving many databases, but it still feels a bit wrong.  This server is dedicated to Agresso.)

And now to the performance monitoring.  I took an afternoon’s activity from the middleware / web application server as my baseline, as most of our users go through this and this was feeling particularly slow.  I made the following notes:

SELECT COUNT(*)

Two components of the system are running this against a particular table every 15 seconds; and six other processes are running it every five minutes.  And it’s not necessarily the most efficient way of getting the rowcount (but at least in this instance it does use an index scan rather than a table scan).

I did some performance comparisons against this 18-row table.  The screenshot below shows the results:

Results of running "SELECT COUNT(*)" vs Querying Sysindexes

Results of running "SELECT COUNT(*)" vs querying Sysindexes

As you can see, the sysindexes query was responsible for just 8% of the query load.  I never did get round to encapsulating both behaviours in stored procedures and going for the foursome!  However, given the number of times that this particular query is run, it seems that an increase in performance is possible through using this technique.

cwTK_Task table

I ran the monitor for just under 3 hours.  During that time, over 27000 queries (of over 57000 captured queries) were calls by CS_SERVICE to update cwTK_Task table.  And the vast majority of those were calls to update the tk_status field by 16384 (ah, the joys of Magic Numbers).

One.  Record.  At.  A.  Time.  (Or “RBAR” – “Row By Agonizing Row” as I’ve seen elsewhere.)

27000 of these isn't going to help

And the list of rows to be updated?  From a relatively straightforward query.

Why was it done this way?  Why not wrap this up in a single stored procedure to do reduce round-trip processing time?  Why not use a set-based query to perform the update even quicker?

Temporary Tables

Things get a bit odder when we look at the use of temporary tables.  Roughly 2% of those queries recorded in my monitoring period were DDL creating or dropping temporary tables.  The process was a little unusual:

  1. Drop table
  2. Create table
  3. Do stuff
  4. Drop table

Now, a few thoughts come to mind here, and I noticed a couple of other interesting “features”:

  • As expected, these are all created in the agrtempdb rather than in the standard MSSQL TempDB.  See above.
  • The table names are hardcoded rather than normal temporary table names.
  • The first drop statement is issued without checking to see if the table exists in the first place.
  • There are still tables in the agrtempdb that were created nine months ago, so the tidy-up process isn’t fool-proof.  If this had been the “real” tempdb, those tables would have been long gone – the server was, until fairly recently, being rebooted every five weeks (don’t ask).
  • The tables themselves were not consistent.  I noticed a few names coming and going regularly – HAGRTPS121, for example – and I filtered out the DDL for this table.  It seems that there are (at least) six different versions of the table being created at various times; and there are other tables that are similarly “blessed”.

Round-trip SELECTs on cwTK_Task

There’s a query run against the cwMQ_message_queue table, that selects a load of data.  And then, for each record that’s returned from that query, there’s another select statement issued to retrieve further data.  Why?

The original select isn’t too complicated:

Original select statement from Agresso Message Queue

This is the starting point for the subsequent select statements

And for each record / cwTK_guid returned in the above query, run the following:

Select statement for each record in the previous Select statement.

This is run for each GUID returned by the previous query.

Once again, I have to ask – why not do it at the server and save all those round trips?  Why not wrap it up in a stored procedure?  Why not use a single set-based query to get the whole lot?

AgrDWS.exe – More SELECT COUNT(*) goodness

Roughly half the queries observed from AgrDWS.exe were of the form

Agresso checking if a table exists

AgrDWS really wants to know if the table exists

Why not replace that little lot with a function call that takes the table name as the parameter?  Save a bit more of that network bandwidth.

Summary

The SQL Server was not stressed at any time during the making of this post.  The application / web server similarly wasn’t stressed.  The main problem is the sheer number of queries each doing their own tiny little thing, and with all the overhead that goes along with each individual query, it adds up…

Application performance enhancement is not just about index tuning.  It’s about designing and writing the application properly.

And a note to the team at Agresso – you’re not the only ones who do this, and I know there’s a new version of your software coming out soon / out now, and I hope that you’ve already addressed these issues…

Advertisements
This entry was posted in SQLServerPedia Syndication, Uncategorized and tagged , . Bookmark the permalink.

5 Responses to Application Performance – Agresso

  1. Thomas says:

    It’s a good thing you say that this is not the newest version of Agresso. In fact there are about 6-7 years since there were any new developments on this version. There is of course a much bigger focus on SQL effiency for the most stressed tables like the task table you have looked at. The reason for not having stored procedures is a design decision rooted in the need for having all the business logic code in the component model, for better separation and easier maintenance.

    All that being said,this is not my favorit datamodel either:-)

  2. thomasrushton says:

    Hi Thomas – I’m glad to hear there’s some progress! Is it really six years old? Strewth. I look forward to seeing a new version soon.

  3. AndersLövgren says:

    This is what I usually do to boost performance in Agresso and Sql Server 2005/2008.
    Databaseserver
    !!No user can be logged in
    ALTER DATABASE AgrProd SET ALLOW_SNAPSHOT_ISOLATION ON
    ALTER DATABASE AgrProd SET READ_COMMITTED_SNAPSHOT ON
    ALTER DATABASE AgrTemp SET ALLOW_SNAPSHOT_ISOLATION ON
    ALTER DATABASE AgrProd SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE AgrProd SET READ_COMMITTED_SNAPSHOT ON
    ALTER DATABASE AgrProd SET MULTI_USER

    –If you upgraded from 2000 to 2005, make sure your predeccessor ran these correct
    DBCC UPDATEUSAGE (‘AgrProd’) WITH COUNT_ROWS
    — Run until no messages is shown
    sp_updatestats
    DBCC CHECKDB WITH DATA_PURITY
    DBCC CHECKDB
    DBCC UPDATEUSAGE (‘AgrProd’) WITH COUNT_ROWS

    App/webserver
    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
    “MaxUserPort”=dword:00004e20
    “TcpTimedWaitDelay”=dword:00000030
    Restart!!

    Read more from BIG vendors suggesting the same
    http://publib.boulder.ibm.com/infocenter/pvcvoice/51x/index.jsp?topic=/com.ibm.websphere.wvs.doc/wvs/tun_conwin.html
    http://download.oracle.com/docs/cd/E12839_01/web.1111/e13814/os_tuning.htm

    You’re runing Agresso 5.4, from 5.5.2 it’s possible to use SQL Server own tempdb for reports. Helps performance alot. 5.4. is really old by now.

    Stored procedure, you just have to live with it. I’ve been doing it for 15 years. No sp in current version 5.5.3 or coming 5.6.

    All the tables cw% is for the electronic invoicing and it really sucks in 5.4. Table structure and performance is much better from 5.5.2. You might noticed that there’s no history clean up in those tables and if you been using it for some years get millions of rows. There’s scripts to run but they don’t do it all the way.

    Some default settings might come along from Sql server 2000. Turn allow_page_locks off. Reduces page locks and increases performance alot and.
    Run this and cut’n’paste it to another sql query tab.
    SELECT ‘ALTER INDEX ‘+sysindexes.name+’ ON dbo.’+sysobjects.name+’ SET ( ALLOW_PAGE_LOCKS = OFF )’
    FROM sysobjects, sysindexes
    WHERE sysobjects.id=sysindexes.id
    AND type = ‘U’
    AND sysindexes.status NOT IN (0,96,8388704)
    ORDER BY sysobjects.name

    As you might noticed most of the recommendations applies to any SQL Server database. And sorry about my english.

    Regards
    Anders Lövgren

  4. thomasrushton says:

    Hi Anders

    Thanks for that comment – it looks like it could be of use!

    Unfortunately (or fortunately) for me, I’m not currently working on an Agresso site, but I’ll keep this handy in case I do see it again…

  5. Pingback: 2010 in review – according to WordPress | The Lone DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s