You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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
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 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
The text was updated successfully, but these errors were encountered: