TopCoder Direct Performance Improvement - Expert Services Report

In this challenge, we want you to help to optimize the performance of Expert Service Report in TopCoder Direct. This is done mainly by the query 'member_spend_by_platform_specialist' now. The Expert Service Report is accessible by TopCoder Staff role at the action (direct/platformSpecialistsReport.action), see expert_service_report.png

Requirements Details

• You need to read all the documents ( see Documentation section for details) we provided first to analysis and make an optimization plan. You can optimize from different aspects:
  • Optimize in the query level 
    • Optimize the current query by rewrite the joins or add / remove indexes for the related tables
    • Split the query it into multiple ones
    • Totally rewrite the query
  • Optimize in the code level - for example, remove unnessary calls, optimize the logic to improve performance etc.
• You need to document your optimizations and why / how they improve the performance. Give measures on how the performance is improved. For example, the showing the report of 10 expert services of 1 year takes 30 seconds before optimization, after optimization it only takes 4 seconds. The measurement should based on the same environment and same test data set. For test data and test environment, please refer to the Testing section.
• You need to implement all the optimizations in your optimization document.

Testing

• Use the test_data_generator.7z to generate a SQL file which inserts the test data into database. You can adjust build.properties to adjust the amout of data you want to generate.  For this challenge, you need to generate
  • 500 - 600 of direct projects
  • Each Direct Project will have 20+ completed challenges
  • Each Direct Project will have 40+ challenges within the whole project
• After generate the test data SQL, you need to write another SQL to insert expert services people for each direct project - insert records into tcs_catalog:direct_project_metadata with project_metadata_key_id = 14, the metadata_value should be value of user ID.
• Insert the test data into the database on the VM assigned to you. Add the indexes which are existent in production database but missing in VM. Refer to production_database_tables.docx.
• Run UPDATE STATISTICS for the related tables so the informix optimizer can capture the latest database information and do proper optimizations.
• You need to figure out a way to measure the performance and 
  • Mesure the performance of the methods which use the old query on VM
  • Mesure the performance of the these methods after implementing your optimizations (assume these method signatures and return values do not change) on VM.

Documentation

The following documents are attached in the challenge forum: Documents Forum

• production_database_tables.docx - the statistics like row number, existing indexes, number of unique rows for each column for all the related tables to my projects query.
• query_member_spend_by_platform_specialist.sql - the SQL query content
• test_data_generator.7z - the test data generator

Repository:

Direct : https://github.com/cloudspokes/direct-app/tree/30047762_performance_expert_services_report 

Remote branch: 30047762_performance_expert_services_report  

DB Scripts SVN: https://coder.topcoder.com/internal/database/scripts/trunk (Revision 85498)

VM Environment

You need a TopCoder VM to work on this challenge

Information about VM can be found below:

VM specific information is found here: http://www.topcoder.com/wiki/display/docs/VM+Image+2.5. Upon registration as a submitter or reviewer you will need to request a VM based on the new TopCoder Cockpit/Direct image. To request your image, please use the forum. Before requesting your VM, you need to ensure that you have an SSH key created and in your member profile. Instructions to do so are here: http://www.topcoder.com/wiki/display/projects/Generate+SSH+Key, and instructions to connect afterwards are here: http://www.topcoder.com/wiki/display/projects/Connect+Using+SSH+Key.

For the direct on VM, please do not use the /home/direct/direct as your work base. Instead you should git clone from https://github.com/cloudspokes/direct-app into another direct for example /home/direct/direct-app.

• Copy token.properties and topcoder_global.properties from the /home/direct/direct to /home/direct/direct-app
• Update topcoder_global.properties to point to the new location  /home/direct/direct-app
  • ���Add a new line direct_service_libdir=${libdir}/tcs/ejb
• Run 'export JAVA_HOME=/opt/jdk1.7.0_67' to use JDK7
• You can run 'ant deploy' to deploy the direct after these steps

Final Submission Guidelines

• A optimization plan document which documents
  • The optimization you make and why / how you make it
  • The improvments you gain by these optimization - time measurements.
• The updated / added queries, java source codes, configurations etc.
• The test data SQL you used.