Windows Server

Brian · Guest

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 · Guest

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:

Brian · Guest

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:

Ravi Shankar · Guest

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:

Brian · Guest

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 · Guest

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:

	Windows Server Forum Index -> Commerce Server	All times are GMT
Page 1 of 1