Here is the scenario that I need some input on:

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 

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.

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.

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

It is just a matter of you create a UDF that doesn't perform a query, it would just test on the date and return a true or false.  This way the sorting of the date is not part of the query...your UDF is just called and determines if the record will be included or not. 

Couple of things...first, the checking of the date via UDF doubled the time involved in the query (I am likely not doing it right).  Second, I do not see a way to view the stats generated on the actual UDF used in my query.  Perhaps there is a bottleneck in there, but I am not seeing it.

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