SQL Generation Optimisation
Joined: 03-Apr-2007
Joined: 03-Apr-2007
Joined: 03-Apr-2007
Joined: 03-Apr-2007
Joined: 03-Apr-2007
Joined: 03-Apr-2007
Joined: 03-Apr-2007
Sorry - we have a strange air pressure today 
I'm using the 3.1.11.1129 version provided by Otis in that thread:
http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20453
Other libraries comes from the latest official build.
Previously (in last message) I've checked in wrong output folder.
Joined: 03-Apr-2007
Joined: 03-Apr-2007
Ok, I have a test scenario. Create the db from the attached script.
Generate the The A,B and C entities.
Then simple ask for entities:
EntityCollection<AEntity> collection = new EntityCollection<AEntity>();
IRelationPredicateBucket filter = new RelationPredicateBucket(AFields.Id < 2000);
IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.AEntity);
prefetchPath.Add(AEntity.PrefetchPathBs)
.SubPath.Add(BEntity.PrefetchPathCs);
string connStr = "****";
using (var adapter = new DataAccessAdapter(connStr))
{
adapter.FetchEntityCollection(collection, filter, prefetchPath);
}
In my case I had an out of memory exception 
- but it doesn't matters because in output window (or probably in the or profiler too) you'll see the following query:
SELECT "public"."b"."c" AS "C", "public"."b"."d" AS "D", "public"."b"."id" AS "Id", "public"."b"."id_a" AS "Ida"
FROM "public"."b" WHERE ( "public"."b"."id_a" IN
(SELECT "public"."a"."id" AS "Id"
FROM "public"."a" WHERE ( ( ( "public"."a"."id" < :p1)))))
| Filename | File size | Added on | Approval |
|---|---|---|---|
| createTestDB.sql | 1,623 | 02-Dec-2011 09:17.17 | Approved |
Joined: 03-Apr-2007
Sorry - really hard week.
Postgresql=>9.0 and 9.1 npgsql=>2.0.11.91
NEXT TIME, please format the posts, the numbers you posted were making the thread unreadable. 
-- Otis
ok
miloszes wrote:
SELECT "content"."fields"."created_at" AS "CreatedAt", "content"."fields"."created_by" AS "CreatedBy", "content"."fields"."id" AS "Id", "content"."fields"."id_association_type" AS "IdAssociationType", "content"."fields"."id_field_name" AS "IdFieldName", "content"."fields"."id_field_sub_name" AS "IdFieldSubName", "content"."fields"."id_lang" AS "IdLang", "content"."fields"."id_main_content" AS "IdMainContent", "content"."fields"."lang_independent" AS "LangIndependent", "content"."fields"."modified_at" AS "ModifiedAt", "content"."fields"."modified_by" AS "ModifiedBy", "content"."fields"."order_number" AS "OrderNumber", "content"."fields"."value" AS "Value" FROM "content"."fields" WHERE ( "content"."fields"."id_main_content" IN ======>>>>>>HERE<<<<<<====== (SELECT "content"."main_contents"."id" AS "ID" FROM "content"."main_contents" WHERE ( ( ( "content"."main_contents"."id" ======>>>>>>HERE<<<<<<====== IN (((601)::int8), ((602)::int8), ((603)::int8), ((604)The marked fragment by ======>>>>>>HERE<<<<<<====== is totally unnecessary. The field contained in where clause is the same returned as a sub query result set and in an addition its a PK.
I disagree. I mean, when you prefetch something the format of the prefetch query is:
SELECT *
FROM [PrefetchTable]
WHERE [TheFk] IN
(
SELECT [ThePkOfOriginalQuery]
... (main FROM and WHERE clauses)
)
If the ParameterisedPrefetchPathThreshold applies, then the query would look like:
SELECT *
FROM [PrefetchTable]
WHERE [TheFk] IN
( @idA1, @idA2, ... , @idA3)
Now, lets examine your test code: You are doing this:
IRelationPredicateBucket filter = new RelationPredicateBucket(AFields.Id < 2000);
IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.AEntity);
prefetchPath.Add(AEntity.PrefetchPathBs)
.SubPath.Add(BEntity.PrefetchPathCs);
// .. fetch
which emits this:
SELECT "public"."b"."c" AS "C",
"public"."b"."d" AS "D",
"public"."b"."id" AS "Id",
"public"."b"."id_a" AS "IdA"
FROM "public"."b"
WHERE ("public"."b"."id_a" IN
(SELECT "public"."a"."id" AS "Id"
FROM "public"."a"
WHERE ((("public"."a"."id" < :p1)))))
... and that's expected. I know you see a redundant part because the query could be written like:
FROM "public"."b"
WHERE "public"."b"."id_a" < :p1
... but that is just because your filter is so simple, and you are only filtering by the PK. Imagine a more complex query on AEntity, then the subquery is always needed.
Now, I don't know how you end up with the query in your first post, because in your test case if I change a little bit the filter:
IRelationPredicateBucket filter = new RelationPredicateBucket(AFields.Id < 30);
...
I get this:
SELECT "public"."b"."c" AS "C",
"public"."b"."d" AS "D",
"public"."b"."id" AS "Id",
"public"."b"."id_a" AS "IdA"
FROM "public"."b"
WHERE ("public"."b"."id_a" IN (:p1, :p2, :p3, :p4,
:p5, :p6, :p7, :p8,
:p9, :p10, :p11, :p12,
:p13, :p14, :p15, :p16,
:p17, :p18, :p19, :p20,
:p21, :p22, :p23, :p24,
:p25, :p26, :p27, :p28, :p29))
... which is also expected and totally right.
So I'm a little confused on what is the optimization you expect on this 
Joined: 03-Apr-2007
The original code has a complicated filters based on FullTextSearch joins and some other indexes. It filters some main entities, and after that applies prefetch paths. Even if I send you an original query, you won't generate the situation because you don't have a production data.
The created test reflects only the prefetch path's situation. After FullTextSearch and some other filters ORM filters the First table. Because more/less I know the main element count and elements count on 2nd level Prefetch Path I set the ParameterisedPrefetchPathThreshold to 600 to disable the complicated filter re usage.
To better reflect a real scenario you should add additional 3 tables at the top of already existing (the 1st and 2nd level prefetch path should contain much less data and should be filtered by more complicated filter). The A table should posses the Id of newly added table and should be added as 3rd prefetch path deep level.
The query in my case can have from 6 to 8 prefetch path graph depth. The referenced test shows more/less prefetch path at 3rd depth level. Because it's generated by yours code (probably prefetch paths takes PK ids of found entities) it'll ask for entities by PKs. Maybe you know when you have to deal with a filtering by PK situation and you can just skip the step in which your asking for PKs by PKs. Like I said in one of my previous posts I know, that in case we have a different then PK=>PK case you just can't do that, but in PK=>PK situation it could increase the LLBLGen performance. The problem is not located only in one query (prefetch path). It manages to all prefetch paths at many levels (especially deeper).
daelmo wrote:
I disagree. I mean, when you prefetch something the format of the prefetch query is:
SELECT * FROM [PrefetchTable] WHERE [TheFk] IN ( SELECT [ThePkOfOriginalQuery] ... (main FROM and WHERE clauses) )
I agree that in case you write above it's true. But we have the following situation:
SELECT *
FROM [PrefetchTable]
WHERE [TheFk] IN
(
SELECT [ThePkOfOriginalQuery]
... (main FROM and WHERE PK in [ThePkOfOriginalQuery])
)
Like I said it's the query generated by the LLBLGen Prefetch Path of let say 3rd depth level. I cannot manipulate that. I don't know even in the approximation how many elements it'll have to change the threshold 
. You just filter PKs by PKs. I know that it could be problematic in implementation, but maybe it's possible. I'm lack of arguments vs my client DBA that the above sql is correct.
daelmo wrote:
If the ParameterisedPrefetchPathThreshold applies, then the query would look like:
SELECT * FROM [PrefetchTable] WHERE [TheFk] IN ( @idA1, @idA2, ... , @idA3)Now, lets examine your test code: You are doing this:
IRelationPredicateBucket filter = new RelationPredicateBucket(AFields.Id < 2000); IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.AEntity); prefetchPath.Add(AEntity.PrefetchPathBs) .SubPath.Add(BEntity.PrefetchPathCs); // .. fetchwhich emits this:
SELECT "public"."b"."c" AS "C", "public"."b"."d" AS "D", "public"."b"."id" AS "Id", "public"."b"."id_a" AS "IdA" FROM "public"."b" WHERE ("public"."b"."id_a" IN (SELECT "public"."a"."id" AS "Id" FROM "public"."a" WHERE ((("public"."a"."id" < :p1)))))... and that's expected. I know you see a redundant part because the query could be written like:
FROM "public"."b" WHERE "public"."b"."id_a" < :p1... but that is just because your filter is so simple, and you are only filtering by the PK. Imagine a more complex query on AEntity, then the subquery is always needed.
Yes I agree with you. But the reason that the filter filters by PK is that this filter was generated by prefetch path.
daelmo wrote:
Now, I don't know how you end up with the query in your first post, because in your test case if I change a little bit the filter:
IRelationPredicateBucket filter = new RelationPredicateBucket(AFields.Id < 30); ...I get this:
SELECT "public"."b"."c" AS "C", "public"."b"."d" AS "D", "public"."b"."id" AS "Id", "public"."b"."id_a" AS "IdA" FROM "public"."b" WHERE ("public"."b"."id_a" IN (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20, :p21, :p22, :p23, :p24, :p25, :p26, :p27, :p28, :p29))... which is also expected and totally right.
So I'm a little confused on what is the optimization you expect on this
Again yes, but I didn't create the where clause, but deeper prefetch path.
I know that because of my language barrier the problem description is muddy. Please let me known whether I can provide more info.
Joined: 03-Apr-2007
Joined: 03-Apr-2007