Custom controller very slow for some users, really slow for others

Question

We have built a custom controller for a customer which passed products data and loads their products in a custom url.

The controllers are basically like this:

class CustomProducts(http.Controller):
    @http.route('/custom', type='http', auth='public', website=True, methods=['GET'])
    def render_custom_products(self, **kw):

        if kw.get('order'):
          if kw.get('order') != "":
            order_by = kw.get('order')
        else:
          order_by = 'list_price desc'

        products = http.request.env['product.product'].sudo().search([('categ_id.name', 'ilike', 'Custom'), ('is_published', '=', 'true'), ('active', '=', 'true')],
        order= order_by,
        limit = 150)
        return http.request.render('custom_module.custom_products', {
          # pass products details to view
          'products': products,
          })

EDIT: This has worked good for over 2 years but suddenly the page is very slow. The weird thing is that the route is very slow for some visitors and very fast (like before) for some. What could be the issue?

Postgres sometimes gives this error: Could not serialize access due to concurrent update SELECT * FROM website_visitor where id = XX FOR NO KEY UPDATE NOAWAIT

The customer only have between 30-50 products for sale at once.

Looks like a good example for an SQL injection. If someone has network access to your system, you can make a request with parameter `order` with SQL code. Because you use the value for the `search()` `order` parameter which is used as SQL query `order by` value. You should solve that like Odoo in the portal, for example like in [sale portal](https://github.com/odoo/odoo/blob/8f94f2ec864bdb54e3deb9b853a2720c5c92f592/addons/sale/controllers/portal.py#L73) by using a dictionary, which holds the order by values. — CZoellner, Mar 06 '23 at 16:41
ontopic: could be a lot of things. The `active=True` is implicit, you can leave it out. Reindexing the indeces on `product_product` and `product_template` tables could help. If you're safe by saying "there are only 30-50 published` products, why not search on `published` only and filtering in python afterwards. Joined querys (like here with `categ_id.name`) are sometimes not very fast in Odoo, so this could help a lot. Why is it fast and sometimes not: depends on the worker lifetime (cache) and which one was used on the request. So "older" workers tend to be faster. — CZoellner, Mar 06 '23 at 16:53
Hi, thanks for the hit for using a dict, will change to that! Ontopic, i found the issue. Added that to the post. — Simon Gottberg, Mar 09 '23 at 17:34

score 1 · Answer 1 · answered Mar 04 '23 at 18:58

1

You should consider adding logging to note the times
http.request.env['product.product'].sudo().search seems to be the only function you are calling, so there can be only 2 (i think) causes of the issue -

Either your search has slowed down
or you have designed things in a way such that your system is not processing things in parallel

You could could try using fastapi/some other framework - to support async or try to diagnose your search issue depending on further finding.

answered Mar 04 '23 at 18:58

arrmansa

518
10

Thanks for your reply! The wierd part is that if the user is logged in as a Odoo user or for their shop, then the page is really fast. Its just slow for non logged in users. – Simon Gottberg Mar 04 '23 at 19:17
If the `search` function calls are the exact same for logged in and non logged in users, then I think the only issue is how your requests are (perhaps not properly) processed in parallel for non-logged in users. In this case a short term fix might be to increase max processes/threads for more concurrency and a long term fix might be to migrate to a more scalable framework / use the scalability features of your current framework. – arrmansa Mar 04 '23 at 19:34
I will try to tweak Postgres if that would help. – Simon Gottberg Mar 04 '23 at 20:41
I upgraded the server and tweaked postgres but still no improvments. – Simon Gottberg Mar 04 '23 at 21:23

score 0 · Accepted Answer · answered Mar 09 '23 at 17:41

I really cant make good sense of the solution but it was changing the images in the Qweb view to not display the images using base encoding but to load the images by their URL.

Old:

t-att-src="'data:image/png;base64,%s' % to_text(product.image_512)"

Working:

t-attf-src="/web/image/product.product/{{product.id}}/image_512"

I still dont understand why it was quick for some users and for some sessions on the same machines.

Custom controller very slow for some users, really slow for others

2 Answers2