A user enters the quantity of widgets that they produced.  They hit the save button.  The production data is stored in table separate from the widget table.  Once the user clicks save, I want the BO to update the On Hand total of the widget in the widget table.  I am thinking that I would place some code in the AfterSave() method to make this happen.  Here's the rub...what if the user entered the wrong quantity (not that this ever happens--I am just doing some just-in-case type of analysis here  ?  The user finds their production data entry and clicks edit, then proceeds to change the quantity and clicks save.  Now, what is the best way of handling this in order to update the widget table with the difference in quantity?

I have some ideas from previous non-SF days, but is there a good SF way to do this?

Thanks,
Bill 

Personally I would do all that kind of work using database triggers.

Cheers, Peter

The problem is that I currently do not have triggers on the DB objects.  If I add them, then the current application (which uses the sames tables) will have some difficulties.  Trying to mesh a couple of systems together with least amount of fallout is quite a touchy procedure.

I would say you could use the fieldchangedevent to accomplish this. In the bomapper under properties you can turn the generation of these events on. Once you turn on the event generation and rebuild your business object you can handle the event so whenever the field is changed in the main bo child business object is also updated. I use this in several places and it works great, also bound controls automatically update so it makes it very nice.

Paul

Interesting.  Should I set the Property Changed Event to "Unique Event for Each Property"?  Not sure how I could use this to my benefit...I would have to think about this one a bit.  Thanks for the tip.

Bill

We have a transaction posting section for our inventory items that allows the end-user to post the purchase of new items, which in turn updates the tran table showing a quantity of X amount that has been added to inventory.  Then there is a transaction posting when the item is actually sold, which then would be a decrement of the QOH.  The view takes all of this into account and returns the value for me....this was a bit of an adjustment to our thinking at first, but has turned out to be MUCH faster, reduce errors, and be a better road to travel altogether.

I usually set it to be a single event for each property as Strataframe passes the field name in the event args. However I also have created my own event and event args and then raise the  custom event by adding the logic in the custom code section of the BO mapper.

For instance I handle events on reg and o.t. hours worked and pay rate fields with one event handler. in handling these events I can then recalculate gross pay whenver any of these fields change which in turn raises another event that calculates taxes and updates the check business object's value for withholding taxes etc. So in the Ui when a user changes something the event is raised and handled which then updates other values in other bound business objects causing all the bound controls to automatically refresh which makes things real real nice.

As Trent said views are great!! I write payroll software and in foxpro I keep running totals of week to date hours taxes garnishments etc and year to date hours etc in seperate tables, well these tables always seemed to get screwed up and I would have to run routines to recalc them. I now have all this type of logic in views so no more totals tables and the data is always accurate, so depending on what you need to do a view may be a better choice, hopefully this helps a little.

Let me see, then...to gather the widget on hand quantity I would run a query (in a view) that would do something like this:

Production table (get total of the widgets made) - Shipping table (get total of the widgets shipped) = Total OnHand Quantity

What about inventory counts when actual and on hand do not match?  How are those handled?  Inventory adjustment table?

In our case all individually produced widgets are allocated to what we call a PUN (a Packing Unit - same as an SKU - stock keeping unit). Every time a widget is created a trigger on that table recounts the number of widgets on the PUN and recreates the stock count (units/weight) and assigns the values to a PUN record.

While recalculating in-stock values of the fly is ok we steered away from that towards keeping values in the PUN for performance reasons, e.g. a full inventory report could contain 1000's of PUN's each containing about 50 widgets. Because we maintain these values via triggers they are never wrong and, except for the triggers themselves (which are very simple), there is no code involved. For instance if the user produces a widget then deletes it the counts on the PUN are incremented then decremented 'automatically' via the trigger that does a recount every time there is a insert/delete/update on the widget table and this includes things like changing the widgets weight and surface area.

When a PUN is shipped or regraded it is flagged as such in the PUN with a Shipped Date or a Regraded Date and our inventory is simply a total of all PUNs with NULL Shipped Date and Regrade Date. So, as you can see, we don't have a Produced and Shipped table just a Produced table in which the PUN's changes state.

