8 Comments

1. 

   Seba on 2025-02-17 at 09:07

   If we can use an indexed view, it will be a good solution.

   create VIEW v_order_priority_counts WITH SCHEMABINDING AS
   SELECT o_orderkey, o_orderpriority , licz = count_big(*)
   FROM dbo.orders
   WHERE o_orderpriority in (1,2,3)
   group by o_orderkey, o_orderpriority

   CREATE UNIQUE CLUSTERED INDEX xdsdsd
   ON v_order_priority_counts(o_orderkey);

   CREATE NONCLUSTERED INDEX [test]
   ON [dbo].[v_order_priority_counts] ([order_count])

   And…
   SELECT COUNT_BIG(*) FROM v_order_priority_counts WITH(NOEXPAND) WHERE o_orderpriority = 3;
   0.000s

   And for table we have to use something like this (Erik Darling lessons) to avoid trivial plan. Moreover table can use index from indexed view

   SELECT COUNT_BIG(*) FROM dbo.orders WHERE o_orderpriority = 2 AND 1 = (SELECT 1);
   0.000s

   Reply
   • 

     seba on 2025-02-17 at 09:14

     Of course:
     CREATE NONCLUSTERED INDEX [test]
     ON [dbo].[v_order_priority_counts] ( [o_orderpriority])

     Reply
2. 

   Yash sunil sachdev on 2025-02-17 at 16:45

   CREATE PROCEDURE #List_orders_5 @order_id BIGINT
   AS
   BEGIN
   IF @order_id = 1
   BEGIN
   SELECT COUNT_BIG(*) FROM dbo.orders WHERE o_orderpriority = 1;
   END
   ELSE IF @order_id >= 3
   BEGIN
   SELECT COUNT_BIG(*) FROM dbo.orders WHERE o_orderpriority = @order_id OPTION (RECOMPILE);
   END
   END;

   Reply
3. 

   Art on 2025-02-18 at 03:35

   My initial guess was that CHECK constraint had WITH NOCHECK option 🙂

   SQL does not optimize such simple queries, query optimization is TRIVIAL and information in check constraint it not used.
   In order to force full optimization, one has to complicate query, for example:

   /* Dashboard KPI Query #3 */
   SELECT COUNT_BIG(*) FROM dbo.orders where o_orderpriority = (select 5)

   SELECT COUNT_BIG(*) FROM dbo.orders where 1=1 ADN o_orderpriority = 5

   SELECT COUNT_BIG(*) FROM dbo.orders where o_orderpriority = 5 OPTION (MAXDOP 2)

   Interesting observations:

   #1
   Full optimization is forced(?)/reported when query goes parallel.
   This can be done by lowering cost threshold for parallelism.
   In case like this, in my tests, index scan was still performed for all value despite full optimization.

   #2
   OPTION (RECOMPILE) works just fine despite query optimization reported as TRIVIAL:

   SELECT COUNT_BIG(*) FROM dbo.orders where o_orderpriority = 5 OPTION (RECOMPILE)

   Thanks for the challenge!

   Reply
   • 

     Uwe Ricken on 2025-02-18 at 06:54

     Hey Art,

     super explanation. You hit the nail.
     Due to a trivial plan and simple parameterization the plan is cached with a variable.
     So the plan won’t change!
     RECOMPILE is one option to deal with the problem.

     Thumb up!

     Reply
4. 

   Peter P on 2025-04-01 at 12:57

   I know i’m late on this – But we can also explicitly tell it to avoid parameter sniffing in sql 2022

   SELECT COUNT_BIG(*) FROM dbo.orders WHERE o_orderpriority = 1 OPTION (USE HINT(‘DISABLE_PARAMETER_SNIFFING’));

   Reply
   • 

     Uwe Ricken on 2025-04-01 at 13:03

     Hallo Peter,

     thank you very much for your valued input. Due to the fact that we are running on SQL 2022 this is a valid option.

     Reply
     • 

       Peter P on 2025-04-01 at 13:09

       Just came across your SQL Challenges series — big thanks to you for kicking it off! Brings back memories to my time spend on BeyondRelational. I’ll definitely be following along with interest.

       Reply