How to Improve Primavera P6 API Performance
Huge potential for improvement in API performance in applications that create objects with uniqueness rules in Primavera P6 based on user-entered values
We have a client in the US who was reporting performance issues with a 3rd party client application that creates large numbers of activities in the P6 database using the Primavera P6 API. I was called to help isolate the issue.
When I joined the meeting, they already had Oracle Enterprise Manager up and running, and it was easy to isolate the query that was taking a long time to run (and using almost all the CPU in the server).
This is a sample of that query:
SELECT DISTINCT task.task_code
FROM task
WHERE
(
(
LOWER(task.task_code) = 'xxx'
AND task.proj_id = 5801
)
OR
(
LOWER(task.task_code) = 'xxx'
AND task.proj_id = 5801
)
OR … 427 more times, one for each activity code
)
AND NOT
(
task.task_id = nnn
)
AND NOT
(
task.task_id = nnn
)
AND NOT … 427 more times, one for each activity code
This query was running for over an hour. There are more than 600 thousand records in the TASK table in this database. I became very suspicious of the LOWER function, because it renders the existing indexes that Oracle provides on the fields task.proj_id and task.task_code useless.
Since the field value is being calculated before the comparison, the existing index cannot be used, and the query takes a long time to run. There is also likely some problem with the query execution plan that prevents the proj_id filter from being applied before the LOWER function is calculated, but in any case, I decided to focus my efforts on the LOWER function and what it did to the execution time of that query.
The first thing I did was to remove the LOWER function call from the query.
Instead of this:
LOWER(task.task_code) = 'xxx'
AND task.proj_id = 5801
Now you have this:
task.task_code = 'xxx'
AND task.proj_id = 5801
And the query completed immediately.
With this new piece of information, I contacted the third party application vendor, who told me that these queries were being generated by the API during the creation of activities. The application was trying to create 429 activities in a batch (a normal use of the API), and the API issued the command shown above to validate if the activities that it is trying to create already exist in the destination database. This is done to detect violations of the Primavera business rules. The LOWER function is used because P6 won’t allow duplicated activity codes, even with different cases (lower case vs. upper case).
Now I had the whole picture – I knew how the LOWER function was being used, and I decided that we should create an INDEX that used the lower function:
CREATE INDEX task_lower_case
ON task (proj_id, LOWER(task_code))
This immediately solved the client’s issue. The query that previously took more than 2 hours to run now is completed in a fraction of a second. The index was created in less than a second, with no downtime, and with a very small footprint since the number of fields in that index is so small.
But… Why stop there? I figured these same rules must apply to other Primavera objects that must have unique codes – I decided to test projects, WBS nodes, and expenses. This is what I have found:
Project and WBS
Sample Query:
(LOWER(projwbs.wbs_name) = 'xxx' and projwbs.parent_wbs_id = 27836 and projwbs.proj_id = 4845)
If a bottleneck is identified creating projects or WBS nodes, then this index would likely help:
CREATE INDEX projwbs_lower_case
ON projwbs (proj_id, LOWER(wbs_name))
Activity Expenses
Sample Query:
(LOWER(projcost.cost_name) = 'xxx' and projcost.task_id = 129460)
If a bottleneck is identified creating expenses, then this index would likely help:
CREATE INDEX projcost_lower_case
ON projcost(proj_id, LOWER(cost_name))
Activity Codes
On August 2017 we identified the same problem against the activity code table. This is the index that fixes the issue:
CREATE INDEX actvcode_lower_case
ON actvcode(LOWER(short_name), actv_code_type_id, parent_actv_code_id)
Conclusion
There is potential for huge improvement in API performance in applications that create objects with uniqueness rules in P6 based on user-entered values, such as WBS nodes, activities and project expenses. We will continue to test this with our products, to ensure our clients are getting the best performance they can. And if you are having problems creating activities through the API, this might very well be the issue.
Please note that this could apply to more than just API programs – P6 Web, Gateway and P6 Web Services are likely also affected by this.
Whatever your performance problem is, we recommend doing an analysis of all different components to understand where the bottleneck is, and then apply the proper solution. And if you need help doing that, please don’t hesitate to contact us.
About the Author
Ravi Wallau - Integration Specialist
Ravi started working with computers as a child, programming with BASIC language on his MSX, a platform once popular outside of North America. In 1998, Ravi started an internship where he further developed his programming skills. After one year, this internship became his first job, where he gained experience developing industrial automation systems.
Over time, Ravi has worked in many different industries and programming languages, developing banking, integration and mobile solutions in C/C++, Java, Visual Basic and C # for a variety of platforms.
From 2007 to 2011, Ravi moved from Brazil, his native country, to Canada with the desire to live and learn another culture, as well as gain experience working in diverse environments.
Since 2009, Ravi has been an important part of the team at Emerald Associates. He has participated in key integration projects with major clients, such as CL&P, Suncor and Nexen, acting as the key technical lead in all phases of the projects. Ravi is also a key contributor at Emerald Associates in the development of unique in-house products such as TAPS, EP-datawarehouse, CAPPS, P6-Auditor and P6-Loader.
Ravi is a dedicated and insightful developer, one who prides himself on the quality of his work. He truly strives to provide optimal functionality so that each piece of a complex integration system fits together to give the end-user what they really need and more.