11

This is another query optimizer conundrum.

Maybe I'm just over-estimating query optimizers, or maybe I'm missing something - so I'm putting it out there.

I have a simple table

CREATE TABLE [dbo].[MyEntities](
  [Id] [uniqueidentifier] NOT NULL,
  [Number] [int] NOT NULL,
  CONSTRAINT [PK_dbo.MyEntities] PRIMARY KEY CLUSTERED ([Id])
)

CREATE NONCLUSTERED INDEX [IX_Number] ON [dbo].[MyEntities] ([Number])

with an index and some thousand rows in there, Number being evenly distributed in the values 0, 1 and 2.

Now this query:

SELECT * FROM
    (SELECT
        [Extent1].[Number] AS [Number],
        CASE
        WHEN (0 = [Extent1].[Number]) THEN 'one'
        WHEN (1 = [Extent1].[Number]) THEN 'two'
        WHEN (2 = [Extent1].[Number]) THEN 'three'
        ELSE '?'
        END AS [Name]
        FROM [dbo].[MyEntities] AS [Extent1]
        ) P
WHERE P.Number = 0;

does an index seek on IX_Number as one would expect.

If the where clause is

WHERE P.Name = 'one';

however, it becomes a scan.

The case-clause is obviously a bijection, so in theory an optimization should be possible to deduct the first query plan from the second query.

It's also not purely academic: The query is inspired by translating enum values to their respective friendly names.

I'd like to hear from someone who know what can be expected from query optimizers (and specifically the one in Sql Server): Am I simply expecting too much?

I'm asking as I had cases before where some slight variation of a query would make an optimization suddenly come to light.

I'm using Sql Server 2016 Developer Edition.

18

Am I simply expecting too much?

Yes. At least in current versions of the product.

SQL Server will not pick apart the CASE statement and reverse engineer it to discover that if the result of the computed column is 'one' then [Extent1].[Number] must be 0.

You need to make sure that you write your predicates to be sargable. Which almost always involves it being in the form. basetable_column_name comparison_operator expression.

Even minor deviations break sargability.

WHERE P.Number + 0 = 0;

would not use an index seek either even though it is even more straightforward to simplify than the CASE expression.

If you want to search on a string name and get a seek on number you would need a mapping table with the names and numbers and join onto it in the query, then the plan might have a seek on the mapping table followed by a correlated seek on [dbo].[MyEntities] with the number returned from the first seek.

6

Don't project your enum as a case statement. Project it as a derived table like so:

SELECT * FROM
   (SELECT
      [Extent1].[Number] AS [Number],
      enum.Name
   FROM
      [dbo].[MyEntities] AS [Extent1]
      LEFT JOIN (VALUES
         (0, 'one'),
         (1, 'two'),
         (2, 'three')
      ) enum (Number, Name)
         ON Extent1.Number = enum.Number
   ) P
WHERE
   P.Name = 'one';

I suspect you'll get better results. (I didn't convert the Name to ? when missing because this would likely interfere with possibly performance gains. However, you could move the WHERE clause inside the outer query in order to put the predicate on the enum table, or you could return two columns from the inner query, one for the predicate and one for display, where the predicate one is NULL when there's no matching enum value.)

I'm guessing, though, that due to that [Extent1] in there, you're using an ORM such as Entity Framework or Linq-To-SQL. I can't guide you how to accomplish such a projection natively, but, you could use a different technique.

In one project of mine, I reflected code enum values in real tables in the database, through a custom-build class that merged the enum values into the database. (You do have to respect the rule that you must explicitly list your enum values, can never delete any without reviewing your tables, and can never, ever change them, though you already have to observe at least some of this with your current setup).

Now, I was using an enumerable of an Identifier base class that has many different concrete subclasses, but there's no reason it couldn't be done with a plain vanilla enum. Here's an example use:

new EnumOrIdentifierProjector<CodeClassOrEnum, PrivateDbDtoObject>(
   _sqlConnector.Connection,
   "dbo.TableName",
   "PrimaryKeyId",
   "NameColumnName",
   dtoObject => dtoObject.PrimaryKeyId,
   dtoObject => dtoObject.NameField,
   EnumerableOfIdentifierOrTypeOfEnum
)
   .Populate();

You can see that I passed in all the necessary information in order to both write and read the database values. (I had a situation where the current request may not contain all extant values, so needed to return any additional from the database as well as the currently loaded set. I also let the database assign IDs, though for an enum you probably wouldn't want that.)

The idea is that once you have a table that is read/written only once at startup that will reliably have all the enum values, you simply join to it like any other table, and performance should be good.

I hope these ideas are enough for you to make an improvement.

  • Yes, I use EntityFramework and in there is where the solution really should be in an optimal world. Before that happens, your suggestion is one of the best workarounds I believe. – John Aug 11 '16 at 7:12
5

I interpret the question as that you are interested in optimizers in general, but with a special interest for SQL Server. I tested your scenario with db2 LUW V11.1:

]$ db2 "create table myentities ( id int not null, number int not null )"
]$ db2 "create index ix_number on myentities (number)"
]$ db2 "insert into myentities (id, number) with t(n) as ( values 0 union all select n+1 from t where n<10000) select n, mod(n,3) from t"

