实体框架是因为派生表的缓慢 [英] Entity Framework is slow because of derived tables
问题描述
我使用的MySQL Connector /净6.5.4使用LINQ到实体,因为实体框架生成使用派生表的查询我经常收到糟糕的查询性能。
这里是什么我遇到好几次一个简单的例子。在C#中,我写这样的查询:
VAR culverCustomers从CS =在db.CustomerSummaries那里cs.Street ==卡尔弗选CS;
//后...
VAR sortedCustomers = culverCustomers.OrderBy(CS => cs.Name).ToList();
而不是生成简单的查询是这样的:
SELECT cust.id FROM customer_summary卡斯特WHERE cust.street =卡尔弗ORDER BY cust.name
实体框架生成具有这样的派生表的查询:
选择Project从.ID(
选择cust.id,cust.name,cust.street FROM customer_summary卡斯特
其中Project1.street =卡尔弗
)作为PROJECT1 - 这里就是EF产生一个毫无意义的派生表
ORDER BY Project1.name
如果我解释这两个查询,我得到这个对于第一个查询:
的id,SELECT_TYPE,表,类型,possible_keys,行
1,小学,地址, ALL,小学,9
1,小学,卡斯特,裁判,小学,4
。 ..和为实体框架查询
的id,SELECT_TYPE,表,类型,possible_keys,行$ b $可怕的东西像这样b 1,PRIMARY,< Derived2的>中ALL,9639
2,派生,地址,ALL,小学,9
2,派生,卡斯特,裁判,小学,4
请注意第一行,在MySQL的解释,它的扫描 9000+记录即可。因为派生表,MySQL正在创建一个临时表和装载的每一行。 (我是这么基于像这样的文章我推断:的派生表和视图效果)
如何防止实体框架使用派生的表,或者我怎么能说服MySQL来?做了明显的优化这样的查询
有关竣工,这里就是这个LINQ查询源视图:
CREATE VIEW customer_summary为
选择cust.id,cust.name,addr.street
客户卡斯特
加入地址在addr.customer_id = cust.id
p>我觉得你的查询语句中缺少'选择'。您还没有确定你想要的结果。
你的查询:
VAR culverCustomers从CS =在db.CustomerSummaries
,其中cs.Street == 卡尔弗;
//没有选择
是什么你从表中选择?试试这个
例如:
VAR culverCustomers从CS在DB = .CustomerSummaries
其中cs.Street ==卡尔弗
选择CS;
I am using MySQL Connector/Net 6.5.4 with LINQ to entities, and I frequently get terrible query performance because the entity framework generates queries that use derived tables.
Here is a simplified example of what I've encountered several times. In C#, I write a query like this:
var culverCustomers = from cs in db.CustomerSummaries where cs.Street == "Culver" select cs;
// later...
var sortedCustomers = culverCustomers.OrderBy(cs => cs.Name).ToList();
Instead of generating simple a query like this:
SELECT cust.id FROM customer_summary cust WHERE cust.street = "Culver" ORDER BY cust.name
The entity framework generates a query with a derived table like this:
SELECT Project1.id FROM (
SELECT cust.id, cust.name, cust.street FROM customer_summary cust
WHERE Project1.street = "Culver"
) AS Project1 -- here is where the EF generates a pointless derived table
ORDER BY Project1.name
If I explain both queries I get this for the first query:
id, select_type, table, type, possible_keys, rows
1, PRIMARY, addr, ALL, PRIMARY, 9
1, PRIMARY, cust, ref, PRIMARY, 4
... and something awful like this for the entity framework query
id, select_type, table, type, possible_keys, rows
1, PRIMARY, <derived2>, ALL, 9639
2, DERIVED, addr, ALL, PRIMARY, 9
2, DERIVED, cust, ref, PRIMARY, 4
Note the first row, where MySQL explains that it's scanning 9000+ records. Because of the derived table, MySQL is creating a temp table and loading every row. (Or so I'm deducing based on articles like this one: Derived Tables and Views Performance)
How can I prevent the Entity Framework from using a derived table, or how can I convince MySQL to do the obvious optimization for queries like this?
For completion, here is the view that is the source for this linq query:
create view customer_summary as
select cust.id, cust.name, addr.street
customers cust
join addresses addr
on addr.customer_id = cust.id
I think your query statement is missing 'select'. You have not identified the record(s) you want. your query:
var culverCustomers = from cs in db.CustomerSummaries
where cs.Street == "Culver";
//no select
what are you selecting from the table? try this
example:
var culverCustomers = from cs in db.CustomerSummaries
where cs.Street == "Culver"
select cs;
这篇关于实体框架是因为派生表的缓慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!