Hi 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

Hi 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

What  I am not understanding is the radical difference between the following two queries:

** 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

Guess I am on the wrong track: http://sqlblogcasts.com/blogs/simons/archive/2005/01/17/User-Defined-Function-performance-comparison.aspx

Also: http://allinthehead.com/retro/247/user-defined-functions-considered-harmful

Hi 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

The articles that you read as well as the comments that Peter made are both accurate...up to a point.  The problem is that this is not an exact science and what works well for one query may not work well for another.  This is why I mentioned using the SET STATISTICS command to get a better understanding of what the query is actually trying to do.  WHen we are faced with performance issues we start creating different methods and including them into our queries to see where the best performance lies...and this can, at times, be a length process.  We have some CLR stored procedures that do a lot of the work for us...but they end up calling stored procedures and UDFs to do the heavy lifting.  The problem with a stored procedure is that it cannot be included as part of a query, whereas a view can...but a view generally is a worse performer than sprocs...whereas a UDF CAN be included as part of a query and do a lot of work in the process.  So all of these ideas are right...in the right query   That is why you have to play with your query and learn which will work best for your data in certain scenarios.  I will say, though, that SQL Server is definitely strong enough to handle it and to do it quickly!  We have tweaked queries that when we started were running in minutes and when we were finished had them down to 2 seconds or even less.  So it can be done, but you just have to stick with it....keep it up!

Two days and counting...

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