SQL Generation Optimisation

Posts   
 
    
miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 30-Nov-2011 12:41:01   

Hi,

I have another ask to you. Is it possible to optimize the following query, when you generate it? It's a query generated by one of the prefetch paths.

Like you can see in the example, there is an unnecessary subquery, because it selects id_main_contents from main_contents by the same id_main_contents. Other words (you cannot see it right now because you don't have a db schemma right now) it does: selects PKs from some table by the same PKs (Primary Keys).

The following query were sent to me by my client's DBA and ask to fix that. I'm already using the ParameterisedPrefetchPathThreshold to optimise some other queries, but in this case it's very hard to do that, because it's a web service and I don't know how many input parameters I'll receive.

Does it sounds to you ok to optimise the sql generation algorithm somehow? I can send you a db schema via the hep desk if necessarily, but I can't send you any of data.

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 
(SELECT "content"."main_contents"."id" AS "ID" 
FROM "content"."main_contents" 
WHERE ( ( ( "content"."main_contents"."id" 
IN (((601)::int8), ((602)::int8), ((603)::int8), ((604)::int8), ((605)::int8), ((606)::int8), ((607)::int8), ((608)::int8), ((609)::int8), ((610)::int8), ((611)::int8), ((612)::int8), ((613)::int8), ((614)::int8), ((615)::int8), ((616)::int8), ((617)::int8), ((618)::int8), ((619)::int8), ((620)::int8), ((621)::int8), ((622)::int8), ((623)::int8), ((624)::int8), ((625)::int8), ((626)::int8), ((627)::int8), ((628)::int8), ((629)::int8), ((630)::int8), ((631)::int8), ((632)::int8), ((633)::int8), ((634)::int8), ((635)::int8), ((636)::int8), ((637)::int8), ((638)::int8), ((639)::int8), ((640)::int8), ((641)::int8), ((642)::int8), ((643)::int8), ((644)::int8), ((645)::int8), ((646)::int8), ((647)::int8), ((648)::int8), ((649)::int8), ((650)::int8), ((651)::int8), ((652)::int8), ((653)::int8), ((654)::int8), ((655)::int8), ((656)::int8), ((657)::int8), ((658)::int8), ((659)::int8), ((660)::int8), ((661)::int8), ((662)::int8), ((663)::int8), ((664)::int8), ((665)::int8), ((666)::int8), ((667)::int8), ((668)::int8), ((669)::int8), ((670)::int8), ((671)::int8), ((672)::int8), ((673)::int8), ((674)::int8), ((675)::int8), ((676)::int8), ((677)::int8), ((678)::int8), ((679)::int8), ((680)::int8), ((681)::int8), ((682)::int8), ((683)::int8), ((684)::int8), ((685)::int8), ((686)::int8), ((687)::int8), ((688)::int8), ((689)::int8), ((690)::int8), ((691)::int8), ((692)::int8), ((693)::int8), ((694)::int8), ((695)::int8), ((696)::int8), ((697)::int8), ((698)::int8), ((699)::int8), ((700)::int8), ((701)::int8), ((702)::int8), ((703)::int8), ((704)::int8), ((705)::int8), ((706)::int8), ((707)::int8), ((708)::int8), ((709)::int8), ((710)::int8), ((711)::int8), ((712)::int8), ((713)::int8), ((714)::int8), ((715)::int8), ((716)::int8), ((717)::int8), ((718)::int8), ((719)::int8), ((720)::int8), ((721)::int8), ((722)::int8), ((723)::int8), ((724)::int8), ((725)::int8), ((726)::int8), ((727)::int8), ((728)::int8), ((729)::int8), ((730)::int8), ((731)::int8), ((732)::int8), ((733)::int8), ((734)::int8), ((735)::int8), ((736)::int8), ((737)::int8), ((738)::int8), ((739)::int8), ((740)::int8), ((741)::int8), ((742)::int8), ((743)::int8), ((744)::int8), ((745)::int8), ((746)::int8), ((747)::int8), ((748)::int8), ((749)::int8), ((750)::int8), ((751)::int8), ((752)::int8), ((753)::int8), ((754)::int8), ((755)::int8), ((756)::int8), ((757)::int8), ((758)::int8), ((759)::int8), ((760)::int8), ((761)::int8), ((762)::int8), ((763)::int8), ((764)::int8), ((765)::int8), ((766)::int8), ((767)::int8), ((768)::int8), ((769)::int8), ((770)::int8), ((771)::int8), ((772)::int8), ((773)::int8), ((774)::int8), ((775)::int8), ((776)::int8), ((777)::int8), ((778)::int8), ((779)::int8), ((780)::int8), ((781)::int8), ((782)::int8), ((783)::int8), ((784)::int8), ((785)::int8), ((786)::int8), ((787)::int8), ((788)::int8), ((789)::int8), ((790)::int8), ((791)::int8), ((792)::int8), ((793)::int8), ((794)::int8), ((795)::int8), ((796)::int8), ((797)::int8), ((798)::int8), ((799)::int8), ((800)::int8), ((801)::int8), ((802)::int8), ((803)::int8), ((804)::int8), ((805)::int8), ((806)::int8), ((807)::int8), ((808)::int8), ((809)::int8), ((810)::int8), ((811)::int8), ((812)::int8), ((813)::int8), ((814)::int8), ((815)::int8), ((816)::int8), ((817)::int8), ((818)::int8), ((819)::int8), ((820)::int8), ((821)::int8), ((822)::int8), ((823)::int8), ((824)::int8), ((825)::int8), ((826)::int8), ((827)::int8), ((828)::int8), ((829)::int8), ((830)::int8), ((831)::int8), ((832)::int8), ((833)::int8), ((834)::int8), ((835)::int8), ((836)::int8), ((837)::int8), ((838)::int8), ((839)::int8), ((840)::int8), ((841)::int8), ((842)::int8), ((843)::int8), ((844)::int8), ((845)::int8), ((846)::int8), ((847)::int8), ((848)::int8), ((849)::int8), ((850)::int8), ((851)::int8), ((852)::int8), ((853)::int8), ((854)::int8), ((855)::int8), ((856)::int8), ((857)::int8), ((858)::int8), ((859)::int8), ((860)::int8), ((861)::int8), ((862)::int8), ((863)::int8), ((864)::int8), ((865)::int8), ((866)::int8), ((867)::int8), ((868)::int8), ((869)::int8), ((870)::int8), ((871)::int8), ((872)::int8), ((873)::int8), ((874)::int8), ((875)::int8), ((876)::int8), ((877)::int8), ((878)::int8), ((879)::int8), ((880)::int8), ((881)::int8), ((882)::int8), ((883)::int8), ((884)::int8), ((885)::int8), ((886)::int8), ((887)::int8), ((888)::int8), ((889)::int8), ((890)::int8), ((891)::int8), ((892)::int8), ((893)::int8), ((894)::int8), ((895)::int8), ((896)::int8), ((897)::int8), ((898)::int8), ((899)::int8), ((900)::int8), ((901)::int8), ((902)::int8), ((903)::int8), ((904)::int8), ((905)::int8), ((906)::int8), ((907)::int8), ((908)::int8), ((909)::int8), ((910)::int8), ((911)::int8), ((912)::int8), ((913)::int8), ((914)::int8), ((915)::int8), ((916)::int8), ((917)::int8), ((918)::int8), ((919)::int8), ((920)::int8), ((921)::int8), ((922)::int8), ((923)::int8), ((924)::int8), ((925)::int8), ((926)::int8), ((927)::int8), ((928)::int8), ((929)::int8), ((930)::int8), ((931)::int8), ((932)::int8), ((933)::int8), ((934)::int8), ((935)::int8), ((936)::int8), ((937)::int8), ((938)::int8), ((939)::int8), ((940)::int8), ((941)::int8), ((942)::int8), ((943)::int8), ((944)::int8), ((945)::int8), ((946)::int8), ((947)::int8), ((948)::int8), ((949)::int8), ((950)::int8), ((951)::int8), ((952)::int8), ((953)::int8), ((954)::int8), ((955)::int8), ((956)::int8), ((957)::int8), ((958)::int8), ((959)::int8), ((960)::int8), ((961)::int8), ((962)::int8), ((963)::int8), ((964)::int8), ((965)::int8), ((966)::int8), ((967)::int8), ((968)::int8), ((969)::int8), ((970)::int8), ((971)::int8), ((972)::int8), ((973)::int8), ((974)::int8), ((975)::int8), ((976)::int8), ((977)::int8), ((978)::int8), ((979)::int8), ((980)::int8), ((981)::int8), ((982)::int8), ((983)::int8), ((984)::int8), ((985)::int8), ((986)::int8), ((987)::int8), ((988)::int8), ((989)::int8), ((990)::int8), ((991)::int8), ((992)::int8), ((993)::int8), ((994)::int8), ((995)::int8), ((996)::int8), ((997)::int8), ((998)::int8), ((999)::int8), ((1000)::int8), ((1001)::int8), ((1002)::int8), ((1003)::int8), ((1004)::int8), ((1005)::int8), ((1006)::int8), ((1007)::int8), ((1008)::int8), ((1009)::int8), ((1010)::int8), ((1011)::int8), ((1012)::int8), ((1013)::int8), ((1014)::int8), ((1015)::int8), ((1016)::int8), ((1017)::int8), ((1018)::int8), ((1019)::int8), ((1020)::int8), ((1021)::int8), ((1022)::int8), ((1023)::int8), ((1024)::int8), ((1025)::int8), ((1026)::int8), ((1027)::int8), ((1028)::int8), ((1029)::int8), ((1030)::int8), ((1031)::int8), ((1032)::int8), ((1033)::int8), ((1034)::int8), ((1035)::int8), ((1036)::int8), ((1037)::int8), ((1038)::int8), ((1039)::int8), ((1040)::int8), ((1041)::int8), ((1042)::int8), ((1043)::int8), ((1044)::int8), ((1045)::int8), ((1046)::int8), ((1047)::int8), ((1048)::int8), ((1049)::int8), ((1050)::int8), ((1051)::int8), ((1052)::int8), ((1053)::int8), ((1054)::int8), ((1055)::int8), ((1056)::int8), ((1057)::int8), ((1058)::int8), ((1059)::int8), ((1060)::int8), ((1061)::int8), ((1062)::int8), ((1063)::int8), ((1064)::int8), ((1065)::int8), ((1066)::int8), ((1067)::int8), ((1068)::int8), ((1069)::int8), ((1070)::int8), ((1071)::int8), ((1072)::int8), ((1073)::int8), ((1074)::int8), ((1075)::int8), ((1076)::int8), ((1077)::int8), ((1078)::int8), ((1079)::int8), ((1080)::int8), ((1081)::int8), ((1082)::int8), ((1083)::int8), ((1084)::int8), ((1085)::int8), ((1086)::int8), ((1087)::int8), ((1088)::int8), ((1089)::int8), ((1090)::int8), ((1091)::int8), ((1092)::int8), ((1093)::int8), ((1094)::int8), ((1095)::int8), ((1096)::int8), ((1097)::int8), ((1098)::int8), ((1099)::int8), ((1100)::int8), ((1101)::int8), ((1102)::int8), ((1103)::int8), ((1104)::int8), ((1105)::int8), ((1106)::int8), ((1107)::int8), ((1108)::int8), ((1109)::int8), ((1110)::int8), ((1111)::int8), ((1112)::int8), ((1113)::int8), ((1114)::int8), ((1115)::int8), ((1116)::int8), ((1117)::int8), ((1118)::int8), ((1119)::int8), ((1120)::int8), ((1121)::int8), ((1122)::int8), ((1123)::int8), ((1124)::int8), ((1125)::int8), ((1126)::int8), ((1127)::int8), ((1128)::int8), ((1129)::int8), ((1130)::int8), ((1131)::int8), ((1132)::int8), ((1133)::int8), ((1134)::int8), ((1135)::int8), ((1136)::int8), ((1137)::int8), ((1138)::int8), ((1139)::int8), ((1140)::int8), ((1141)::int8), ((1142)::int8), ((1143)::int8), ((1144)::int8), ((1145)::int8), ((1146)::int8), ((1147)::int8), ((1148)::int8), ((1149)::int8), ((1150)::int8), ((1151)::int8), ((1152)::int8), ((1153)::int8), ((1154)::int8), ((1155)::int8), ((1156)::int8), ((1157)::int8), ((1158)::int8), ((1159)::int8), ((1160)::int8), ((1161)::int8), ((1162)::int8), ((1163)::int8), ((1164)::int8), ((1165)::int8), ((1166)::int8), ((1167)::int8), ((1168)::int8), ((1169)::int8), ((1170)::int8), ((1171)::int8), ((1172)::int8), ((1173)::int8), ((1174)::int8), ((1175)::int8), ((1176)::int8), ((1177)::int8), ((1178)::int8), ((1179)::int8), ((1180)::int8), ((1181)::int8), ((1182)::int8), ((1183)::int8), ((1184)::int8), ((1185)::int8), ((1186)::int8), ((1187)::int8), ((1188)::int8), ((1189)::int8), ((1190)::int8), ((1191)::int8), ((1192)::int8), ((1193)::int8), ((1194)::int8), ((1195)::int8), ((1196)::int8), ((1197)::int8), ((1198)::int8), ((1199)::int8), ((1200)::int8)))))))
Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 30-Nov-2011 15:42:45   

