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());Comments (33)
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
nice info. thanks for sharing
comment
thanks for sharing
LYgUdxyHUvGVgoS
ufODWz <a href="http://ndmrnsbqohdl.com/">ndmrnsbqohdl</a>, [url=http://xuzzrkdnasqu.com/]xuzzrkdnasqu[/url], [link=http://wiyeayrejnhg.com/]wiyeayrejnhg[/link], http://ugaupvpbiyek.com/
szNnFUbPuZUfDFK
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
Thats something i am still unable to understand probably..
Nice Post
Thanks for sharing .. m trying it..
Palamos
that would lead to having to j
Controversy Arose Pamela
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
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
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
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
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
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
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
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
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
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
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
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
Amazing
http://www.passforsure.co.uk/jn0-541.htm
Amazing
http://www.passforsure.co.uk/jn0-562.htm
LIke
http://www.passforsure.co.uk/1z0-043.htm
I like
abc520
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
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
<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
[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
<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
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
<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
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

Doctrine baby!
2007-10-23 10:48:34 by Lukas