What is the validity of the SQL Server index when using EntityFramework?

Asked 2 years ago, Updated 2 years ago, 101 views

This is the first development in the Entity Framework 6, ASP.NET (MVC) project (C#).
DB is the Azure SQL database.
We created a non-clustered index on age because the ID is the primary key and the member table with columns such as name, age takes a long time to search.
If you look at the execution plan below, it will be a key reference to get the name data.

select id, name, age from member where age>=25

To avoid this, it would be better to create a composite index or include option in (age, name).

This raises my question, but in the case of EntityFramework, we obtain it as follows:
I think I got all the columns because I didn't specify a SELECT clause, but does it mean that the index doesn't work?

using(vardb=new SampleContext())
{
    varquery=db.member.Where(m=>m.age>=25);

    foreach (var member in query)
    {
        Console.WriteLine("id={0}, name={1}, age={2}", member.id, member.name, member.age);
    }
}

If I use EntityFramework, will the index become meaningless, and if so, I would like to ask you how to reduce the processing time.
I'm a beginner, so I'm looking forward to working with you.

(Additional)
Attempted to retrieve SQL using the code below.

using(vardb=new SampleContext())
{
    db.Database.Log=sql=>{Console.WriteLine(sql);};

    varquery1 = db.member.Where(e=>e.age==25);

    varquery2=db.member.Where(e=>e.age==25).Select(e=>new{e.id,e.name,e.age});
}

// query1
// SELECT 
//    [Extent1].[id] AS[id],
//    [Extent1].[name]AS[name],
//    [Extent1]. [mail_address] AS[mail_address],
//    [Extent1].[type] AS[type],
//    [Extent 1]. [age] AS [age]
//    FROM [dbo]. [member] AS [Extent1]
//    WHERE [Extent 1]. [age] = @p_linq__0
//
// query2
// SELECT 
//    1 AS [C1]
//    [Extent1].[id] AS [id]
//    [Extent1].[name]AS[name]
//    [Extent 1]. [age] AS [age]
//    FROM [dbo]. [member] AS [Extent1]
//    WHERE [Extent 1]. [age] = @p_linq__0

When I looked at the execution plan in SQL, I found that both were 100% clustered Index Scan with only the primary key (id).
When indexing with the Include option as shown below, query1 was 50% Index Seek, 50% Key Reference, and query2 was 100% Index Seek.
CREATE INDEXIX_member ON member(age) INCLUDE(name)

When I measured the processing time about 100 times, it was about 30 milliseconds on average, but query 2 was slower.
Since most of the existing programs use query1, does that mean that the index is not very effective?

Is it an ant to use the Include option to specify all other columns?

I'm sorry that it's my first time using this.Thank you for your continued support.

c# database sql-server entity-framework sql-azure

2022-09-30 14:57

2 Answers

The Entity Framework is neither magic nor magic, it generates SQL statements according to the instructions, and then causes the DB engine to execute the generated SQL statements.You can view the generated SQL statements in Logging and Receiving Database Operations.

The DB engine parses the received SQL statements to determine if the index can be used.

Therefore,

  • What SQL statements did the Entity Framework generate
  • How the DB Engine interpreted SQL statements

Make a decision in two stages:

When I measured the processing time about 100 times, it was about 30 milliseconds on average, but query 2 was slower.
Since most of the existing programs use query1, does that mean that the index is not very effective?

I understand that the relative value is about 30 ms, but what about the absolute value? Originally, if the processing is done in a short time, the effect of the index may be reduced.Also, does this query account for a significant weight in the overall program process? 80:20 law, even if you speed up enough, there's nothing to gain as a whole.


2022-09-30 14:57

In general, select * from table where index_column=x also uses indexes.
However, if the number of tables to be referenced is small, the optimizer may decide that it is more efficient to get all of them.In this case, the index is not used.

Perhaps amam is confused that the index is used and that the query execution is completed only by referring to the index.

Specifying all columns with the include option means storing everything that should be used as a table in the Index area, so it's an unusual way to use it (although there may be some better scenes).


2022-09-30 14:57

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.