0

In a schema, an int is used as a bitfield, one of those bits is used in a frequently called SQL query (a bit marking entries to be new for processing).

The query (entity framework generated, got it from listening in/querying execution plan cache) is

SELECT TOP (1000)  
[Extent1].[Id] AS [Id],   [Extent1].[Created] AS [Created],   [Extent1].[DataTypeId] AS [DataTypeId],   
[Extent1].[CodePage] AS [CodePage],   [Extent1].[InMsgStateFlags] AS [InMsgStateFlags],   [Extent1].[ContentData] AS [ContentData],   
[Extent1].[ContentHash] AS [ContentHash]  
FROM [dbo].[tInput] AS [Extent1]  
WHERE (([Extent1].[InMsgStateFlags]) & (@p__linq__0)) = @p__linq__1

Only relevant values in reality are 1 for both parameters (though others would be possible).

I tried

 -- add computed column
 alter table dbo.tInput add InMsgStateFlagNew as
 (InMsgStateFlags & 1)
 persisted

 -- add index, filtered index cannot be created on computed column..
 CREATE INDEX IX_InMsgStateFlag_New ON tInput (InMsgStateFlagNew);
 -- where InMsgStateFlagNew = 1;

Sadly, SQL Server (2008 R2) does not automatically recognize this and still scans the index (there is a "normal" index on InMsgStateFlags). Is there any way to make the C#-generated code (well, the statement given above) recognize the index without having to rewrite the C# code?

(Note: I would for the rewrite-case directly query the computed column, probably making it a bit type, and leaving the generic functionality in place and handling the fact that both values are 1 in the special case of going to the computed column. If possible, I would like to avoid that, in order not to introduce breaking changes between code and DB schema.)

  • 1
    Does your query use the computed column if you stick a recompile hint on it and run it from SSMS? – Erik Darling Dec 28 '17 at 1:03
  • 1
    Aside from parameterization, it could be incorrect ansi set options from the client app. – Erik Darling Dec 28 '17 at 2:38
  • @sp_BlitzErik Yes, I put OPTION(RECOMPILE) behind query, when manually executing the query from SSMS. Also, I don't know about ansi options, even after looking up your link. What might affec tthis? – Andreas Reiff Dec 28 '17 at 7:53
  • 1
    Can you rewrite the query so that you test if your parameter is >= than the stored value. Your issue is that by performing the bit operation on the column the index cant really be considered (its scanned to find all the values to apply the bitwise operation to). You want to end up with: WHERE [Extent1].[InMsgStateFlags] >= @p__linq__0. – Kent Chenery Dec 28 '17 at 22:25
  • 1
    You can make it a between to ensure higher bits are excluded if you need @AndreasReiff – Kent Chenery Jan 11 '18 at 3:00

Your Answer

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

Browse other questions tagged or ask your own question.