Run time of queries

[Former Member]

Hi all,

one short question concerning the run time (duration) of queries.

In my performance tests I have noticed that every query call to a database (select, update ...) takes 200-300ms at minimum. Besides the server size of the DB, where is the time spent? I think the DB is much faster than that, and most of the time is spent in the communication from MII to the DB.

Is the 200ms limit a common value for queries? What are you experiences?

Michael

[Former Member]

On MII 11.5, the "raw" overhead is on the order of 15-20ms. For example, a TagQuery in "Current" mode, with four values in it, when performed against the Simulator server should return in about 20-30 milliseconds.

A trick/tip:

You can monitor performance from the client by adding a Trace applet parameter (with a value of 1 or true) to any MII applet. The Java console will then contain trace information for the URL that was executed and the round-trip response time.

I would suggest creating a page with the iGrid and your SQLQuery, and adding the Trace parameter.

Also, regarding database/SQL queries, if you have a "validation" query defined for your data server (which is used to verify the health of the connection), it will add some overhead.

[Former Member]

Jeremy,

the 200-300ms are values from the workbench when using the DEBUG execution which includes the timestamps.

As I have no access to the MII server, I will ask our Server people to do the tracert to see whats going on.

Rick,

I have tried the TRACE parameter, and it displays a value around 200-300 when calling a query which receives two rows with two columns and displays it in a simple grid.

I will also check what time the validation causes.

Michael

[jcgood25]

ORACLE perhaps?

[Former Member]

If you remove the validation query, and the time goes roughly in half, I would suspect network issues.

Also, how big is the recordset being returned? How many rows/columns?

[Former Member]

Jeremy,

sorry...yes, we are using Oracle 10g.

Michael

[Former Member]

Rick,

in my tests there is only a slight win of performance when deleting the validation string. The variation of results:

With validation string: 130-300, average around 180

Without validation string: 80-280, average around 130

I have selected a table with 2 columns and 2 rows.

I did some more testing, with/without validation and with DNS or IP. There are only slight differences, the Trace shows average values from 166-187.

Michael

Edited by: Michael Otto on Jan 15, 2009 6:42 PM

[Former Member]

For a similar query (against a local SQL Server, using 11.5), my response time averages about 30-40 milliseconds. And that's on a laptop with a relative slow CPU.

The MII team in Exton did some 11.5 vs 12.0 benchmarking - maybe they can private share the results with you.

[Former Member]

A few thoughts:

1) Maybe the web server, logging subsystem, or security system in 12.0 is doing a reverse DNS lookup on the requesting system's IP to turn it into a friendly name. See if there is a way to turn this off.

2) Perhaps the authentication infrastructure in 12.0 is a lot slower than in 11.5.

Rick

[Former Member]

Rick,

today we changed some parameters on one of the DB servers. I changed my query to run on this server, and the Web Trace shows 30 - 40ms, which sounds good.

However, when I run the same query inside a transaction in the workbench, it shows around 180-200ms. So the transaction must cause some overhead.

Michael

[Former Member]

Could you share what parameters you changed?

[jcgood25]

Maybe he ported the tables from Oracle into SQL Server

[Former Member]

Rick and Jeremy,

no, our DB people increased the SGA pool size to 512MB. That solved some DB problems...same on the MII Netweaver server, the pool size of the SAPSR3DB was also increased to 1GB.

However, as I explained, the run time of queries which are called from inside a transaction has not changed. Anyway...we will find a way to get things running faster.

Michael

[jcgood25]

Is your MII Data Server an IDBC type (MII owned and managed JDBC connection pool) or a Datasource type (NW managed pool with MII pointer)?

Have you tried comparing the differences between the two?

[Former Member]

Jeremy,

we use the IDBC type.

We once have set up a Datasource type, but usually use the IDBC because we have no direct access to the NW server. But this may be worth a test. I will come back with the results.

Michael

[Former Member]

Jeremy, which do you expect to be faster?

[Former Member]

Hi all,

to give a little follow up:

The overhead time when executing a query from inside a BLT seems mostly to be due to the underlying hardware power. Lately we extended our hardware (more RAM, more SAPS), and the average time for processing a query in a BLT changed from around 200-300ms to 80ms.

However, if the MII repository (or the virtual file system) is used to save a lot of files (using the "db:/" or "web:/" prefix), the time goes up again.

Michael

[jcgood25]

What are you using as a stop watch? If you are manually timing this from the 'Test' button and running the query from the WB then the info might be a little slanted because the default output is the html table, which is a combination of the xml resultset with the IllumRowsetHTML.xsl applied for the look and feel.

A couple of considerations...

1. How many tcp/ip hops do you get when you do 'tracert' in a command prompt from the server to the DB server (use the same name or IP address in the JDBC connection string setting for the data server)?

2. If you are using the server name instead of IP address is there any additional overhead with the DNS server resolving the name?