Sql-server – Speeding up COUNT(*) – WHERE clause slowing query

A few suggestions:

• Remove the DISTINCT as the ID is the primary key. There is no way you'll get duplicate rows in the result.
• Do not convert the datetime columns. This makes your conditions non-sargable and the query will always do a table scan. The variables would need no conversion either if they are declared as dates but that is not a problem for sargability.
• Use close-open ended ranges for date and datetimes, as @Aaron suggests in the comments. Please read his blog post: Bad habits to kick : mis-handling date / range queries .
• Use NOT EXISTS instead of (SELECT COUNT(*) ...) = 0 to check if there is no rows matching some condition.
• Drop the WITH (NOLOCK) hints. Another blog post: Bad habits : Putting NOLOCK everywhere and a question in this site with several valuable points: Is NOLOCK always bad?.
• Add appropriate indexes. I'd guess separate indexes on (Date1) and (Date2) or an index on (Date1, Date2) would be ok but that needs testing. An index on (CaseId, Date1) will be useful for the subquery.
• (Minor note) always use the schema prefixes. Another blog post by @Aaron Bertrand: Bad habits to kick : avoiding the schema prefix.
• Add OPTION (RECOMPILE) (as @Mikael Eriksson suggested). This basically tells the optimizer to not rely on cached plans but spend some time for each query run to recompile the query - i.e. produce a new plan according to the new parameter values. With 7 variables that can change the query substantially, this seems a very good option. Read the article from @Paul White for more detailed explanation of the "Parameter Embedding Optimization", parameter sniffing and also other options and advantages: Parameter Sniffing, Embedding, and the RECOMPILE Options.

The query rewritten:

SELECT  
    mt.ID 
FROM 
    [dbo].[MyTable] AS mt
WHERE 
      (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
  AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
  AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
  AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
  AND ( @status IS NULL 
     OR @status <> 2 
     OR ( @status = 2 
          AND NOT EXISTS
              ( SELECT * 
                FROM dbo.MyTable AS mt2
                WHERE mt2.CaseID = mt.CaseID 
                  AND mt2.Date1 > mt.Date1
              )
        ) 
      ) 
OPTION (RECOMPILE) ;

• The part about @status can be written in a slightly more compact way.

I don't think this will make much difference (if any) in performance and it may look obfuscated but I add for completeness this version as well:

SELECT  
    mt.ID 
FROM 
    [dbo].[MyTable] AS mt
WHERE 
      (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
  AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
  AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
  AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
  AND NOT EXISTS
      ( SELECT * 
        FROM dbo.MyTable AS mt2
        WHERE @status = 2 
          AND mt2.CaseID = mt.CaseID 
          AND mt2.Date1 > mt.Date1
      )
OPTION (RECOMPILE) ;

Give this a try:

Add this composite index to user_activity: INDEX(user_id, action). Then

SELECT SUM(x.ct), u.user_type, x.action
    FROM (
        SELECT user_id, action, COUNT(*) AS ct
            FROM user_activity
            GROUP BY user_id, action
         ) x
    JOIN user u  ON x.user_id = u.id
    GROUP BY x.action, u.user_type;