Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Entity framework query resulting in natural scan [CORE2963] #3345

Closed
firebird-automations opened this issue Apr 11, 2010 · 4 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alexander Muylaert (alexander_gonline.be)

Duplicates CORE1239

Hi

I'm not sure if this is a *bug*, but for some reason it performs a natural scan on my table.
the SQL is generated by the Entity Framework of Microsoft, I can't really change much to it.

The problem is that EF generates first the left outer join and later on the inner join.
If I move the inner first and the left outer last, I have no natural scan.

The problem is that the table T_PERSON might result in thousands and thousands of records.

Thanks in advance, if you need a db structure or complete database, i'll gladly provide it.

SELECT
"Extent1"."F_PERS_ID" AS "F_PERS_ID",
"Extent2"."F_US_ID" AS "F_US_ID"
FROM "T_PERSON" AS "Extent1"
LEFT OUTER JOIN "T_USER" AS "Extent2" ON "Extent1"."F_PERS_ID" = "Extent2"."F_PERS_ID"
INNER JOIN "T_PARTICIPANT" AS "Extent3" ON "Extent1"."F_PERS_ID" = "Extent3"."F_PERS_ID"
WHERE "Extent3"."F_SES_ID" = 59929

Query
------------------------------------------------
SELECT
"Extent1"."F_PERS_ID" AS "F_PERS_ID",
"Extent2"."F_US_ID" AS "F_US_ID"
FROM "T_PERSON" AS "Extent1"
LEFT OUTER JOIN "T_USER" AS "Extent2" ON "Extent1"."F_PERS_ID" = "Extent2"."F_PERS_ID"
INNER JOIN "T_PARTICIPANT" AS "Extent3" ON "Extent1"."F_PERS_ID" = "Extent3"."F_PERS_ID"
WHERE "Extent3"."F_SES_ID" = 59929

Plan
------------------------------------------------
PLAN JOIN (JOIN (Extent1 NATURAL, Extent2 INDEX (UNQ_US_PERS_ID)), Extent3 INDEX (FK_PAR_PERS_ID, FK_PAR_SES_ID))

Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 16,00 ms
Avg fetch time: 0,15 ms

Memory
------------------------------------------------
Current: 55.647.416
Max : 72.571.532
Buffers: 2.048

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 4.684

Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| T_PARTICIPANT| 0 | 106 | 0 | 0 | 0 | 0 |
| T_PERSON| 0 | 0 | 660 | 0 | 0 | 0 |
| T_USER| 0 | 6 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

Query
------------------------------------------------
SELECT
"Extent1"."F_PERS_ID" AS "F_PERS_ID",
"Extent2"."F_US_ID" AS "F_US_ID"
FROM "T_PERSON" AS "Extent1"
INNER JOIN "T_PARTICIPANT" AS "Extent3" ON "Extent1"."F_PERS_ID" = "Extent3"."F_PERS_ID"
LEFT OUTER JOIN "T_USER" AS "Extent2" ON "Extent1"."F_PERS_ID" = "Extent2"."F_PERS_ID"
WHERE "Extent3"."F_SES_ID" = 59929

Plan
------------------------------------------------
PLAN JOIN (JOIN (Extent3 INDEX (FK_PAR_SES_ID), Extent1 INDEX (PK_T_PERSON)), Extent2 INDEX (UNQ_US_PERS_ID))

Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 15,00 ms
Avg fetch time: 0,54 ms

Memory
------------------------------------------------
Current: 55.664.228
Max : 72.571.532
Buffers: 2.048

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 861

Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| T_PARTICIPANT| 0 | 106 | 0 | 0 | 0 | 0 |
| T_PERSON| 0 | 106 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE1239 [ CORE1239 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant