0

I have an e-commerce site that is generating upwards of 70 orders a week and the client works on a weekly ordering cycle, so we have a template that generates packing-slip for all of the open orders. Otherwise it would be a pain to click through the orders one by one to generate the invoice.

The orders all contain multiple items and options, making the resulting query massive.

Last week they went over 80 orders and the server just choked and I had to do 40 at a time.

Does anyone know of a way to optimise this?

| improve this question | |
  • I don't know of any other way to decrease query weight. I'd re-jig the template to make use of segments to limit server load. Can you post your opening template tags? – stuartmcd69 Jun 27 '17 at 7:34
1

To start with, 70 orders per week should not be choking your server unless it is a severely underpowered box or something is not well written in the code - like embeds within embed loops or something that is causing massive slowdowns. FWIW, I have an EC site that normally does around 50 orders per day on EE3/CT (php7) and another that runs about 80 per week on EE2/CT. (php 5.6)

In my case, I tend to rewrite much of the heavy EE code into exp:query tags because it is so much faster for data output due to a massive reduction in total page queries.

As to your invoice output question, I can think of a couple of ways that may be worth consideration.

  1. The way we handle our order processing here is that all new orders get checked and then we flip the status to 'checked' or 'hold' or whatever. We then output the entire days' orders to a CSV file and that data is used to import data into the factory production system and fulfillment systems. To put it simply - it may be better for you to run out all your order data into a CSV or output file and use that file to mail merge or print invoices via word or excel or something similar. Its easier to manage and easier to control the output when compared to setting up print css and trying to print everything from the browser. If I'm not mistaken, both Word and Excel are able to take data from a file and use it in templates.

  2. If you must print from the browser, this might be painful, but its doable. You'll need to first setup a template that's optimized for fast output - exp:query or doing some smart embedding. Thats also why I mentioned status controls earlier. You'll want a way to target ONLY the orders that need to be printed and ignore everything else. Try and limit your query scope as much as possible prior to getting the data - i.e, status and entry_date to start with. Since printing each page individually is time consuming, you'll want to setup your template to handle all pages in a loop and you'll most likely want to set that template up with some css for printing thats formatted to fit your invoice page size and requirements. Use a css page break between entries to print each entry onto its own page.

Off the top of my head, the above is just two ways that I might try to develop into a more workable solution. I know its not specific information, but perhaps the concepts are enough to get you moving towards your goal. If you need more specific info, I'll be happy to help where I can.

| improve this answer | |
  • Great ideas! thanks for taking the time to reply. I will try a custom exp:query. I already do this for some other custom reports. I do know for sure that it's the loops that select the order items that cause the slowness... We have another template that generates a csv to import all the sales to Xero and it's fine... – Kokako Jun 29 '17 at 23:16
  • just for reference, with 73 orders in the system, this template has 519 queries. If I remove the exp:cart throb:order_items sub tag, it drops to 108 queries. – Kokako Jul 3 '17 at 21:14
  • the order_items tag is indeed very heavy. Cartthrob stores all item info and options in their own table as serialized base64 data so that tag has to fetch the data and decode it prior to display. Just for curiosity, I just ran a CSV output of about 60 orders that includes order_items tags and the profiler reports 194 queries. It takes about 10 seconds to pull the data and download the file. with 150 orders, it was about 30 seconds and due to date/category filtering, the query number ran up to about 300 - pretty heavy, but that is expected considering the filtering I was using. – pixldev Jul 4 '17 at 7:26
  • If you want to look into CSV output, I would recommend using a plugin called http_header. Its for EE2, but can be fixed to run on EE3 as well. You can set the plugin tags to output to CSV file and then you just need to set your tags inside the template to organize your headers and data as needed. You may need to add some php to the template to add BOM and/or line return codes for Excel as well. Let me know if you go this route, I can help walk you through it. – pixldev Jul 4 '17 at 7:30
  • thanks pixldev. Yes i also use http_header on a lot of projects. with regard to my problem, I am abandoning EE and now working on a pure php solution. I have got the basic SQL working. At first I thought I could run my SQL through the {exp:query} but I need to nest to get the info out of exp_cartthrob_order_items and {exp:query} doesn't seem to like nesting... – Kokako Jul 5 '17 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.