September 23, 2016 at 9:59 am
Hi all,
My setup is as follows:
Product:
* product_id INT (PK)
* product CHAR(10)
Product_Stat:
* product_Stat_id INT (PK)
* product_id INT (FK)
* onhand decimal(14,4)
Many users take products out of inventory. The problem is that access to the onhand changes can come from stored procs, odbc connects, web connects. I would like to change to the following:
Product:
* product_id INT (PK)
* product CHAR(10)
Product_Stat:
* product_Stat_id INT (PK)
* product_id INT (FK)
* onhand (calc field - sum( quantity) from product_onhand)
Product_OnHand:
* Product_OnHand_Id INT (PK) (Auto-inc)
* Product_id (FK)
* Quantity Decimal(14,4)
As inventory is added, it is a record in Product_onhand, as it is sold, it is a record in Product_onhand. To find out current stock, I would do a sum. this causes great slow down.
Any ideas on how to do this?
Thank you,
Mike
September 23, 2016 at 10:12 am
Please provide create table statments and a small amount of sample data as described HERE[/url], and someone will be happy to help you. Be sure to include the indexes on your tables.
While this is a little more work on your part, it enables all the volunteers here to give you coded and tested solutions. If you set the problem up right, you will find a number of experienced people will tackle your problem.
Also, could you post your current query which is running slow, and the volumes involved?
Thanks 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 23, 2016 at 10:21 am
Looking at what you *have* provided. I have a couple of questions:
Are you trying to develop on-hand amounts for all products, or one at a time, as an order is placed?
Do you have an index for product_onhand by product_ID?
Why do you not simply maintain a column in the product table with the current on-hand amount? - Blocking problems? What is your transaction volume like at peak period?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 26, 2016 at 10:42 am
First, cluster the tables for best (overall) performance. In particular, don't assume that every table should be clustered by identity. That's most often simply not the best way to organize data tables.
Try the structure below, especially given the current performance issues. Yes, there will then be multiple INSERT points for the transaction tables below, but each insert location will match the order needed for the related product id. You can reorganize or even partition the underlying tables if/as needed to accommodate the new clustering.
Also, consider adding a "confirmed" quantity amount and datetime in the product "master"; then, you only have to add rows from that time forward to get a current total. The confirmed amount could be the result of a physical inventory or simply a spot-check for that one item. On heavily used items, you might automatically compute a confirmed quantity every, say, 24 hours.
Product:
* product_id INT (PK)
* product char(10) NOT NULL
* quantity_confirmed decimal(14, 4) NULL
* quantity_confirmed_type tinyint NULL /*code indicating type of confirmation: 'Physical Inventory', 'Checked by Employee', etc.*/
* quantity_confirmed_datetime NULL
* quantity_confirmed_identity NULL
Product_Status:
* product_stat_id IDENTITY(1, 1)
* product_id int FK
* quantity decimal(14, 4) NOT NULL
* quantity_datetime NOT NULL
PRIMARY KEY( product_id, quantity_datetime, $IDENTITY /*just to **guarantee** uniqueness*/ )
Product_Transactions:
* product_transaction_id int IDENTITY(1, 1)
* product_id int (FK)
* transaction_datetime NOT NULL
* quantity decimal(14,4) NOT NULL
PRIMARY KEY( product_id, transaction_datetime, $IDENTITY /*just to **guarantee** uniqueness*/ )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply