Inventory Stock, Triggers vs Views/SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inventory Stock, Triggers vs Views/SP

Author

Topic

F00K33
Starting Member

3 Posts

Posted - 2007-01-22 : 22:48:36

Hello..

I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?

1st Design

PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price

PURCHASE TABLE
PurchaseID
Date
...etc...

PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...

and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE

2nd Design

PRODUCT TABLE
ItemID
ItemName
Price
...etc...

INVENTORY TABLE
ItemID
QtyBegin
...etc...

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...

and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:

QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ........

According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?

Thanks a lot.

Kristen
Test

22859 Posts

Posted - 2007-01-23 : 02:06:57

I would use a trigger. VERY expensive to query SUM every time you want current stock. Plus you wouldn't ever be able to delete any "stale" DETAIL table data ... which in turn will cause the system to run slower over time.

P.S. And you won't be easily able to adjust stock level for a Stock Take (well, I suppose you could do a dummy PurchaseDetail or SalesDetail ...)

Kristen

F00K33
Starting Member

3 Posts

Posted - 2007-01-23 : 12:44:40

is it really VERY expensive? How expensive?

"stale" DETAIL table.. hmmm i think i have forgotten to mention that the 2nd Design will have an accounting period, that in the end, all transaction table (sales, purchases, returns, adjustments) will be cleared, and the QtyOnHand of that period will be the QtyBegin of the next period.

I believe StockTake = Item Adjustment? I think I have included one up there.

Thanks Kristen.

Kristen
Test

22859 Posts

Posted - 2007-01-24 : 08:43:10

"is it really VERY expensive? How expensive?"

For your specific example you will need to make some tests. I am expecting to here that the SUM route was MUCH more expensive!!

Kristen

F00K33
Starting Member

3 Posts

Posted - 2007-01-24 : 13:57:24

I have done the 2nd one before, and the difference to load the 'item' table without calculating QtyOnHand compared to load a view that calculates the QtyOnHand with many tables related (although there are only 2 tables with more than 30000 rows) is about 1 sec. And those transactions occurs within 2-3 months.

Maybe this time, I will try with the trigger...

Thanks Kristen.

LurkingVariable
Starting Member

2 Posts

Posted - 2007-02-09 : 17:33:40

FOOK, I'm doing an inventory app as well, but I've taken the view approach. My app has a pretty rich business logic layer, so I prefer to keep my SQL as dumb as possible and leave my logic in the application (makes my app more transportable, and managing lots of different triggers all over the place is no fun at all). If you create a view in SQL2000, it won't have to do querying everytime. I've heard that your view needs to have an index though, otherwise the view is not maintained and created dynamically each time... which of course would be slow.

Would you be interested in comparing notes on inventory app design? Mine is for a pretty small niche market, so I doubt we'd be in competition.

Kristen
Test

22859 Posts

Posted - 2007-02-10 : 09:03:24

"I've heard that your view needs to have an index though, otherwise the view is not maintained and created dynamically each time"

Ermmm ... sounds like someone is feeding you "Horse ****" to me!

The View is only materialised when you use it.

You can put an Index on a view, which will help performance, but it doesn't cause the data "owned" by the view itself to be stored/cached.

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-10 : 11:05:42

acctually it does.
Indexed views have a clustered index on them. from BOL

quote:

When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.

i understand that quote as data in the view being stored in the db same as tables. so it has pages and leafes etc...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-02-10 : 11:35:31

I stand corrected.

BoL has this too:

"Indexed views are stored in the database in the same format as a table. The query processor treats indexed views the same way it treats base tables"

But having read BoL on this matter I'm not clear how much work it is likely to have to do when the underlying tables used by the view change.

"Creating a clustered index on a view stores the data as it exists at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does."

I presume this is NOT just the data in the index, but also the data in the resultset?

"As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification."

So each time a PurchaseDetail or SalesDetail record is added/changed the ResultSet is going to be updated (and only those rows that are effected, because of the unique constraint of the clustered index on the View)?

Either way, that is going to include a SUM which is going to be a fair bit of work for SQL Server compared to just adjusting a running QtyOnHand column by the variance caused by the PurchaseDetail / SalesDetail transaction.

There are then all the painful requirements that creating a clustered index on a view brings with it. We created one once, it caused so much mayhem to our normal ways of working we had to ditch it and find a different route

(We were trying to get a Unique constraint on a column, but allow multiple NULL values:

CREATE VIEW MyView
AS
SELECT MyMustBeUniqueColumn
FROM MyTable
WHERE MyMustBeUniqueColumn IS NOT NULL

and then make a Unique Index on MyView.MyMustBeUniqueColumn)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-10 : 12:15:18

well i use indexed views for really very high read very low delete/update/insert data.
nothing else.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-02-10 : 12:45:08

Very sound policy IMHO

LurkingVariable
Starting Member

2 Posts

Posted - 2007-02-12 : 14:14:53

Cool, glad we got that worked out. Kristen, I don't think I'll run into your issue (fingers crossed) with the clustered index, since it sounds like you had extra requirements for the index that I won't. I understand that performance won't be optimal with a view vs. triggers, but I much prefer the simplicity and ease of maintenance the view method provides compared to having maintain triggers in multiple locations. Also, I am integrating with a system from a third party provider and while I can create my own tables in the same database I don't know how they'd feel about creating triggers on their tables. Obviously if it begins to interfere with performance I'll need to make adjustments.

Spirit1, I think I'm following the same rule of thumb, but I wondered if you had a ratio for your rule? 100/1 (reads/CUDs)?

KOOK3, did you do any performance testing with the two models? Would love to hear how you're handling Unit Costing and the like.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-12 : 14:18:07

100:1 is pretty ok

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources