Order overview extremely slow

[sstottelaar]

Hi there,

I have a performance issue in my Order overview in the CP. It can take from 10 to 40 seconds to load the overview with only 1 order present (I purged empty cart orders). The store has rougly 5.500 products, which takes a while to load but not this extreme. In the debugbar I can see the issue (I think):

As you can see it does 4436 queries which are these:

select * from taxcategories where id = 'default'
select * from taxcategories where id = ?

I tried deleting all products but 10, and the overview loads instantly. So I think that the order overviewpage loops through all product for some reason and that's why it takes so long. Is there any reason why this is happening?

It's all in flat files and a default SC setup.

Laravel Version: 11.31.0
PHP Version: 8.3.13
Composer Version: 2.7.7
Environment: local
Debug Mode: ENABLED
URL: xxx.test
Maintenance Mode: OFF
Timezone: Europe/Amsterdam
Locale: nl

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: file
Database: sqlite
Logs: stack / single
Mail: smtp
Queue: sync
Session: file

Simple Commerce
Currencies: EUR
Gateways: Mollie
Repository: Customer: DuncanMcClean\SimpleCommerce\Customers\EntryCustomerRepository
Repository: Order: DuncanMcClean\SimpleCommerce\Orders\EntryOrderRepository
Repository: Product: DuncanMcClean\SimpleCommerce\Products\EntryProductRepository
Shipping Methods: Pakketpost, Brievenbus pakketje, Afhalen in de winkel
Tax Engine: DuncanMcClean\SimpleCommerce\Tax\Standard\TaxEngine

Statamic
Addons: 9
Sites: 1
Stache Watcher: Enabled (auto)
Static Caching: Disabled
Version: 5.38.0 PRO

Statamic Addons
aerni/font-awesome: 3.0.1
aryehraber/statamic-captcha: 1.13.0
doefom/currency-fieldtype: 1.4.1
doefom/statamic-export: 0.4.0
duncanmcclean/simple-commerce: 7.5.4
edalzell/forma: 3.0.1
mattrothenberg/statamic-mapbox-address: 0.9.0
transformstudios/events: 5.4.3
withcandour/aardvark-seo: 5.0.0

Any help is much appreciated!

[duncanmcclean]

I can't think of anything obvious from the top of my head.... 🤔

I don't know how many orders you have, but if you have > 500, you could maybe try storing orders in a database to see if that helps at all?

[sstottelaar]

Currently there is only 1 order, that's the strange bit (the shop is about to go live). I have another SC project where this isn't an issue, but I can't figure out what is causing the issue. And idea where I might start with debugging such an issue?

[duncanmcclean]

Are most of the queries tax-related?

[sstottelaar]

Yep, all of them are these two:

select * from taxcategories where id = 'default'
select * from taxcategories where id = ?

I'm wondering why the order overview does a query on the products in the first place 🤔

Edit: most of the product in the shop have the tax_category set to 'default', not sure if that helps

[duncanmcclean]

Thanks! I'll try and take a look when I can. I don't know why it's querying the products - but we can probably cache the tax category queries at the least.

[sstottelaar]

Thanks in advance!

[duncanmcclean]

Would you be able to copy the changes from this PR into your project and let me know if it improves things?

[sstottelaar]

Yes, should I give access to the repo? Can I contact you via email to discuss details?

[duncanmcclean]

Yes, should I give access to the repo?

That would be great, thanks! You can send the invite to duncanmcclean/duncan@doublethree.digital. 🙂

[sstottelaar]

I just invited you to the repo, please let me know if you need any more info.

[duncanmcclean]

Thanks! I'll take a look when I can.

[duncanmcclean]

I don't have enough time to take a closer look but it seems like uninstalling the Aardvark SEO addon improves things quite considerably.

With it installed, the orders listing page was taking ~11 seconds to load. However, with it uninstalled, it was taking ~3 seconds, which is much more bareable.

I'm going to merge & tag my changes in #1183 anyway, doing less lookups can't hurt.

[sstottelaar]

Just to let you know, I switched to a database for my orders and customers. This did fix the issue for me for the long loadtimes, but it might still be an issue for others. Im closing this for now.