Jump to content

Orders Controller delays


Recommended Posts

I am experiencing long delays in the BO Orders function (300,000 + orders). Switching on Debug I notice that the SQL statement generated by the php AdminController includes the "SQL_NO_CACHE" clause. I am aware that the database access statement in the controller ($db->executeS($sql, true, false);) has a parameter related to caching (currently set to false). I am aware that removing the no cache clause may have other ramifications. However, I have set the cache parameter (in the relevant executeS code in the controller statement) to true and still the order SQL queries have the "SQL_NO_CACHE" clause inserted. Why does this caching parameter appear not to work?

If I am on the wrong track, related to delays, then any other useful info that can help me remove the "SQL_NO_CACHE" for my experimentation would be very much appreciated.

Thanks in advance

Link to comment
Share on other sites

I had a similiar problem with a loong response time. In my case it was caused by ps_log table.
You could check, if your problem is not caused  also by this table:
1) do a copy of table ps_log
2) empty the table ps_log
3) try to work with BO Orders and compare delaying

4a) if it doesn't help - return back ps_log from a copy
4b) if it helps - return back ps_log from a copy, reduce it's size ( =delete obsolete logs) , check neccesarity of  your logs( if they exist) and create a new index ps_log at least for these four colums (severity, error_code, object_type, object_id)

Link to comment
Share on other sites

3500 logs are quite a little...
Indexing helps only for the cases that object_id is specified - (unfortunatelly it is  related approx 5% logs)
But in your cases I would try to do steps 1)-3) and would check the types of new logs.
(if the new logs will contain "image..." then write)

Link to comment
Share on other sites

When I cleared out I tried steps 1 to 3. Still slow. I have set up indexes as suggested. Still slow order query.

Given that there are now only 2 logs in the table (and originally only 3500 records) I am sure that the delay is not, in any major way, related to the ps_logs table.

I have taken the orders generated SQL statement related to the orders query and run it in phpMyAdmin and MySQL workbench. Removing the "SQL_NO_CACHE" improves the times dramatically  (after the first run which obviously builds the cache). So this clause seems to be the root of the issue.

Thanks again

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...