While I understand what you are saying re, in your case, triggers being difficult you may be better of trying to rationalise your current and new application. It's hard to imagine that both systems could have a different understanding of what your inventory position is.

Cheers, Peter

One of our goals is to eliminate the monthly inventories and rely on the system to present good running totals.  So, I am taking a look at this from a practical perspective with my StrataFrame helmet on.

At the moment, I am considering an InventoryAdjustments table (to handle all of those interim inventory adjustments that always seem to occur because of scrap, miscounts, etc.).  The SQL view would combine all four tables (Parts, Production, Shipments, Adjustments) and present something like this:

PartIndex, ProdTotal, ShipTotal, AdjTotal, OnHandQty

With this layout, I could total each table within the view by transaction entry date which would allow for a point-in-time inventory total.  This has great promise.  I would no longer have to code for changes in the totals...the normal BO functionality handles all of that in each of the tables.  This may be the better way to handle the inventory for us.

By the way, one flaw that I am seeing is time-sensitivity.  The longer we go with this, the more data involved in the queries.  Is there a way to data overhead?  Not seeing an easy way to do that...we would have to maintain all production, shipping and adjustment records indefinitely.  I suppose I could write something to move stuff to a history table of sorts and make a cumulative entry into the adjustment table....kind of like a beginning balance.

Well, I am rambling, now.  If anyone can see major gotchas in my approach, just holler.  I think this will help us best transition into the new application without upsetting the data structure too much.

Bill

We keep things effectively forever, at one plant the main widget table has 12 million rows. We also manage point in time inventory and recalculate what was the overall position at any point in time, e.g. the user can say 'what happened in December 2006' and get, what we call the Open/Close Inventory Report, which gives the opening inventory at a selected date, plus all the production, shipping, regrading and adjustments activity during the period and closing inventory (this is at the inventory item level). This report run 'today' would give exactly the same answer as the same report run on 31st Dec 2006.

I believe the key to performance in this kind of (SQL Server anyway) database is the clustered index we have on the widget production date and other strategic indexes but I believe the clusted date index is the most important for us as most reporting is selectable on a date range. For instance I've just VPNed to the plant with 12 million rows and a got a daily production summary for yesterday - 4000 widgets in a two page pivot table format in about 4 seconds from clicking go to having the report rendered - I ran the same report for 26 Feb 2006 and it rendered in about the same time. So that's a trip to the database that returned 4000 physical rows which had to be compiled into a pivot table report by Crystal Reports.

Cheers, Peter

I have a query that results in a total row count of ~980.  It returns the total production for each active part by a certain date.  The production table consists of about 96000 records.  I created a UDF that does the totalling of the production table.  It is a simple query...at least, I thought so.  It takes way too long (~3 min) to run a query like this:

SELECT
 partnum, 
 dbo.fn_GetTotalProduction(partnum, '3/1/2008') AS produced
FROM
 PartsMaster
WHERE
 divisionindex = 1
 AND inactive = 0
ORDER BY
 partnum

The function look like this:

CREATE FUNCTION fn_GetTotalProduction(@partnum varchar(30), @enddate datetime) 
RETURNS float
AS 
BEGIN
 RETURN
  (
  SELECT
   ISNULL(SUM([Units Produced]),0)
  FROM
   PartsMaster Parts
   LEFT OUTER JOIN [4D_Romulus].dbo.[ORDER DATA] Orders ON Orders.[PartNum] = Parts.partnum
   LEFT OUTER JOIN [4D_Romulus].dbo.[PLANT SURVEY] Production ON Production.[Cust Job Num] = Orders.[Cust Job Num]
  WHERE
   Parts.partnum = @partnum
   AND Production.[Date] <= @enddate
  
END

If I run the query without the UDF, it takes less than 3 seconds.  In order to get the production total, shipping total and adjustment total for each active part, I would like to use UDFs; however, it looks like there is a huge overhead in using UDFs.

What am I doing wrong?  I am trying to get my inventory setup like you all outlined above.  Any tip here would be helpful.

Thanks,
Bill

CREATE FUNCTION fn_GetTotalProduction(@partnum varchar(30), @enddate datetime) 
RETURNS float
AS 
BEGIN
 RETURN
  (
  SELECT
   ISNULL(SUM([Units Produced]),0)
  FROM
   PartsMaster Parts
   LEFT OUTER JOIN [4D_Romulus].dbo.[ORDER DATA] Orders ON Orders.[PartNum] = Parts.partnum
   LEFT OUTER JOIN [4D_Romulus].dbo.[PLANT SURVEY] Production ON Production.[Cust Job Num] = Orders.[Cust Job Num] AND Production.[Date] <= @enddate
  WHERE
   Parts.partnum = @partnum
  
END

I moved the date limitation into the join rather than the where clause.  Can I still do better than 25 seconds?

Bill

Yeah... you can get it much snappier that 25 seconds.  This is not an answer that I can just plop out here for you, but I can give you some suggestions to work towards a resolution.  First, SQL Server Management Studio has a feature that shows you the execution plan.  You can have SQL Server show you how it plans to execute the query:

Secondly, SQL Server doesn't like anything negative (i.e. NOT statements).  And if I recollect, a LEFT OUTER JOIN is somewhat negative.  Depending on what you are trying to accomplish, you would be better of doing a LEFT JOIN and then using COALESCE to make sure no NULL values end up in your results...or doing more than one query versus trying to get it all into one statement...thus the beauty of a UDF.

Third, dates can be picky as well.  When you have a date that uses a < or > operator, the query has to sort itself before testing (I am pretty sure...I know that a BETWEEN or doing a <= and >= on the same date does).  So this can be a performance issue as well.  So in this case, you can create a UDF that just does this test for you on a passed parameter so that it isn't part of the query and performance can increase as well.

Finally, SQL Server comes with some statistics command that can help.  For example, you can turn on the following:

SET STATISTICS PROFILE ON - This will spit out the actual execution path that was used when the query takes place.  You will use this within the Management Studio.

SET STATISTICS TIME ON - This will show you exactly how long each portion of the execution took in the Messages window when a query is performed. 

On both of these commands you will have to turn it on for each connection that you have (i.e. if you have more than one query window open).

But that is basically how we work through a speed and performance issue when dealing with large datasets and complicated queries.

Thanks for the tips, Trent.  Can you explain how you handle the date constraint?  Not sure what you mean by your statement above.  Sorry for my density.

Bill

I will keep tweaking my UDF.  Somehow, this thing should generate results in under 3 seconds.  I can do it manually; however, I need to do this with UDFs.

Thanks!
Bill

In SQL Server I would do the following. Both return a summary of production by Grade and run against a transaction table containing about a 1,000,000 rows.

In the first example the HPCDateTime column is indexed so it is very fast. In the second example HPCCreated is not indexed which causes a full table scan.

I used HPCXRefCount to simulate your selection based on Active.

SELECT HPC_HGRID, COUNT(HPCID) AS TotalProduction
FROM dbo.tblHPCHidePiece
WHERE (HPCXrefCount = 0) AND (HPCDateTime BETWEEN CONVERT(DATETIME, '2006-12-05 00:00:00', 102) AND CONVERT(DATETIME, '2006-12-06 00:00:00', 102))
GROUP BY HPC_HGRID

Runtime - sub second. Returns 12 rows - total rows selected ~6000

SELECT HPC_HGRID, COUNT(HPCID) AS TotalProduction
FROM dbo.tblHPCHidePiece
WHERE (HPCXrefCount = 0) AND (HPCCreated BETWEEN CONVERT(DATETIME, '2006-12-05 00:00:00', 102) AND CONVERT(DATETIME, '2006-12-06 00:00:00', 102))
GROUP BY HPC_HGRID

Runtime - 10 seconds. Returns 13 rows - total rows selected ~6000

Note that both queries use GROUP BY to return summary info only. HPCID is the PK on the table and Count(HPCID) gives the count of PK's associated with the grouping column.

Both queries run localy on my development laptop, an ASUS G1 with 2Gb memory.

As Trent advised - the Profiler is a great tool for spotting what is going on inside those pesky long running queries. Sometimes you can't do much about them but most times you can.

Cheers, Peter

In SQL Server I would do the following. Both return a summary of production by Grade and run against a transaction table containing about a 1,000,000 rows.

In the first example the HPCDateTime column is indexed so it is very fast. In the second example HPCCreated is not indexed which causes a full table scan.

I used HPCXRefCount to simulate your selection based on Active.

SELECT HPC_HGRID, COUNT(HPCID) AS TotalProduction
FROM dbo.tblHPCHidePiece
WHERE (HPCXrefCount = 0) AND (HPCDateTime BETWEEN CONVERT(DATETIME, '2006-12-05 00:00:00', 102) AND CONVERT(DATETIME, '2006-12-06 00:00:00', 102))
GROUP BY HPC_HGRID

Runtime - sub second. Returns 12 rows - total rows selected ~6000

SELECT HPC_HGRID, COUNT(HPCID) AS TotalProduction
FROM dbo.tblHPCHidePiece
WHERE (HPCXrefCount = 0) AND (HPCCreated BETWEEN CONVERT(DATETIME, '2006-12-05 00:00:00', 102) AND CONVERT(DATETIME, '2006-12-06 00:00:00', 102))
GROUP BY HPC_HGRID

Runtime - 10 seconds. Returns 13 rows - total rows selected ~6000

Note that both queries use GROUP BY to return summary info only. HPCID is the PK on the table and Count(HPCID) gives the count of PK's associated with the grouping column.

Both queries run localy on my development laptop, an ASUS G1 with 2Gb memory.

As Trent advised - the Profiler is a great tool for spotting what is going on inside those pesky long running queries. Sometimes you can't do much about them but most times you can.

Cheers, Peter

** QUERY 1 **

SELECT
 partindex,
 partnum,
 dbo.fn_GetTotalProduction(partindex, '3/1/2008') AS fnprod
FROM
 PartsMaster
WHERE
 divisionindex = 1
 AND inactive = 0

SQL Server Execution Times:
   CPU time = 28203 ms,  elapsed time = 28376 ms.

** QUERY 2 **

SELECT
 partindex,
 partnum,
 (
 SELECT
  ISNULL(SUM([Units Produced]),0)
 FROM
  PartsMaster Parts
  LEFT OUTER JOIN [4D_Romulus].dbo.[ORDER DATA] Orders ON Orders.[PartNum] = Parts.partnum
  LEFT OUTER JOIN [4D_Romulus].dbo.[PLANT SURVEY] Production ON Production.[Cust Job Num] = Orders.[Cust Job Num] AND Production.[Date] <= '3/1/2008'
 WHERE
  Parts.partindex = PartsMaster.partindex
  AS fnprod
FROM
 PartsMaster
WHERE
 divisionindex = 1
 AND inactive = 0

SQL Server Execution Times:
   CPU time = 985 ms,  elapsed time = 1168 ms.

The UDF in the first query is the embedded select statement in the second query.  All I did was replace the date variable with a string and specified the partindex by the PartsMaster table.  This is a stripped down version of what needs to be happening in my UDF, but if I am going to have this kind of degradation of performance by simply employing a UDF, then I will have to find another way.  Not sure what that will be, but I will have to find it. 

I am on a mission...my goal is to get this procedure, with UDFs or views or whatever, to run under 10 seconds.  Under 3 would be great since I can see that potential; however, I think 10 is a realistic goal.

Bill

Yes, that was what I was saying but didn't... Using a UDF for getting a simple summary is not needed when much more efficient methods are available, i.e. GROUP BY. UDF's are great for complex calculations but inappropriate for simple totals.

Cheers, Peter

My goal is closer.  I am adding a field into my shipments table which will take care of some quantity conversions for me.  That is the most complicated portion of my query, so I thought I would relegate it to the save procedure of the BO.  I'd rather have the intricate calculation done as the row is being saved than on each row during an aggregate query.  With that in place (and it will be done soon...finishing up some testing), the aggregate query speed drops to 1-2 seconds.  That is quite acceptable.

I appreciate everyone's input on this.  I am grateful that there are folks out there who are willing to share their expertise.  And, to provide simple encouragement on a task that is frustrating at times.

Thanks!!
Bill