ParameterisedPrefetchPathThreshold is set to 50 by default. So I wonder how did you get these 600 IN parameters in the query without changing its value.

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 01-Dec-2011 07:59:39   

I'm already manipulating the parameterisedPrefetchPathThreshold. In that case is set to 600. It's very important because filter is complicated and is enough efficient only in case it's run once. Running the filter in the subquery for all prefetch paths in this scenario would cause that a whole data load would take a few seconds. Because of the parameterisedPrefetchPathThreshold set to 600 it tooks from 40-300 ms.

The mentioned sql was generated from a prefetch path. There was some filter (I don't know how it looked, because it is dynamic and it wasn't logged by the sql - it was enough efficient) contents for which the subquery was generated.

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 01-Dec-2011 08:10:37   

Sorry i wasn't clear in the following sentence:

I'm already using the ParameterisedPrefetchPathThreshold to optimise some other queries, but in this case it's very hard to do that, because it's a web service and I don't know how many input parameters I'll receive.

I manually set the ParameterisedPrefetchPathThreshold to constant value for that query to 600. The filter created in that scenario is dynamically created based on the client application search criteria. The criteria is created by a human, so I don't know what a human wants in each case. The fields table can posses from few to thousands records per content. Thats why it's very hard to manipulate the ParameterisedPrefetchPathThreshold parameter dynamically. I don't know how many contents it'll find and how "big" they are. Of course I also don't know when in my scenario more efficient will be subquery. The filter parameters is inter alia based on full text search, that's why is so unpredictable.

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 01-Dec-2011 09:36:38   

