SHAREPOINTBlogs.com Mirror

SQL & CRM performance – GROUP BY and COUNT with CASE statements

This post is really about performance. I had a situation where I started off using subqueries:

  SELECT DISTINCT Account.Name AS BusinessName, Test.New_name AS TestName,
                          (SELECT     COUNT(*)
                            FROM          New_Test T2
                            WHERE       T2.New_AccountId = Account.AccountId AND
                            T2.new_testtypeid = Test.new_testtypeid AND
                                        new_result LIKE '%PASS%') AS PassedItems,
                          (SELECT     COUNT(*)
                            FROM          New_Test T2
                            WHERE   T2.New_AccountId = Account.AccountId AND
                            T2.new_testtypeid = Test.new_testtypeid AND
                                    new_result LIKE '%FAIL%') AS FailedItems
FROM Account INNER JOIN
     New_Test Test ON Account.AccountId = Test.new_accountid

Looking at the above SQL select query, for each row of the returned records two additional select statements must be executed. Well the problem is that CRM views involve a few tables, and the particular company I was doing this for had LOTS of records. While the above example is significantly simplified, reduced in size, shortened etc. etc. the query I had to use was way too much of a performance hit on a quality server. I roughly estimated it will take around 6 days to execute. And I had to use the LIKE keyword. I even tried without using the views.

So after a lot of good old google searching, reading articles and blogs I came up with the following: I grouped the results and used CASE statements within the COUNT function to provide me with the sums of data that I require.

SELECT DISTINCT Account.Name AS BusinessName, Test.New_name AS TestName,
                      COUNT(CASE WHEN New_Test.new_result = 'FAIL' THEN 0 ELSE NULL END) AS FailedItems,
                      COUNT(CASE WHEN New_Test.new_result = 'PASS' THEN 0 ELSE NULL END) AS PassedItems
FROM Account INNER JOIN
     New_Test Test ON Account.AccountId = Test.new_accountid
GROUP BY Account.Name, Test.New_name

The result was 0.06 seconds compared to 6 days. I also had to use some scalar subqueries to make sure any one-to-many relationships aren’t diluting my aggregated results. Scalar queries can be used to prevent group by results to duplicate in one-to-many situations.

Great fun and quite a power saving result.

Read the complete post at http://www.sharepointblogs.com/radi/archive/2007/09/01/sql-amp-crm-performance-group-by-and-count-with-case-statements.aspx