The optimizer in DB2 rewrites the second query to the first one:

Original Statement:
------------------
SELECT 
  * 
FROM 
  (SELECT 
     number,

   CASE 
   WHEN (0 = Number) 
   THEN 'one' 
   WHEN (1 = Number) 
   THEN 'two' 
   WHEN (2 = Number) 
   THEN 'three' 
   ELSE '?' END AS Name 
   FROM 
     MyEntities
  ) P 
WHERE 
  P.name = 'one'


Optimized Statement:
-------------------
SELECT 
  Q1.NUMBER AS "NUMBER",

CASE 
WHEN (0 = Q1.NUMBER) 
THEN 'one' 
WHEN (1 = Q1.NUMBER) 
THEN 'two' 
WHEN (2 = Q1.NUMBER) 
THEN 'three' 
ELSE '?' END AS "NAME" 
FROM 
  LELLE.MYENTITIES AS Q1 
WHERE 
  (0 = Q1.NUMBER)

The plan looks like:

Access Plan:
-----------
        Total Cost:             33.5483
        Query Degree:           1


      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
      3334 
     IXSCAN
     (   2)
     33.1861 
     4.66713 
       |
      10001 
 INDEX: LELLE   
    IX_NUMBER
       Q1

I don't know much about other optimizers, but I get the feeling that the DB2 optimizer is considered pretty good even among competitors.

  • That's exciting. Can you shed some light on where the "optimized statement" comes from? Does db2 itself give that back to you? - Also, I have trouble reading the plan. I take it "IXSCAN" does not mean index scan in this case? – John Aug 11 '16 at 7:14
  • 1
    You can tell DB2 to explain a statement for you. The information collected is stored in a set of tables, and you can either use visual explain or as in this case the utility db2exfmt (or create your own util). In addition you can monitor a statement and compare the estimated cardinality in the plan with the actual plan. In this plan we can see that it indeed is an indexscan (IXSCAN) and the estimated output from this operator is 3334 rows. Is this bad in SQL server? It knows the startkey and the stopkey so it only scans the relevant rows in DB2. – Lennart Aug 11 '16 at 7:29
  • So what it calls scan does involve seeking, and to be honest, Sql Server's equivalent plan explanations also sometimes call something a scan that does involve seeking, and other times it calls it a seek. I always need to look at the row count to understand what's what. Since there's clearly a 3334 in db2's output it sure does what I was hoping for. Very interesting. – John Aug 11 '16 at 7:36
  • Yes, I also find it confusing sometimes. One have to look at the more detailed information for each operator to really understand what is going on. – Lennart Aug 11 '16 at 7:47
0

In this particular query, it's pretty silly to even have a CASE statement. You're filtering down to one particular case! Perhaps this is just a detail of the particular example query you've given, but if not, you can write this query to get equivalent results:

SELECT
    [Extent1].[Number] AS [Number],
    'one' AS [Name]
FROM [dbo].[MyEntities] AS [Extent1]
WHERE [Extent1].[Number] = 0;

This will give you exactly the same result set, and since you're already hard coding values in a CASE statement anyway, you're not losing any maintainability here.

  • 1
    I think you're missing the point—this is generated SQL from a back-end codebase that works with enums via their string representations. The code that's projecting the SQL is doing the violence to the query. I'm sure the asker, if he were writing the SQL himself, would be able to write a better query. Thus, it's not silly to have a CASE statement at all, because ORMs do that sort of thing. What's silly is that you didn't recognize these simple facets of the problem... (how's that for being indirectly called brainless?) – ErikE Aug 11 '16 at 5:04
  • @ErikE Still kind of silly, since you can just use the numeric value of the enum, assuming C# anyway. (A fairly safe assumption given that we're talking SQL Server.) – jpmc26 Aug 11 '16 at 7:54
  • But you don't have any idea what the use case is. Maybe it would be a huge change to switch to the numeric value. Maybe the enums were retrofit into an existing giant code base. Criticizing without knowledge is ridiculous. – ErikE Aug 11 '16 at 13:35
  • @ErikE If it's ridiculous, then why are you doing it? =) I only answered to point out that if the use case is as simple as the example in the question (which is clearly specified in the preface of my answer), the CASE statement can be eliminated entirely without drawback. Of course there might be unknown factors, but they're unspecified. – jpmc26 Aug 11 '16 at 15:14
  • I have no objection to the factual parts of your answer, just the parts that subjectively characterize. As for whether I'm criticizing without knowledge, I'm all ears to understand any way in which I have failed to use scrupulously clean logic or have made assumptions that are demonstrably false... – ErikE Aug 11 '16 at 15: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.