May 12, 2008 at 12:21 pm
We are currently redesigning our E-Commerce web site. Our current design has one table to hold all of the shopping cart information for all of the active sessions. This one table design is causing performance problems due to locking and blocking of the table from the constant barrage of inserts, updates, and deletes. We have considered having one table per session but then the name of the table becomes a variable, making the SQL within the stored procedures executed rather than compiled.
Does any one have a recommendation on the best design for a high performance E-Commerce database?
Thank you in advance.
May 12, 2008 at 1:17 pm
Making one table per session is probably going to cause more performance problems and other issues than it will solve.
You should have no issues with a header / detail table and a big transaction volume. You may have some issues with the code in the current application or simply some bad indexing that is causing your issues now. You may want to spend the time to really understand the current issues before heading down the re-design path. You may need to redesign, but your current issues could really help you design something that will scale to your needs.
That being said, I have worked on an e-commerce site running a 50 server web cluster against a single set of tables (CartHeader, CartDetail, and some item tables) that used a GUID to separate sessions and it worked fine. We had to be very careful with table indexes and our queries to ensure we did not get resource conflicts, but the design itself worked ok.
May 13, 2008 at 4:00 pm
Thank you for your feedback Michael. I have one question about the large e-commerce web site though. When a user checked out or otherwise exited the site, did you delete their data from the shopping cart tables immediately or did you clean out the data later? I'm trying to think of ways to reduce contention for the tables and I'm wondering if an insert only type approach might help. Thanks again...Andy
May 14, 2008 at 5:30 am
We did not delete the session data when the user exited. At first we wanted to, but we really had a lot of trouble getting accurate information from IIS about session termination so we ended up leaving the data in the tables.
This turned out to be really good because we later discovered that mining this data told us a lot about when people quit the shopping process without buying anything and it helped us figure out usability issues with the site.
The data went into a data warehouse on a daily basis and old session information was purged after a week by another process. The purge process caused a bit of contention, so we had it purge in small pieces at the times of the day that the site itself was reporting there was little activity.
The data from the shopping cart was also copied to orders when an order was finalized so our fulfillment systems did not conflict with the shopping carts. I do not think this was entirely necessary (I hate to have two copies of data), but it worked pretty well.
I cannot take credit for the design, just portions of the implementation.
May 14, 2008 at 7:29 am
That's great information. Thanks again for your time.
May 14, 2008 at 7:50 am
Why would you have the data in the cart and not the fk_ProductID ?
Other than data relevant to the current shopper's actions why would one insert product data into a cart?
I am just asking because I am learning more about carts myself..
Erik
Dam again!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply