joomla/joomla-cms — pull request #4925 by rdeutz (2014-10-24 19:32:39+00:00)
Title: batch exec queries instead as each as single query

The reodering in the JTable executes any reordering statement as a single query. If you have a lot of articles in one category it can easily add hunderts of query this results in a large response time and can be result in timeouts.

This is hard to find out, because of the redirect after a save the debug shows only the executed queries from the last request and not from the save request.

This patch implements for the mysqli DB driver a executeBatch command that can handle an array of queries, concats the queries and execute it with the mysqli_multi_query function. This is significant faster, I had on my local env. 1:15 before and 0:02 after the patch. (just watch the videos)

I also did some refactoring because if I did not I had to duplicate code. I don't think there are BC issues because I implemented a new function instead of extending the execute function. I think that is more save because in this area the test coverage is not so good.

# How to test this:
I have made a database backup, because you need a DB with 3000 Articles in one single category to see an effect in a local env. If you are on a shared hosting env. you can run into troubles with much less articles. I have seen that in a database von 300 articles in one category. So you can use my DB backup and them simply try to save an article in the category "Overload 1", it might be take some time, so if you timeouts on a low lover you will probably not seeing the result page, try to set "max_execution_time = 240" or zero and make sure you memory_limit is high enough. If you have done that just wait, grab a coffee or watch a star trek episode.

Then add the patch and do it again you should see that there is a significant difference.

Here are the link to the files you might need to test or view what happened.

https://dl.dropboxusercontent.com/u/959364/dbExecuteBatch.sql.zip
https://dl.dropboxusercontent.com/u/959364/beforepatch.mp4
https://dl.dropboxusercontent.com/u/959364/afterpatch.mp4