|
Home > Archive > Commerce Server General > September 2005 > BasketGroup growing out of space.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BasketGroup growing out of space.
|
|
|
| Hi,
I help managing a CS2K environment that has been running for over 3
years. A few weeks ago, we started to realize that the BasketGroup
table is growing out of hand. The problem is, whenever people put
anything in the cart, it creates an entry, but only committed orders
will be moved off that table (to OrderGroup). We have over 9 millions
of rows in that table and I bet over 80% of the data file is consumed
by this table. I've tried using the BizDesk, but the basket manager
gave me an error.
I'm just wondering if I can just start running batches of delete
statement and remove the old data completely. Would there be any
leftover effect to the application? I've checked the dependency on the
table, and it appears that OrderSummaryProc is the only object that
depends on this table.
As for the future, is there a way to prevent it from growing? i.e. cap
the basket to 3 months?
Thanks in advance for any insight.
Brian
| |
|
|
You should check if the entry in the basketGroup is not a template of a
basket or basket of register user(if you have this feature)
otherwise it is safe to remove all old entries .
In our site we remove basket older then few weeks and never encouter problem
arik
"Brian" wrote:
> Hi,
>
> I help managing a CS2K environment that has been running for over 3
> years. A few weeks ago, we started to realize that the BasketGroup
> table is growing out of hand. The problem is, whenever people put
> anything in the cart, it creates an entry, but only committed orders
> will be moved off that table (to OrderGroup). We have over 9 millions
> of rows in that table and I bet over 80% of the data file is consumed
> by this table. I've tried using the BizDesk, but the basket manager
> gave me an error.
>
> I'm just wondering if I can just start running batches of delete
> statement and remove the old data completely. Would there be any
> leftover effect to the application? I've checked the dependency on the
> table, and it appears that OrderSummaryProc is the only object that
> depends on this table.
>
> As for the future, is there a way to prevent it from growing? i.e. cap
> the basket to 3 months?
>
> Thanks in advance for any insight.
>
> Brian
>
>
| |
| Ravi Shankar 2005-09-27, 5:53 pm |
| Hi Brian,
IMHO you'd be better off writing a script which does
1. Load all baskets
2. Checks the last update date (or basket date) to be within a specified
period (30 days or 60 days)
3. If not then delete the basket
You can then periodically (fortnightly or monthly) execute this script to
clear up the BasketGroup table (recommended time is after a backup  )
Also the wishlists (common implementation) store baskets/etc as
OrderTemplates.. How many such wishlists per user does you site support ?
--
Ravi Shankar
"Brian" wrote:
> Hi,
>
> I help managing a CS2K environment that has been running for over 3
> years. A few weeks ago, we started to realize that the BasketGroup
> table is growing out of hand. The problem is, whenever people put
> anything in the cart, it creates an entry, but only committed orders
> will be moved off that table (to OrderGroup). We have over 9 millions
> of rows in that table and I bet over 80% of the data file is consumed
> by this table. I've tried using the BizDesk, but the basket manager
> gave me an error.
>
> I'm just wondering if I can just start running batches of delete
> statement and remove the old data completely. Would there be any
> leftover effect to the application? I've checked the dependency on the
> table, and it appears that OrderSummaryProc is the only object that
> depends on this table.
>
> As for the future, is there a way to prevent it from growing? i.e. cap
> the basket to 3 months?
>
> Thanks in advance for any insight.
>
> Brian
>
>
| |
|
| Arik,
Thanks for pointing out the relationship to the registered users.
However, if someone had opened up a basket 2 years ago, and if I
deleted it, would that affect the account in any way? Shouldn't the
application only load up the most recent basket?
Thanks,
Brian
arik wrote:[vbcol=seagreen]
> You should check if the entry in the basketGroup is not a template of a
> basket or basket of register user(if you have this feature)
> otherwise it is safe to remove all old entries .
>
> In our site we remove basket older then few weeks and never encouter problem
> arik
>
> "Brian" wrote:
>
| |
|
| Hi Ravi,
Thanks for your insight. I think this is a good way to tackle the
problem if we don't have as many records. However, in our case, we
simply have too big of a table and we're really hoping to find a way
that will not take too long affecting the live site.
One of our options is to move the baskets from the past 2-3 months to a
dummy table and truncate the whole basketgroup and then re-insert those
in the dummy table back to the basketgroup. Do you think it's doable
or it'll create some problems down the road?
Thanks again for your help.
Regards,
Brian
| |
| Ravi Shankar 2005-09-28, 2:49 am |
| Brian,
If you do a blind truncation then some or lot of users would loose data
which would affect the customer stickiness..
You need to develop a strategy based on some rationale..
You could look at the date_last_changed (something similar) and status_code
(1 for Basket, 2 for OrderTemplates) to run some querying and get numbers...
The size of the table would be associated with what is stored...
There are a few cases to consider
1. If your site allows anonymous users to create & store baskets then you'd
have abondoned baskets. Eliminating these would free up space (this would
require you to parse the basketgroup and check against the UserObject for
matching User_id's). Write a query and check how many ?
2. IF your site allows users to store multiple wishlists (OrderTemplates)
then it is a user behaviour and the question on how to get your users to
behave )
You have the record count of the BasketGroup.. so do some number crunching
and decide how best to tackle this.
Once you've decided on a strategy write some sample scripts and asses the
impact time and effectiveness.
--
Ravi Shankar
"Brian" wrote:
> Hi Ravi,
>
> Thanks for your insight. I think this is a good way to tackle the
> problem if we don't have as many records. However, in our case, we
> simply have too big of a table and we're really hoping to find a way
> that will not take too long affecting the live site.
>
> One of our options is to move the baskets from the past 2-3 months to a
> dummy table and truncate the whole basketgroup and then re-insert those
> in the dummy table back to the basketgroup. Do you think it's doable
> or it'll create some problems down the road?
>
> Thanks again for your help.
>
> Regards,
>
> Brian
>
>
|
|
|
|
|