Blog

Eager fetching and SELECT N+1 problem

2007-10-22 23:33:57 by Martynas Jusevičius

After starting using an ORM and enjoying the benefits it brings, you might soon run into so-called SELECT N+1 problem if you don't use it properly, probably even without noticing it.

SELECT N+1 occurs when you retrieve a collection of objects from database via ORM, and then iterate it accessing the object properties of collection members. For example (we will use Propel):

$posts = PostPeer::doSelect(new Criteria());

foreach ($posts as $post)
{
	$author = $post->getAuthor();
	print $author->getName(); // do something with $author
}

Here we have Post and Author as classes, and each Post has it's own Author (this of course has to be specified as a foreign key in the database schema).
Now, when you access the author of every post in a loop, Propel loads it from the database with a separate SELECT query. And if there were N posts, you get a total of N such queries, plus 1 to load the posts in the first place — thus SELECT N+1.

While such code would work, it is far from optimal, since there is no need to SELECT from database in a loop to get authors of every post. Frequent database queries is one of the biggest hits on backend performance, and SELECT N+1 is causing it but often overlooked. If you are not sure if you have run into this problem, it is easiest to check your database query logs for excessive amounts of alike-looking SELECTs.

The problem is solved using eager fetching, which means that main and related objects are loaded in one go. In other words, when posts are loaded, author for each of them has to be instantiated at the same time, and not later when it is accessed.
The solution is implemented using JOINs and SELECTing data from related tables into one dataset. Processing it into objects (or “populating” objects, in Propel terms) needs little more memory and logic, but that is negligible compared to the overall performance gain. With Propel, it is achieved simply by specifying a different table-specific peer method for loading posts:

$posts = PostPeer::doSelectJoinAuthor(new Criteria());

Digg Digg this! del.icio.us del.icio.us!

Comments (33)

Doctrine baby!

2007-10-23 10:48:34 by Lukas

Well Doctrine supports with lazy and eager fetching, but it also supports another mode where you can solve the issue with 2 queries. Basically it allows you to load one property in a collection with a single query. This can be more efficient if you are fetching a lot of data from the parent table or if you need to load several properties (some of them optionally) that would lead to having to join a lot of tables in a single query with eager fetching (something where MySQL does not excell). Its also useful when you first filter/split the collection locally before fetching the property.

Fine more information here (the manual is pretty solid):
http://www.phpdoctrine.net/index.php/documentation/manual

comment

2009-06-23 14:33:43 by oxoniafamily.com

nice info. thanks for sharing

comment

2009-06-23 14:34:20 by oxoniafamily.com

thanks for sharing

LYgUdxyHUvGVgoS

2009-08-01 19:00:30 by reeqsgmj

