Performance issue - Query templates

[Former Member]

Hi,

We are using MII 12.0.1. We have intraction with Oracle Database. In DB, currently we have 2 Million records. Now in UI, system has taken more time to fetch the data from DB through Query template. Previously, it has taken max 10 seconds only. but now, it is taken more than 3 minutes to fetch the data from DB through query templates.

Is any performance tuning methods or tools available for Query templates?

[Former Member · Accepted solution]

Senthil,

your tuning should be done by the underlying DB in the first case (maybe new indices, etc.). The following link gives you some hints: [Oracle SQL Tuning Tips|http://www.akadia.com/services/ora_query_tuning.html].

Just some more idea...

- how many rows do you retrieve in your query?

- is it a complex query or just a select with many rows?

- are there bandwidth problems in your DB connection?

Michael

[jcgood25 · Accepted solution]

Senthil,

It would also be good to know what is the UI you refer to, and how are you determining the timing of the query results?

If you are displaying the query results in an MII applet, then you can add the following line in your APPLET definition to see the query results being requested and the msec response time for the resultset (in the browser's Java console):

<PARAM NAME="Trace" VALUE="true">

Regards,

Jeremy

[salvatore_castro]

Senthil,

Please post the table structure, identify keys, along with your SQL statement so we can critique this as well.

Sam

[Former Member]

Hi,

The performance depends on the rowcount used in the query templates. I would suggest you to set the row count accordingly and improve on your select query to filter out the records in more optimum way.

Thanks,

Amit

[agentry_src]

Senthil,

First I would update your version of MII. 12.0.1 had a number of issues which were resolved in later versions.

Regards,

Mike