posted 17 years ago
Hey All,
Just wanted to share my experiences in achieving a better performance for database operations.
My days have been getting better from the day I started passing/retrieving Arrays to my oracle stored procedure. Loads of data are getting transferred either way in a matter of fractions of milliseconds. Before using Arrays we used to call our stored procs in a loop, which is very time consuming. Then we started passing comma separated data which lead to ugly code and unnecessary parsing. IMHO passing arrays as IN/OUT parameters provide a significant improvement in performance, especially if the volume of data is large.
There is this very strange thing that we found out recently. We have a stored procedure that'll be invoked when a form is created or updated. The form involves lots of data. In certain cases, the form has around 20,000 controls. The data is handed to the stored procedure by 10 odd arrays. In case of a new form, the procedure involves INSERT statements in loops. In case of an existing form being edited by the user, the procedure involves UPDATE statements. The INSERT/UPDATE will be on 2 to 3 tables. We found that in case of a new form, the procedure executed in less than 3 seconds. In case of an editing an existing form, the procedure took approx 1 min to execute. We had no other option but to tune our procedure as 1 min is not tolerable. I tried by luck by explicitly calling a DELETE procedure from the original procedure in the first line. The DELET procedure will delete the data corresponding to this form from 2 to 3 tables, Now since there is no data corresponding to this form in the database, even in case of an existing form being updated, the procedure involves only INSERT statements. This wasy we were able to improve the performance significantly.
But am still not able to believe this. Does this mean that DELETE+INSERT can be better than UPDATE? Obviously, at record level UPDATE will offer better performance than DELETE+INSERT. But in our case since the DELETION didnt involve much condition checking and was bulk in nature, it offered superior performance i guess.
Your comments are welcome.
Cheers,
Arvind
[ August 05, 2006: Message edited by: Arvind Sampath ]