ufODWz <a href="http://ndmrnsbqohdl.com/">ndmrnsbqohdl</a>, [url=http://xuzzrkdnasqu.com/]xuzzrkdnasqu[/url], [link=http://wiyeayrejnhg.com/]wiyeayrejnhg[/link], http://ugaupvpbiyek.com/

szNnFUbPuZUfDFK

2010-04-05 10:25:28 by pbsqqpsjcqx

pqgRYY <a href="http://mqjceezgvdkq.com/">mqjceezgvdkq</a>, [url=http://bvrxsuxpcsco.com/]bvrxsuxpcsco[/url], [link=http://gpyapiuwccjl.com/]gpyapiuwccjl[/link], http://yfowgkwbblxq.com/

Thanks alot

2010-04-19 21:00:46 by Play Game Poker

Thats something i am still unable to understand probably..

Nice Post

2010-04-19 21:02:12 by mervin

Thanks for sharing .. m trying it..

Palamos

2010-06-17 02:13:20 by شات مصرى

that would lead to having to j

Controversy Arose Pamela

2010-07-19 13:06:08 by links of london

Then a significant trend of making use of the exact same kind of bracelet like Sarah Jessica Parker was on its way.

Avrill Deliverd Divorce Files

2010-07-19 13:06:21 by tiffany jewelry

Everyone, mainly most women were hoping to find the bracelet and left for spending budget of Tiffany to request guidance.

Good Match For Pandora Necklaces

2010-07-19 13:06:46 by pandora bracelets

Also, some minimal (and popular with the young) things seems within the series for example the Tiffany bracelets. Certainly, Tiffany, as one of the top jewelries on earth would show out

some people

2010-07-19 13:07:18 by thomas sabo

Just for the common people, it is just a chance to get to know something about fashion and something about Cartier, Tiffany and so on.

Handbooks In Maintaining Jewelry New

2010-07-19 13:07:42 by tiffany jewellery

It is actually widely acknowledged that tiffany jewellery engagement rings could be the dreaming pieces of all. Therefore don't wait, go for one particular for your girls immediately!

Thank you.This

2010-08-05 05:14:21 by ed Hardy Clothing

I found this post while surfing the web for freebies.There seemed to me something cheap ed hardy alarming in such easy delights. In my ed hardy sale heart was desire to live more ed hardy clothing dangerously. I was not unprepared for jagged herve leger dress rocks and treacherous, shoals it I could only have change-change and the http://www.edhardy-buy.com/ exicitement of unforeseen.Thanks for sharing this article.

Great article

2010-08-05 05:21:01 by cheap Jordans Shoes

Thank you for your insight,the article was worth every minute reading it.However mean your authentic jordan shoes life is,meet it and live it,do not shun it and call it cheap jordans shoes hard names.It is not so bad as you cheap nike shox are.It looks poorest when you are cheap nike shoes richest.The fault-finder will find faults in http://www.nikejordanshoes2sell.com/ paradise.

Air Jordan shoes

2010-08-05 05:22:41 by Nike shoes

Welcome to buy and enjoy the greatest benefits.Only those who have the nike shoes patience to do simple things perfectly ever acquire the air jordan shoes skill to do difficult things easily.If you wish to succeed,you should use prada shoes persistence as your good friend,experience as your gucci Shoes reference,prudence as your brother and hope as your http://www.nikeaf1jordanshoes.com/ sentry.Very good collection.A new showcase for this site.

Air Jordan shoes

2010-08-05 05:23:45 by Nike shoes

Welcome to buy and enjoy the greatest benefits.Only those who have the nike shoes patience to do simple things perfectly ever acquire the air jordan shoes skill to do difficult things easily.If you wish to succeed,you should use prada shoes persistence as your good friend,experience as your gucci Shoes reference,prudence as your brother and hope as your http://www.nikeaf1jordanshoes.com/ sentry.Very good collection.A new showcase for this site.

New model

2010-08-05 05:28:58 by christian Louboutin UK

Great article very helpful.Human felicity is produced christian louboutin shoes not so much by great pieces of good tory borch shoes fortune that seldom happen,as by little advantages that occur christian louboutin pumps every day.Happiness lies not in the mere possession of money it lies in the christian louboutin sale joy of achievement , in the thrill of creative http://www.christianlouboutinshoestore.com/ effort.

New model

2010-08-05 05:29:09 by christian Louboutin UK

Great article very helpful.Human felicity is produced christian louboutin shoes not so much by great pieces of good tory borch shoes fortune that seldom happen,as by little advantages that occur christian louboutin pumps every day.Happiness lies not in the mere possession of money it lies in the christian louboutin sale joy of achievement , in the thrill of creative http://www.christianlouboutinshoestore.com/ effort.

New model

2010-08-05 05:29:18 by christian Louboutin UK

Great article very helpful.Human felicity is produced christian louboutin shoes not so much by great pieces of good tory borch shoes fortune that seldom happen,as by little advantages that occur christian louboutin pumps every day.Happiness lies not in the mere possession of money it lies in the christian louboutin sale joy of achievement , in the thrill of creative http://www.christianlouboutinshoestore.com/ effort.

http://www.passforsure.co.uk/jn0-532.htm

2010-08-06 09:05:40 by pass4sure JN0-532

Amazing

http://www.passforsure.co.uk/jn0-541.htm

2010-08-06 09:06:03 by pass4sure JN0-541

Amazing

http://www.passforsure.co.uk/jn0-562.htm

2010-08-06 09:06:22 by pass4sure JN0-562

LIke

http://www.passforsure.co.uk/1z0-043.htm

2010-08-06 09:06:46 by pass4sure 1z0-043

I like

abc520

2010-08-12 05:01:01 by watch.watcheslux

The Latest Jackie series, created by Frida Giannini, Innovative Director for Gucci, initial showed up in Gucci 2009 Spring and Summer fashion show. It can be the Guccis most classic design and style, the Jackie a magnificence of creativity. On the base in the Jackies great design and style as well

authentic jordans on sale

2010-08-12 06:30:27 by authentic jordan shoes

The Jordan shoes was inspired by the heritage of basketball and created for superior comfort
and style,All the products is in good quality with low price.you can go through
www.kicksbar.com to get more info.

Retail Jordan sneakers

2010-08-16 08:48:10 by gucci shoes wholesale 2010

<A href="http://www.cheapshoeschina.com/">china gucci shoes wholesale</A>
<A href="http://www.cheapshoeschina.com/">Wholesale puma shoes</A>
<A href="http://www.cheapshoeschina.com/">Discount shop D&G</A>
<A href="http://www.cheapshoeschina.com/">replica Air Max 2010/24-7</A>
<A href="http://www.cheapshoeschina.com/">Fashion gucci handbags</A>
<A href="http://www.cheapshoeschina.com/">Retail Jordan sneakers</A>
<A href="http://www.cheapshoeschina.com/">New sinful shirts</A>

Designer clothing by Gucci,louis vuittio

2010-08-18 06:51:07 by cheap gucci


[url=http://www.brandclothingstore.com/wholesale/Women-Chanel-Shoes-867_p1.html]Gucci shoes[/url]


[url=http://www.brandclothingstore.com/wholesale/Nike-Air-Max-24-7-Shoes-1278_p1.html]Nike Air Max 24/7 Shoe[/url]

[url=http://www.edhardyoutfits.com]gucci t-shirts chanel handbag[/url]


[url=http://www.edhardyoutfits.com/wholesale/Men-Shoes-7_p1.html]wholesale men shoes[/url]

[url=http://www.edhardyoutfits.com]fashion Authentic ed hardy cap[/url]

[url=http://www.edhardyoutfits.com]accept paypal men louis vuittion shoes[/url]

[url=http://www.edhardyoutfits.com]Gucci t-shirts history[/url]
[url=http://www.edhardyoutfits.com]ed hardy t-shirts docle gabbana shoes[/url]
[url=http://www.brandclothingstore.com]polo shoes history[/url]

[url=http://www.coogiwarehouse.com]Radii shoes history[/url]



[url=http://www.coogiwarehouse.com]gucci t-shirts[/url]

[url=http://www.coogiwarehouse.com]gucci t-shirts[/url]
[url=http://www.brandclothingstore.com]wholesale louis vuittion sneakers[/url]

[url=http://www.brandclothingstore.com]accept paypal nike air max 24-7 shoes[/url]

[url=http://www.brandclothingstore.com]Authentic coogi t-shirts[/url]

Designer clothing by Gucci,louis vuittio

2010-08-18 06:51:48 by cheap gucci clothes,Discount ed hardy,pr

<A href="http://www.brandclothingstore.com/">ed hardy t-shirts wholesale</A>
<A href="http://www.brandclothingstore.com/">louis vuittion sandals wholesale</A>
<A href="http://www.brandclothingstore.com/">armani t-shirts wholesale</A>
<A href="http://www.brandclothingstore.com/">gucci t-shirts wholesale</A>
<A href="http://www.brandclothingstore.com/">nike sneakers wholesale</A>
<A href="http://www.brandclothingstore.com/">true religion jeans wholesale</A>
<A href="http://www.brandclothingstore.com/">fendi shoes wholesale</A>
<A href="http://www.brandclothingstore.com/">christian Audigiers t-shirts wholesale</A>
<A href="http://www.brandclothingstore.com/">versace t-shirts wholesale</A>
<A href="http://www.brandclothingstore.com/">prada shoes wholesale</A>
http://www.brandclothingstore.com

accept paypal coogi clothing-Dolce gabba

2010-08-18 06:52:37 by Fashion shoes from china


<A href="http://www.edhardyoutfits.com/">wholesale gucci discount</A>
<A href="http://www.edhardyoutfits.com/">cheap prada shoes discount</A>
<A href="http://www.edhardyoutfits.com/">Authentic docle gabbana shoes discount</A>
<A href="http://www.edhardyoutfits.com/">replica versace shoes discount</A>
<A href="http://www.edhardyoutfits.com/">discount nike shoes discount</A>
<A href="http://www.edhardyoutfits.com/">on sale louis vuittion shoes discount</A>
<A href="http://www.edhardyoutfits.com/">sell cheap armani shoes discount</A>
<A href="http://www.edhardyoutfits.com/">accept paypal gucci shoes discount</A>
<A href="http://www.edhardyoutfits.com/">wholesale cheap fendi shoes discount</A>
<A href="http://www.edhardyoutfits.com/">replica versace shoes discount</A>
<A href="http://www.edhardyoutfits.com/">online wholesale hogan shoes discount</A>

ugg boots

2010-08-21 09:43:25 by ugg boots

i like<a href="http://www.buyuggsite.com ">ugg boots</a>,<a href="http://www.buyuggsite.com/ugg-argyle-knit-5879-boots-c-14.html ">uggs</a>,<a href="http://www.buyuggsite.com ">ugg australia</a>,<a href="http://www.buyuggsite.com ">ugg classic</a>,<a href="http://www.buyuggsite.com/ugg-argyle-knit-5879-boots-c-14.html ">ugg boots uk</a>,especlially like<a href="http://www.buyuggsite.com ">ugg classic tall</a><a href="http://www.buyuggsite.com/ugg-bailey-button-5803-boots-c-35.html ">tall ugg boots</a><a href="http://www.buyuggsite.com/ugg-bailey-button-5803-boots-c-35.html ">short ugg boots</a><a href="http://www.buyuggsite.com/ugg-classic-cardy-5819-boots-c-8.html ">uggs sale</a><a href="http://www.buyuggsite.com ">buy ugg</a><a href="http://www.buyuggsite.com/ugg-classic-cardy-5819-boots-c-8.html ">buy ugg boots</a><a href="http://www.buyuggsite.com/ugg-classic-crochet-5833-boots-c-1.html ">discount ugg</a><a href="http://www.buyuggsite.com/ugg-classic-crochet-5833-boots-c-1.html ">buy uggs</a><a href="http://www.buyuggsite.com/ugg-classic-mini-5854-boots-c-10.html ">ugg boots online</a><a href="http://www.buyuggsite.com/ugg-classic-mini-5854-boots-c-10.html ">discount uggs</a><a href="http://www.buyuggsite.com/ugg-classic-short-5825-boots-c-9.html ">buy cheap ugg boots</a><a href="http://www.buyuggsite.com/ugg-classic-short-5825-boots-c-9.html ">knitted ugg boots</a><a href="http://www.buyuggsite.com/ugg-classic-tall-5815-boots-c-7.html ">ugg boats australia</a> cheap<a href="http://www.buyuggsite.com/ugg-ultra-short-5225-boots-c-2.html ">ugg boats</a><a href="http://www.buyuggsite.com/ugg-nightfall-5359-boots-c-6.html ">buy ugg site</a><a href="http://www.buyuggsite.com/ugg-nightfall-5359-boots-c-6.html ">buy discount ugg boots</a><a href="http://www.buyuggsite.com/ugg-sundance-ii-5325-boots-c-5.html ">Ladies UGG Boots</a><a href="http://www.buyuggsite.com/ugg-sundance-ii-5325-boots-c-5.html ">UGG boots sale</a> ugg boats<a href="http://www.buyuggsite.com/ugg-classic-tall-5815-boots-c-7.html ">UGG Classic Tall 5815 Boots</a> very much.there are several styles:<a href="http://www.buyuggsite.com/ugg-argyle-knit-5879-boots-c-14.html ">ugg 5879</a>

http://www.caps-hot.com

2010-08-21 17:14:43 by hantian911

<a href=" http://www.caps-hot.com/"><strong>wholesale Arizona Diamondbacks cap</strong></a>
<a href=" http://www.caps-hot.com/"><strong>cheap Arizona Diamondbacks cap </strong></a>
<a href=" http://www.caps-hot.com/"><strong>replica Arizona Diamondbacks cap </strong></a>
<a href=" http://www.caps-hot.com/"><strong>fake Arizona Diamondbacks cap </strong></a>
<a href=" http://www.caps-hot.com/"><strong>discount Arizona Diamondbacks cap</strong></a>

http://www.caps-hot.com

2010-08-21 17:15:12 by hantian911

http://www.caps-hot.com wholesale brand caps, cheap new era caps,wholesale monster energy caps,ed hardy cap

wholesale Arizona Diamondbacks cap
cheap Arizona Diamondbacks cap
replica Arizona Diamondbacks cap
fake Arizona Diamondbacks cap
discount Arizona Diamondbacks cap

wholesale Atlanta Braves cap
cheap Atlanta Braves cap
replica Atlanta Braves cap
fake Atlanta Braves cap
discount Atlanta Braves cap

wholesale Baltimore Orioles cap
cheap Baltimore Orioles cap
replica Baltimore Orioles cap
fake Baltimore Orioles cap
discount Baltimore Orioles cap

wholesale Boston Red Sox cap
cheap Boston Red Sox cap
replica Boston Red Sox cap
fake Boston Red Sox cap
discount Boston Red Sox cap

wholesale Chicago Cubs cap
cheap Chicago Cubs cap
replica Chicago Cubs cap
fake Chicago Cubs cap
discount Chicago Cubs cap

wholesale Chicago White Sox cap
cheap Chicago White Sox cap
replica Chicago White Sox cap
fake Chicago White Sox cap
discount Chicago White Sox cap

wholesale Cleveland Indians cap
cheap Cleveland Indians cap
replica Cleveland Indians cap
fake Cleveland Indians cap
discount Cleveland Indians cap

wholesale Cincinnati Reds cap
cheap Cincinnati Reds cap
replica Cincinnati Reds cap
fake Cincinnati Reds cap
discount Cincinnati Reds cap

wholesale Colorado Rockies cap
cheap Colorado Rockies cap
replica Colorado Rockies cap
fake Colorado Rockies cap
discount Colorado Rockies cap

wholesale Detroit Tigers cap
cheap Detroit Tigers cap
replica Detroit Tigers cap
fake Detroit Tigers cap
discount Detroit Tigers cap

wholesale Florida Marlins cap
cheap Florida Marlins cap
replica Florida Marlins cap
fake Florida Marlins cap
discount Florida Marlins cap

wholesale Houston Astros cap
cheap Houston Astros cap
replica Houston Astros cap
fake Houston Astros cap
discount Houston Astros cap

wholesale Kansas City Royals cap
cheap Kansas City Royals cap
replica Kansas City Royals cap
fake Kansas City Royals cap
discount Kansas City Royals cap

wholesale Los Angeles Angels of Anaheim cap
cheap Los Angeles Angels of Anaheim cap
replica Los Angeles Angels of Anaheim cap
fake Los Angeles Angels of Anaheim cap
discount Los Angeles Angels of Anaheim cap

wholesale Los Angeles Dodgers cap
cheap Los Angeles Dodgers cap
replica Los Angeles Dodgers cap
fake Los Angeles Dodgers cap
discount Los Angeles Dodgers cap


wholesale Minnesota Twins cap
cheap Minnesota Twins cap
replica Minnesota Twins cap
fake Minnesota Twins cap
discount Minnesota Twins cap

wholesale Milwaukee Brewers cap
cheap Milwaukee Brewers cap
replica Milwaukee Brewers cap
fake Milwaukee Brewers cap
discount Milwaukee Brewers cap

wholesale New York Mets cap
cheap New York Mets cap
replica New York Mets cap
fake New York Mets cap
discount New York Mets cap

wholesale New York Yankees cap
cheap New York Yankees cap
replica New York Yankees cap
fake New York Yankees cap
discount New York Yankees cap

wholesale Oakland Athletics cap
cheap Oakland Athletics cap
replica Oakland Athletics cap
fake Oakland Athletics cap
discount Oakland Athletics cap

wholesale Philadelphia Phillies cap
cheap Philadelphia Phillies cap
replica Philadelphia Phillies cap
fake Philadelphia Phillies cap
discount Philadelphia Phillies cap

wholesale Pittsburgh Pirates cap
cheap Pittsburgh Pirates cap
replica Pittsburgh Pirates cap
fake Pittsburgh Pirates cap
discount Pittsburgh Pirates cap

wholesale San Diego Padres cap
cheap San Diego Padres cap
replica San Diego Padres cap
fake San Diego Padres cap
discount San Diego Padres cap

wholesale San Francisco Giants cap
cheap San Francisco Giants cap
replica San Francisco Giants cap
fake San Francisco Giants cap
discount San Francisco Giants cap

wholesale St. Louis Cardinals cap
cheap St. Louis Cardinals cap
replica St. Louis Cardinals cap
fake St. Louis Cardinals cap
discount St. Louis Cardinals cap

wholesale Seattle Mariners cap
cheap Seattle Mariners cap
replica Seattle Mariners cap
fake Seattle Mariners cap
discount Seattle Mariners cap

wholesale Tampa Bay Rays cap
cheap Tampa Bay Rays cap
replica Tampa Bay Rays cap
fake Tampa Bay Rays cap
discount Tampa Bay Rays cap

wholesale Toronto Blue Jays cap
cheap Toronto Blue Jays cap
replica Toronto Blue Jays cap
fake Toronto Blue Jays cap
discount Toronto Blue Jays cap

wholesale Texas Rangers cap
cheap Texas Rangers cap
replica Texas Rangers cap
fake Texas Rangers cap
discount Texas Rangers cap

wholesale Washington Nationals cap
cheap Washington Nationals cap
replica Washington Nationals cap
fake Washington Nationals cap
discount Washington Nationals cap

wholesale winter new era cap
cheap winter new era cap
replica winter new era cap
fake winter new era cap
discount winter new era cap

wholesale New Era Light Up cap
cheap New Era Light Up cap
replica New Era Light Up cap
fake New Era Light Up cap
discount New Era Light Up cap

http://www.caps-hot.com wholesale brand caps, cheap new era caps,wholesale monster energe caps,ed hardy cap

New comment






No HTML allowed.