So how do you like that SQL to be optimized?

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 01-Dec-2011 10:00:12   
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.

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 01-Dec-2011 11:12:13   

1- Which LLBLGen runtime library version (build number) are you using? 2- If you run the previous query in SQL once as is, and once after removing the excess part, would you see any performance difference?

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 01-Dec-2011 11:34:05   

1) Product version 3.0.10.0611 2) Unfortunateley yes. It's various: sometimes its e few ms, and sometimes about 500 ms. The mentioned query in the first post took about 1,2s. It should run in about 200 ms. The long run time is caused by a large rows amount. In case you won't generate a subquery it's only one index scan => select rows. In scenario with a subquery I have:

index by PK => select elements=>HashAggregate=>index scan=>Nested loop.

Query plan with a subquery

"Nested Loop  (cost=5054.56..110812.85 rows=32124 width=160) (actual time=1.634..10.275 rows=4863 loops=1)"
"  Buffers: shared hit=6554"
"  ->  HashAggregate  (cost=5054.56..5060.56 rows=600 width=8) (actual time=1.615..1.781 rows=600 loops=1)"
"        Buffers: shared hit=1825"
"        ->  Bitmap Heap Scan on main_contents  (cost=2443.64..5053.06 rows=600 width=8) (actual time=1.266..1.397 rows=600 loops=1)"
"              Recheck Cond: (id = ANY ('{/* numbers */}'::bigint[]))"
"              Buffers: shared hit=1825"
"              ->  Bitmap Index Scan on pk_main  (cost=0.00..2443.49 rows=600 width=0) (actual time=1.253..1.253 rows=600 loops=1)"
"                    Index Cond: (id = ANY ('{/* numbers */}'::bigint[]))"
"                    Buffers: shared hit=1804"
"  ->  Index Scan using idx_cont_fields_main_content_id on fields  (cost=0.00..175.58 rows=54 width=160) (actual time=0.005..0.011 rows=8 loops=600)"
"        Index Cond: (id_main_content = main_contents.id)"
"        Buffers: shared hit=4729"
"Total runtime: 10.544 ms"

Query plan without a subquery:

"Bitmap Heap Scan on fields  (cost=2787.26..126572.41 rows=31821 width=160) (actual time=3.532..9.297 rows=4863 loops=1)"
"  Recheck Cond: (id_main_content = ANY ('{/* numbers */}'::bigint[]))"
"  Buffers: shared hit=3547"
"  ->  Bitmap Index Scan on idx_cont_fields_main_content_id  (cost=0.00..2779.30 rows=31821 width=0) (actual time=3.111..3.111 rows=4863 loops=1)"
"        Index Cond: (id_main_content = ANY ('{/* numbers */}'::bigint[]))"
"        Buffers: shared hit=1818"
"Total runtime: 9.606 ms"

As you can see the cost is almost 2x bigger in case with additional subquery.

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 01-Dec-2011 11:37:09   

It affects almost all prefetch path in relation 1:n, having more then threshold value element count.

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 01-Dec-2011 12:12:25   

I think this issue was fixed 6 months ago. Could you please try the latest build of v.3.0. Or better the latest of v.3.1 (free upgrade).

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 01-Dec-2011 13:02:02   

Sorry - we have a strange air pressure today simple_smile

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.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 01-Dec-2011 18:20:33   

One last thing I think we forgot to ask you: the .net code that reproduce such query. The thing is that I can't reproduce it with a simple FieldCompareSetPredicate, so it must be an special scenario.

(Edit) Also provide any special information that could help us on reproduce this. (f.i.: inheritance involved, etc.)

David Elizondo | LLBLGen Support Team
miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 02-Dec-2011 07:20:17   

I'll try to move a problem it into the test environment and sent you.

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 02-Dec-2011 09:17:04   

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 smile - 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)))))
Attachments
Filename File size Added on Approval
createTestDB.sql 1,623 02-Dec-2011 09:17.17 Approved
Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 02-Dec-2011 10:49:17   

Which database are you using? Which version?

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 02-Dec-2011 10:51:10   

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. disappointed -- Otis

ok

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 03-Dec-2011 01:03:27   

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 confused

David Elizondo | LLBLGen Support Team
miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 05-Dec-2011 14:12:14   

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 rage . 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);
// .. 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.

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 confused

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.

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 05-Dec-2011 16:39:37   

Please correct me if I'm wrong.

What I understand is that you have a fetch that returns 600 or more records, where you use a complex filter.

Now you need to prefetch related entities, but you don't want to reuse the complex filter. So you expand the PrefetchPath-Threshold to 600 for example.

So you get a query to fetch related entities with 600 IN parameters. And this affects performance for sure. I won't recommend having more than 100 IN parameters.

The following was written in the docs:

You're adviced not to set the ParameterisedPrefetchPathThreshold to a value larger than 300 unless you've tested a larger value in practise and it made queries run faster. This to prevent you're using slower queries than necessary. You can set the value per call, and it affects all prefetch path related fetches done with the same DataAccessAdapter instance.

I don't see much we can do in this case, as both ways of using perfetchPaths aren't good for you.

So in your case I would recommend you change the way you fetch your entities. I don't recommend using prefetchPaths with many levels deep with many IN parameters.

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 06-Dec-2011 13:18:57   

No the 600 elements threshold is not an issue in this case. It takes a littlebit time to send such parameters, but it doesn't repeat the complicated query.

I'll change a little bit a test scenario and sent it in the next message.

miloszes
User
Posts: 222
Joined: 03-Apr-2007
# Posted on: 06-Dec-2011 14:55:30   

Ok. You must be rigth with avoid using in clausule with to many ids. I tried to exacly reporoduce the case scenario and in any test I didn't found that LLBLGen is converting the complicated query to in clause in such stupid way.

I refactored the code and found out, that client has also an opportunity to create the in(many ids) filter functionality and it seams that they ask for 600 elements. So that subquery wasn't generated by yours code, but mine. I created the mentioned scenario (in clause with many ids in that and it was the only way yours code generated the in clause. Of course in such situation its correct behavior because its filter created by the user not the prefetch path and you need to check whether ids contained in the filter are really stored in the db.

I was wrong, because I thought that during query optimization the complicated filter has been transformed in such way, which wasn't true.

Sorry for not understanding the real problem from my side.