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

After looking a few things up, the LEFT OUTER JOIN isn't necessarily considered negative and many times a LEFT JOIN is just flat out converted into a LEFT OUTER JOIN.  But playing with your configurations is always a good idea when you are dealing with performance issues.

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.

I got the 3 minutes down to 25 seconds.  Tolerable, but it could be much better.  The change that I made was in the UDF:

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

Help!

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

Wow....lots of good stuff on this post.  My point the other day, Bill, was that many times as developers we create fields that we continually have to update when this should be getting pulled from a stored procedure, view, or user-defined function (which the DDT now supports in the final 1.6.5 update...just FYI ).  Inventory can be a tricky thing at times, and the more stress that you place at the server level and fewer trips to and from the server you make, the faster and better things are going to be.

That is excellent performance!  I must blow the dust off of my book, "Fun With Indexing, Vol. 3".    At this point, 12 million rows is way out of my league...that does give me a nice benchmark, though.  Thanks!

Hi 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

All of the widget on hand quantity updates are done in code in the old application.  For example, the PartMaster table has the OnHandQty field.  It gets adjusted whenever a new record is added/edited in the Production table or the Shipments table.  Also, each month a physical inventory is taken (full plant) and these numbers (stored in an InventoryTransaction table) are synchronized with the OnHandQty in the PartsMaster table.

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

Hi Bill,

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