Project and Portfolio Management Practitioners Forum
cancel

Data source / Portlet / Module-page load time with complex SQL

Highlighted
hyllplan
Honored Contributor.

Data source / Portlet / Module-page load time with complex SQL

Hi

When using complex data sources with a lot of joins and sub-selects it causes a performance degrade especially if you set up several portlets using that same datasource in the same page of a module.

For example you build a SQL-query that takes about 2sec to run in a SQL-client. When using the same query in a datasource the module page takes about 10sec to load. If you add a couple of more portlets to the same page using the same data source it will add another 5sec.

To me this indicates that the sql querys are not being run in parallell, they are run in serial?

Anyway, 15sec is not a big deal but the behaviour looks a bit strange for the end-user because it seems like nothing happens for 15sec and then suddenly the data is presented properly, e.g. PPM does not give any indication that it is loading the data, no rotating clock, no partially loading page, nothing. I dont like this behaviour. Is it possible to change this behaviour?

If its not possible to present to the end-user that the system actually is loading but it will take some time; do someone have some tricks to speed up the module page load time (that would be even better)

 

Would it be faster if I put my code in a SQL function and call that from the data source?

Should I ask the database team to play with our indexes?

 

I figured if we could cut SQL processing time to 0,5sec it maybe would cut the module page load time with a number of seconds depending of the number of portlets on that particular page.

 

Anyone that could give me some general tips/tricks when building a SQL-query in regards to performance? Some nice web sites maybe?

BTW were running PPM 9.12

 

Regards Johan

2 REPLIES
Utkarsh_Mishra
Acclaimed Contributor.

Re: Data source / Portlet / Module-page load time with complex SQL

Hi Johan,

 

Try to run this report to check the Portlet performance issue:

 

Portlet Performance Report (more details can be found at page 314 "Amin and Install guide")

 

Also PPM runs the dashbaord portlet queries in parallel. For testing the performance improvement you can try these steps:

 

  1. Put less number of column in MINIMIZED view; rest of the columns put in maximized view
  2. Put only 10 rows in minimized view
  3. Limit the Portlet rows to 200 (best practice)
  4. As a best practive keep upto 4 portlets in a single dashboard page
  5. Also check the REFRESH time for the portlets; some time due to this performance issue occurs.
  6. Identify that the performance issue is specific to any timelines.. like when system load is high
  7. Weekly run STATPACKS to improve the SQL query performance

 

This was about the PPM... you need to work on SQL as well.

Try to include best SQL practice as mentioned in the below URL.

http://beginner-sql-tutorial.com/sql-query-tuning.htm

 

To optimize joints refer this: http://www.dba-oracle.com/art_sql_tune.htm

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
hyllplan
Honored Contributor.

Re: Data source / Portlet / Module-page load time with complex SQL

Thanks for the input, the "issue" is not solved really but you gave me some good links thanks!

Johan