I am designing a ranking application that deals with a huge volume of data (500000+ records).
Its about a marks evaluation and ranking system, where in given the marks in 'n' subjects we need to compute the total marks for various candidates and rank them according to their scores.
The scoring/ranking engine is a stored procedure which first calculates the total score for every candidate and stores this information in a temporary table(note that its a database table not a global temp table).
As the next step we need to ranks the candidates based on their scores and store the rank and the total score for every candidate. For ranking am using the rank() analytical function on Oracle but when I aam trying to update the ranks for every candidate the process takes a huge amount of time. The overall response time that we are looking at is around 20 mins and the current process takes more than 40 mins.
How do we speeden the update queries or are there any alternatives to this procedure.
1) When computing the total sum for each pupil and writing it to the temporary table, if it is possible to do this with a single
create table temp_x as select .....
then this should be quicker than using a cursor. Also if you stick either nologging at the end of the statement or make it a global temp table then this should sped things up.
2)When doing the ranking check the execution plan and make sure the execution plan is efficient. It could be you need indexes on your temp table or you need to analyze it (use execute immediate to do the analysis if required).
3) Use the FORALL statment to do the update so it will do a bulk bind for the update statment if youve got 9i.
Alternatively it might be quicker to store the rank in your temp table (i.e. make it a permanent table) and alter your app code to join the pupil table to your pupil_rank table, so you cut out the update completely.
4) Make sure your database / tables / indexes are tuned i.e. indexes on different disk to the table, making sure there arent chained rows, tables analyzed recently etc.