March 19, 2005 at 3:11 am
Hi everybody out there,
i´m trying to implement a solution to maintain the availability of sold items for an online shop. At the moment the solution is a quite simple view of to tables (sold_items and items_stored) which displays the amount of sold items and the amount of items refilled in our storage. So we can always see how much of any item is on stock.
But then the tables got quite large and any access on the view took quite long. So i just made a Stored_Proc to write the "available" items into a table, which runs every 15 minutes to not overstress the server.
But in the meantime this Procedure also takes about 5 seconds (on a Dual Xeon 2,8 with 2 Gigs Ram).
So i am wondering if there is any global suggestion how to implement such solutions - especially under the aspect that we want to replicate the server via merge replication so that it is possible, that customers can buy items (which will affect Server 1) and we can enter new items to our stock (which will affect Server 2) and that we can maintain a correct value even when the two servers replicate.
Kindest regards from austria
Michael
Michael
March 21, 2005 at 7:37 am
At my company, it is critical that we accurately maintain item status. Whether or not an item is active on the website is a complicated set of conditions (there's a picture, there's stock, it's pickable, there's a blurb, etc.). The way we do this is to have an WebActive flag (bit). It's actually in a second table (one-to-one relationship) along with other metadata-type info. We maintain this active flag through a triggers (there's a separate list and delist trigger because the conditions are different for both and it's easier to understand having two separate triggers). So, on insert or update the criteria is evaluated and the flag is changed accordingly. We also have block from delist and a block from list flag to override the automatic processing if needed (such as a pre-sell when there's no stock and thus would automatically get removed). This has worked excellently for us and really haven't had any problems. You just need to make sure your triggers are correct, as with anything.
Also, we learned that we needed to use cursor-based triggers to handle batch updates. Since a trigger only fires on the last record of a batch, we needed to stick the update/insert in a cursor in the trigger to loop through the batch and apply to logic on each record.
Finally, we also had to be very careful about the order of updates and triggers. It's important to make sure you understand your flow of data to make sure that you're not firing too many triggers and that your updates will not get overwritten by something else.
HTH!
Brian
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply