Kryptronic Software Support Forum

You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.

#1 08-12-2020 10:22:17

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1651

Splitting order tables to increase speed

HI Nick

I think we briefly discussed this a while back but the time has come for me to take action.

My orderitems and order tables are huge, over a million lines and some functions are getting a little slow, so I need to separate the old orders into their own tables.

Looking for a pointer to rewrite the code for various functions so the result is pulled from 2 or more tables(I might split tables into years such as ecom_orders_2014, etc)

Based on the orderman_searchp function, would it be better to have single mysql statement that includes UNION so multi tables are checked?
The problem there is $this->CORE_DB->sql_do(array('sql'    => $sql, 'table'  => 'ecom_orders', only allows one table to be checked, so would probably mean rewriting the sql_do function as well.

or would it be better/easier to run sql_do function individually for each tables and then do an array_merge


- Euorpacart 8

Offline

 

#2 08-13-2020 09:15:11

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19577
Website

Re: Splitting order tables to increase speed

We have a solution for this in 9.0.4, which we're going to push out this month.  In 9.0.4 we introduced an archive tables for orders, order items and payments.  The archives hold all order data older than 400 days.  We changed the ordersave() and orderlookup() functions to handle this.  I'd advise waiting for the update.


Nick Hendler

Offline

 

#3 08-13-2020 15:04:57

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1651

Re: Splitting order tables to increase speed

Thanks Nick. I look forward to 9.0.4

For now, I have hacked orderlookup() and ordersearchp() so the correct database is searched based on the year the order was placed.

Backend works great.

Frontend users can only view orders since 2017 which is probably more than enough.


- Euorpacart 8

Offline

 

#4 08-17-2020 12:35:41

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19577
Website

Re: Splitting order tables to increase speed

Very good.  Thanks for the update.


Nick Hendler

Offline

 

#5 08-21-2020 17:29:23

Zdalny
Member
From: Bydgoszcz
Registered: 08-21-2020
Posts: 1
Website

Re: Splitting order tables to increase speed

Does anyone know what's new in the update?

Offline

 

#6 08-24-2020 09:26:30

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19577
Website

Re: Splitting order tables to increase speed

The full features list will be published when it's released.  I think there's between 60-80 changes and functionality enhancements.


Nick Hendler

Offline

 

#7 11-16-2020 09:34:58

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1651

Re: Splitting order tables to increase speed

Any updates on 9.0.4 release date yet?


- Euorpacart 8

Offline

 

#8 11-16-2020 09:48:17

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19577
Website

Re: Splitting order tables to increase speed

We're pushing to get it out this month.


Nick Hendler

Offline

 

Board footer