2

I have a lot of queries to the same table. All queries looking like this

SELECT COUNT(*) FROM <sameTable> WHERE <whereClause> GROUP BY <groupBy>

Let's the amount of such queries is 40. So the table is iterates 40 times, I'm trying to reduce amount of iterations. I have tried different approaches and finished with this:

SELECT
        SUM(CASE WHEN ((p.statusId = 9)) THEN 1 ELSE 0 END) as metric1
        ,SUM(CASE WHEN ((p.statusId IN (10, 1088))) THEN 1 ELSE 0 END) as metric2
        ,SUM(CASE WHEN ((p.statusId = 11)) THEN 1 ELSE 0 END) as metric3
        ,SUM(CASE WHEN ((p.statusId = 20)) THEN 1 ELSE 0 END) as metric4
        ,(SELECT TOP 1 COUNT(DISTINCT p.CaseId) FROM vw_DashboardWorkbench p
LEFT JOIN vw_DashboardCaseStatusHistory history on p.CaseId = history.CaseId
LEFT JOIN vw_EzProviderUser provider on p.SecondaryPhysicianAdvisorId = provider.ProviderId
WHERE p.statusId = 12
GROUP BY history.AssignedByUser ORDER BY COUNT(DISTINCT p.CaseId) DESC) as metric5
FROM
    vw_DashboardWorkbench p
LEFT JOIN vw_DashboardCaseStatusHistory history on p.CaseId = history.CaseId
LEFT JOIN vw_EzProviderUser provider on p.SecondaryPhysicianAdvisorId = provider.ProviderId

There are two issues:

  1. Summ works on all records, but some metrics need unique count of ids matching the expression.
  2. The metric5 is subquery, I have to use it because I have failed to use aggregation function to get max value of cases in status 12 by user.
  • 1) Replace SUM(CASE WHEN condition THEN 1 ELSE 0 END) with COUNT(CASE WHEN condition THEN some_value [ELSE NULL] END). Count() do not count NULLs and allows DISTINCT. When you need unique count of ids matching the expression then some_value is your id, else use any literal value except NULL (1 for example). – Akina Jul 3 at 12:47
  • 2) Try to move the subquery for metric5 from output list to CTE or to FROM clause as one more source table. Check execution plan to ensure it is not worse than original one... – Akina Jul 3 at 12:51
  • If you're running SQL Server 2014 Enterprise Edition or newer, have you looked into converting your base table into a memory-optimized table? These are obviously a bit more mature in the more receent versions of SQL Server, so if this of interest, I would recommend staging this on the newer platforms. – John Eisbrener Jul 3 at 13:25
  • @JohnEisbrener SQL 2016 SP1 brings in-memory tables, columnstore tables, table and index compression, and partitioning to all SQL Server editions. And Columnstore might be a better choice here. – David Browne - Microsoft Jul 3 at 13:51
  • 1
    @DavidBrowne-Microsoft Thanks for the clarification on the edition requirement changes with 2016 SP1. As for columnstore, that may only be useful with a larger dataset. I would suspect it'll provide little value for a table that doesn't have at least a few million records or if the dataset is very volatile. I hope the OP clarifies the requirements a little more. – John Eisbrener Jul 3 at 14:01
2

Another way of writing the query (without using the vw_EzProviderUser provider table because it is unused in the example would be:

;WITH CTE AS
(
SELECT COUNT(DWB.CaseId) as CountingCaseId, DWB.CaseId,DWB.statusId
FROM
dbo.DashboardWorkbench DWB
GROUP BY DWB.CaseId,DWB.statusId
),
CTE2 AS
(
SELECT  COUNT(DISTINCT DWB.CaseId) as CountingCaseIdDistinct, 
        DWB.statusId
FROM dbo.DashboardWorkbench DWB
LEFT JOIN dbo.DashboardCaseStatusHistory DCSH on DWB.CaseId = DCSH.CaseId
GROUP BY DCSH.AssignedByUser,DWB.statusId
)
SELECT  SUM(CASE WHEN CTE.statusId = 9 THEN CountingCaseId ELSE 0 END) as metric1,
        SUM(CASE WHEN CTE.statusId IN (10, 1088) THEN CountingCaseId ELSE 0 END) as metric2,
        SUM(CASE WHEN CTE.statusId = 11 THEN CountingCaseId ELSE 0 END) as metric3,
        SUM(CASE WHEN CTE.statusId = 20 THEN CountingCaseId ELSE 0 END) as metric4,
        (SELECT MAX(CountingCaseIdDistinct) FROM CTE2
        WHERE CTE2.statusId = 12) as metric5,
        (SELECT MAX(CountingCaseIdDistinct) FROM CTE2
        WHERE CTE2.statusId = 10) as metric6
FROM CTE;

Performance

As of performance this could help depending on your data due to the early grouping on DWB.CaseId,DWB.statusId.

YMMV

There should be better solutions as to make it more performant such as columnstore indexes / better rewrites / indexing / ....

Temp tables

I would advise storing the result of CTE2 (or your subquery) in a temporary table if you are calling it multiple times.

This way, you are only reading from the resultset, not evaluating the same query each time.

(Metric5 and Metric6 in the example).


Testing

This was tested on SQL Server 2017

When adding some indexes to cover all options:

CREATE INDEX IX_statusId_CaseId
ON dbo.DashboardWorkbench(statusId,CaseId);
CREATE INDEX IX_CaseId_statusId
ON dbo.DashboardWorkbench(CaseId,statusId);

CREATE INDEX IX_AssignedByUser_CaseId
ON dbo.DashboardCaseStatusHistory(AssignedByUser,CaseId);
CREATE INDEX IX_CaseId_AssignedByUser
ON dbo.DashboardCaseStatusHistory(CaseId,AssignedByUser);

The only real 'benefit' compared to your plan is not having the compute scalar operator or the multiple SUM(CASE WHEN StatusId = 9 THEN 1 ELSE 0), statusid = 10, ... in your query plan on all data.

In my rewrite, it is only doing that on the grouped count of DWB.CaseId & DWB.statusId.

Part of the plan of your query on my test YMMV

enter image description here

The compute scalar from the part above (second from the right)

enter image description here

Wheras there is earlier grouping in my plan + count(DWB.CaseId)

enter image description here

This part of the plan represents the T-SQL statements in CTE (1)

SELECT COUNT(DWB.CaseId) as CountingCaseId, DWB.CaseId,DWB.statusId
FROM
dbo.DashboardWorkbench DWB
GROUP BY DWB.CaseId,DWB.statusId

the computes are done afterwards, on the result of the above query:

enter image description here

The rest of the plan is virtually the same.

Extra info

There will definetely be better solutions, but somebody might be able to use the DB<>Fiddle below and test for something better.

DB<>Fiddle with test data, execution plans etc.

Temp tables can help if CTE2 or your subquery is evaluated multiple times, indexes matter. And as mentioned in the comments there are other options to explore, but we would need more information.

  • It looked to me as though you could first aggregate the data per history.AssignedByUser and then aggregate them again taking the SUM for metrics 1 to 4 and MAX for metrics 5 and 6. But when I tried it (see the last query) against your data sample, the results were not completely identical to yours or OP's. Then I noticed, that you changed the OP's original query not just by removing vw_EzProviderUser from the query completely, but also by removing vw_DashboardCaseStatusHistory from the main select. – Andriy M Jul 7 at 9:46
  • ...I'm somewhat confused about that, but maybe there was a discussion, since removed, where that was explained? It's still confusing that you are comparing your results with the results of the query that is not exactly the same as the original but I'm happy for the OP (and for you) that your solution worked for them. And good job providing the data sample, that's really nice of you. – Andriy M Jul 7 at 9:49
  • @AndriyM True, I did change the query to omit the left joins where they where 'not used' in the example. Only reason for doing so to keep it more simple as I did not have a real dataset. In future references I will try to get it entirely the same as it is true that this can make examples less correct. (OP should re add the left joins with his dataset). I think that the reason that your example is different, is due to the GROUP BY AssignedByUser only being wanted for metric5 in OP's example. This is why I added CTE2 in my example. Thanks a lot for the feedback! – Randi Vertongen Jul 9 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.