Databases
StackOverflow2013
Postsdbo
POEF6 SQL generation for <where nullable columns equals>
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEF6 SQL generation for <where nullable columns equals>
    primarykey
    data
    text
    <p>Trying to upgrade from EF5 to EF6, I encounter significant performance gap searching table by nullable column. Here is a sample:</p> <pre><code>public class Customer { public int Id { get; set; } public int? ManagerId { get; set; } //public virtual Manager Manager { get; set; } } public class MyContext : DbContext { public MyContext(string connstring): base(connstring){} public DbSet&lt;Customer&gt; Customers { get; set; } } class Program { static void Main(string[] args) { var db = new MyContext("CONNSTRING"); var managerId = 1234; var q = from b in db.Customers where b.ManagerId == managerId select b.Id; var s = q.ToString(); } } </code></pre> <p>When EF6 generates SQL, it adds some logic for null handling:</p> <pre><code>SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Customers] AS [Extent1] WHERE (([Extent1].[ManagerId] = @p__linq__0) AND ( NOT ([Extent1].[ManagerId] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[ManagerId] IS NULL) AND (@p__linq__0 IS NULL)) </code></pre> <p>Notice same linq produced more simple SQL under EF5:</p> <pre><code>SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Customers] AS [Extent1] WHERE [Extent1].[ManagerId] = @p__linq__0 </code></pre> <p>I can understand the point developers tried to achieve: if you supply null as parameter, the query where managerId = null will not select any lines. I appreciate the care, but 99.9% times search logic is separated: one usecase looks for <code>where ManagerId == null</code>, another searches for specific id <code>where ManagerId == managerId</code></p> <p>The problem is in big performance impact: MS SQL does not use index on ManagerId and table scan occurs. My project has hundreds of similar searches and on database size about 100GB overall performance after upgrade to EF6 cut by 10 approximately.</p> <p>The question is does anybody know some kind of configuration or convention to disable this roadblock in EF6 and generate simple sql? </p> <p><strong>EDIT:</strong></p> <p>I checked a dozen similar selects in my project and found that:</p> <ul> <li>In some cases SQL SERVER does use the index specified for the field I search. And even in this cases there is slight performance loss: it uses index twice: first time looking for value I specified in parameter, second time looking for null</li> <li><p>EF6 even checks for null when constant is exactly specified as not null, for example:</p> <pre><code> from p in db.PtnActivations where p.Carrier != "ALLTEL" where p.Carrier != "ATT" where p.Carrier != "VERIZON" </code></pre></li> </ul> <p>generates SQL</p> <pre><code> WHERE ( NOT (('ALLTEL' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL))) AND ( NOT (('ATT' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL))) AND ( NOT (('VERIZON' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL))) </code></pre> <p>that did not utilize my index on carrier. EF5 version had</p> <pre><code>( NOT (('ALLTEL' = [Extent1].[Carrier]))) AND ( NOT (('ATT' = [Extent1].[Carrier]))) AND ( NOT (('VERIZON' = [Extent1].[Carrier]) )) </code></pre> <p>that utilized it. </p> <p>Notice the condition <code>('ALLTEL' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL)</code>. Second part is <strong>always</strong> false, but adding this part gives up the index.</p> <p>My routine import of about 1.7M records (that usually had taken about 30 minutes) is up for 3 hours and progress is about 30%. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBen
    1. USBen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEF6 SQL generation for <where nullable columns equals>
      1. USBen
      1. VTFavorite
    2. VO
      singulars
      1. POEF6 SQL generation for <where nullable columns equals>
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEF6 SQL generation for <where nullable columns equals>
      1. USwilx
      1. VTFavorite
    1. COI do not see a difference in the two SQL selects. They should select the same sets of rows. I do not see how could MySQL use different strategy than a table scan in both cases, if there is no index on `ManagerId`. Are you sure that the performance drop is caused by this and not something else?
      singulars
      1. POEF6 SQL generation for <where nullable columns equals>
      1. USwilx
    2. COwilx, the database schema above is artificial, but the pattern is quite common: one table has nullable foreign key on another (of course with index). MS SQL does use index when select is simple like where managerid = parameter and don't use it in EF6 case. I have sql log with performance times for every db operation (implemented using EFTracingProvider in EF5 and DBCommandInterceptor in EF6) and I see exact bottlenecks. To verify, I ran a sql in MSQL SMS and checked the plan in both cases
      singulars
      1. POEF6 SQL generation for <where nullable columns equals>
      1. USBen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload