Re: JPA n+1 problem
Michael Fortin <[email protected]> Fri, 11 May 2018 21:30:34 -0400
| Newsgroups | gmane.org.user-groups.ajug.members |
|---|---|
| Message-ID | <[email protected]> |
--===============2798898126095010329== Content-Type: multipart/alternative; boundary=Apple-Mail-C7F2B569-A166-4502-9B4B-6EFF0CBDECB6 Content-Transfer-Encoding: 7bit --Apple-Mail-C7F2B569-A166-4502-9B4B-6EFF0CBDECB6 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Like the other reply said it=E2=80=99s hard to answer without context. But t= here are two tools to solve that problem, =E2=80=98join fetch=E2=80=99 and j= pql projections. Googling that should give you some ideas. /Michael > On May 11, 2018, at 6:53 PM, Summers Pittman =E2=84=9D <[email protected]= m> wrote: >=20 > Without any context I I will give you some generic advice: >=20 > If you can eagerly fetch the objects giving you trouble, eagerly fetch the= m. Your JPA provider should be smart enough to batch them up and give you m= ore than one at a time. >=20 > If you are iterating over a bunch of objects to aggregate some data, I wou= ld just write a sql query to do that aggregation in the database and return t= he result. > If you are generating data for a report that is rendered to a PDF I would w= rite a bunch of SQL statements to remove iteration. >=20 > Have you used any profiling tools? Are they saying you need indexes in yo= ur database? If so I would make the suggestions they ask. >=20 > Congrats on getting far enough/having data big enough you get to think abo= ut optimization :) >=20 > Summers Pittman > >>Phone:404 941 4698 > >>Java is my crack. >=20 >=20 >=20 >> On Fri, May 11, 2018 at 1:31 PM Mike Barnes <mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]> wr= ote: >> I am using spring boot with JPA for an application that I am developing. I= have created all the entities and wired them in to the application through a= service layer. Now I need to resolve performance problems and have run int= o the famous N+1 problem. >>=20 >> After scouring the web for the last week, I am now confused as to the bes= t way to solve this problem. So, I am reaching out to this community to get a= ny suggests as to the best way to solve the problem. >>=20 >> Thanks >>=20 >> --=20 >> Michael D. Barnes >> Sr Software Engineer >>=20 >> Office: +1 404-960-9621 >> E-mail: mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected] >> Web: www.site1001.com >>=20 >> Follow Site 1001 on: LinkedIn | Instagram | Twitter | Facebook | YouTube >> 20772 Carmen Loop, Suite 130 >> Bend, OR 97702 >> _______________________________________________ >> ajug-members mailing list >> [email protected] >> Manage your subscription and unsubscribe http://lists.ajug.org/mailman/li= stinfo/ajug-members > _______________________________________________ > ajug-members mailing list > [email protected] > Manage your subscription and unsubscribe http://lists.ajug.org/mailman/lis= tinfo/ajug-members --Apple-Mail-C7F2B569-A166-4502-9B4B-6EFF0CBDECB6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D= utf-8"></head><body dir=3D"auto">Like the other reply said it=E2=80=99s hard= to answer without context. But there are two tools to solve that problem, =E2= =80=98join fetch=E2=80=99 and jpql projections. Googling that should g= ive you some ideas.<br><br><div id=3D"AppleMailSignature">/Michael</div><div= ><br>On May 11, 2018, at 6:53 PM, Summers Pittman =E2=84=9D <<a href=3D"m= ailto:[email protected]">[email protected]</a>> wrote:<br><br></div><= blockquote type=3D"cite"><div><div dir=3D"ltr"><div>Without any context I I w= ill give you some generic advice:</div><div><br></div><div>If you can eagerl= y fetch the objects giving you trouble, eagerly fetch them. Your JPA p= rovider should be smart enough to batch them up and give you more than one a= t a time.</div><div><br></div><div>If you are iterating over a bunch of obje= cts to aggregate some data, I would just write a sql query to do that aggreg= ation in the database and return the result.</div><div>If you are generating= data for a report that is rendered to a PDF I would write a bunch of SQL st= atements to remove iteration.</div><div><br></div><div>Have you used any pro= filing tools? Are they saying you need indexes in your database? = If so I would make the suggestions they ask.</div><div><br></div><div>Congr= ats on getting far enough/having data big enough you get to think about opti= mization :)</div><br clear=3D"all"><div><div dir=3D"ltr" class=3D"gmail_sign= ature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">Summers Pittman<b= r><span>>>Phone:<a>404 941 4698</a></span><br>>>Java is my crack= .<br><div><br></div></div></div></div><br></div><br><div class=3D"gmail_quot= e"><div dir=3D"ltr">On Fri, May 11, 2018 at 1:31 PM Mike Barnes <<a href=3D= "mailto:mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]">mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]</a>> wrote:<br= ></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-l= eft:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr">I am using spring boot= with JPA for an application that I am developing. I have created all the en= tities and wired them in to the application through a service layer. Now I n= eed to resolve performance problems and have run into the famous N+1 p= roblem.<div><br></div><div>After scouring the web for the last week, I am no= w confused as to the best way to solve this problem. So, I am reaching out t= o this community to get any suggests as to the best way to solve the problem= .</div><div><br></div><div>Thanks</div><div><div><br></div>-- <br><div class= =3D"m_20723782032308732gmail_signature" data-smartmail=3D"gmail_signature"><= div dir=3D"ltr"><p dir=3D"ltr" style=3D"line-height:1.38;margin-top:0pt;marg= in-bottom:0pt"><span style=3D"font-size:9.5pt;font-family:Verdana;color:#444= 444;background-color:#ffffff;font-weight:700;font-style:normal;font-variant:= normal;text-decoration:none;vertical-align:baseline">Michael D. Barnes<br></= span></p><p dir=3D"ltr" style=3D"line-height:1.38;margin-top:0pt;margin-bott= om:0pt"><span style=3D"font-size:9.5pt;font-family:Verdana;color:#444444;bac= kground-color:transparent;font-weight:400;font-style:normal;font-variant:nor= mal;text-decoration:none;vertical-align:baseline">Sr Software Engineer<br></= span></p><p dir=3D"ltr" style=3D"line-height:1.38;margin-top:0pt;margin-bott= om:0pt"><span style=3D"font-size:9.5pt;font-family:Verdana;color:#444444;bac= kground-color:transparent;font-weight:400;font-style:normal;font-variant:nor= mal;text-decoration:none;vertical-align:baseline"><img src=3D"https://lh4.go= ogleusercontent.com/Sp9xnvepxJBESkxLRN40IGyXFPHgUMicv8nVNkx8viNjvY7U7ITrO50H= yJSkzL-8Z1WjvjTrBm2sS6f-aiJw4Yh5EYTYJW3Cn41oKj5tYSERDj2Valjzuffry1aNsBvjbIdC= C4c4" style=3D"border:none" width=3D"146" height=3D"35"></span></p><p dir=3D= "ltr" style=3D"line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span styl= e=3D"font-size:9.5pt;font-family:Verdana;color:#444444;background-color:tran= sparent;font-weight:400;font-style:normal;font-variant:normal;text-decoratio= n:none;vertical-align:baseline">Office: +1 404-960-9621<br></span></p><p dir= =3D"ltr" style=3D"line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span s= tyle=3D"font-size:9.5pt;font-family:Verdana;color:#444444;background-color:t= ransparent;font-weight:400;font-style:normal;font-variant:normal;text-decora= tion:none;vertical-align:baseline">E-mail: </span><span style=3D"font-size:9= .5pt;font-family:Verdana;color:#1155cc;background-color:transparent;font-wei= ght:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-= align:baseline"><a href=3D"mailto:mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]" target=3D"_blank= ">mike.barnes-f9aWwpgOZj5Wk0Htik3J/[email protected]</a></span></p><p dir=3D"ltr" style=3D"line-height= :1.38;margin-top:0pt;margin-bottom:0pt"><span style=3D"font-size:9.5pt;font-= family:Verdana;color:#444444;background-color:transparent;font-weight:400;fo= nt-style:normal;font-variant:normal;text-decoration:none;vertical-align:base= line">Web: </span><a href=3D"http://www.site1001.com/" style=3D"text-decorat= ion:none" target=3D"_blank"><span style=3D"font-size:9.5pt;font-family:Verda= na;color:#1155cc;background-color:transparent;font-weight:400;font-style:nor= mal;font-variant:normal;text-decoration:underline;vertical-align:baseline">w= ww.site1001.com</span></a></p><br><p dir=3D"ltr" style=3D"line-height:1.38;m= argin-top:0pt;margin-bottom:0pt"><span style=3D"font-size:10pt;font-family:A= rial;color:#222222;background-color:transparent;font-weight:700;font-style:n= ormal;font-variant:normal;text-decoration:none;vertical-align:baseline">Foll= ow Site 1001 on: </span><a href=3D"https://linkedin.com/company/site1001" st= yle=3D"text-decoration:none" target=3D"_blank"><span style=3D"font-size:10pt= ;font-family:Arial;color:#1155cc;background-color:transparent;font-weight:70= 0;font-style:normal;font-variant:normal;text-decoration:underline;vertical-a= lign:baseline">LinkedIn</span></a><span style=3D"font-size:10pt;font-family:= Arial;color:#222222;background-color:transparent;font-weight:700;font-style:= normal;font-variant:normal;text-decoration:none;vertical-align:baseline"> | <= /span><a href=3D"http://instagram.com/site1001" style=3D"text-decoration:non= e" target=3D"_blank"><span style=3D"font-size:10pt;font-family:Arial;color:#= 1155cc;background-color:transparent;font-weight:700;font-style:normal;font-v= ariant:normal;text-decoration:underline;vertical-align:baseline">Instagram</= span></a><span style=3D"font-size:10pt;font-family:Arial;color:#222222;backg= round-color:transparent;font-weight:700;font-style:normal;font-variant:norma= l;text-decoration:none;vertical-align:baseline"> | </span><a href=3D"https:/= /twitter.com/site1001" style=3D"text-decoration:none" target=3D"_blank"><spa= n style=3D"font-size:10pt;font-family:Arial;color:#1155cc;background-color:t= ransparent;font-weight:700;font-style:normal;font-variant:normal;text-decora= tion:underline;vertical-align:baseline">Twitter</span></a><span style=3D"fon= t-size:10pt;font-family:Arial;color:#222222;background-color:transparent;fon= t-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vert= ical-align:baseline"> | </span><a href=3D"http://www.facebook.com/site1001" s= tyle=3D"text-decoration:none" target=3D"_blank"><span style=3D"font-size:10p= t;font-family:Arial;color:#1155cc;background-color:transparent;font-weight:7= 00;font-style:normal;font-variant:normal;text-decoration:underline;vertical-= align:baseline">Facebook</span></a><span style=3D"font-size:10pt;font-family= :Arial;color:#222222;background-color:transparent;font-weight:700;font-style= :normal;font-variant:normal;text-decoration:none;vertical-align:baseline"> |= </span><a href=3D"https://www.youtube.com/channel/UCmQBqlisUgdd0HjQRVFD6WQ"= style=3D"text-decoration:none" target=3D"_blank"><span style=3D"font-size:1= 0pt;font-family:Arial;color:#1155cc;background-color:transparent;font-weight= :700;font-style:normal;font-variant:normal;text-decoration:underline;vertica= l-align:baseline">YouTube</span></a></p><a href=3D"https://www.google.com/ma= ps/place/20772+Carmen+Loop,+Suite+130+Bend,+OR+97702" style=3D"text-decorati= on:none" target=3D"_blank"><span style=3D"font-size:9pt;font-family:Verdana;= color:#1155cc;background-color:transparent;font-weight:400;font-style:normal= ;font-variant:normal;text-decoration:underline;vertical-align:baseline">2077= 2 Carmen Loop, Suite 130</span></a><p dir=3D"ltr" style=3D"line-height:1.38;= margin-top:0pt;margin-bottom:0pt"><a href=3D"https://www.google.com/maps/pla= ce/20772+Carmen+Loop,+Suite+130+Bend,+OR+97702" style=3D"text-decoration:non= e" target=3D"_blank"><span style=3D"font-size:9pt;font-family:Verdana;color:= #1155cc;background-color:transparent;font-weight:400;font-style:normal;font-= variant:normal;text-decoration:underline;vertical-align:baseline">Bend, OR 9= 7702</span></a></p></div></div> </div></div> _______________________________________________<br> ajug-members mailing list<br> <a href=3D"mailto:[email protected]" target=3D"_blank">ajug-member= [email protected]</a><br> Manage your subscription and unsubscribe <a href=3D"http://lists.ajug.org/ma= ilman/listinfo/ajug-members" rel=3D"noreferrer" target=3D"_blank">http://lis= ts.ajug.org/mailman/listinfo/ajug-members</a></blockquote></div> </div></blockquote><blockquote type=3D"cite"><div><span>____________________= ___________________________</span><br><span>ajug-members mailing list</span>= <br><span><a href=3D"mailto:[email protected]">ajug-members@lists.= ajug.org</a></span><br><span>Manage your subscription and unsubscribe <a hre= f=3D"http://lists.ajug.org/mailman/listinfo/ajug-members">http://lists.ajug.= org/mailman/listinfo/ajug-members</a></span></div></blockquote></body></html= >= --Apple-Mail-C7F2B569-A166-4502-9B4B-6EFF0CBDECB6-- --===============2798898126095010329== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 Content-Disposition: inline X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KYWp1Zy1tZW1i ZXJzIG1haWxpbmcgbGlzdAphanVnLW1lbWJlcnNAbGlzdHMuYWp1Zy5vcmcKTWFuYWdlIHlvdXIg c3Vic2NyaXB0aW9uIGFuZCB1bnN1YnNjcmliZSBodHRwOi8vbGlzdHMuYWp1Zy5vcmcvbWFpbG1h bi9saXN0aW5mby9hanVnLW1lbWJlcnM= --===============2798898126095010329==--