tag:blogger.com,1999:blog-28215292.post6384665768274655714..comments2024-03-25T08:39:21.481+01:00Comments on DragonBe's PHP blog: Speeding up database calls with PDO and iteratorsM. van Damhttp://www.blogger.com/profile/12610668749799675317noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-28215292.post-5817600844315076982019-02-26T09:09:39.639+01:002019-02-26T09:09:39.639+01:00It is an informative post.It is an informative post<a href="www.onlinestutorial.com" rel="nofollow">.</a>Anonymoushttps://www.blogger.com/profile/14719889632848622919noreply@blogger.comtag:blogger.com,1999:blog-28215292.post-54119746276215073342018-02-12T20:36:20.178+01:002018-02-12T20:36:20.178+01:00The first example I posted was a "common"...The first example I posted was a "common" use case you can find on many blogs and web sites showcasing the usage of PDO.<br /><br />The reason I'm iterating over each record one by one is I can still process the data (often a lot faster) without filling up my memory space. PHP arrays are great for data sets under ~5000 entries (depending on the complexity of their structures), but once you go over that limit you inevitably run in situations that you're exhausting your memory or the processing of the data takes too much time.<br /><br />Using an Iterator allows me to let the DB do the thing it knows best: retrieving the data and offer it as a stream. With PHP we "Iterate" over that stream, one record at a time which gives us superb speeds and less memory consumption. When you're processing 500K records while 10K users are all requesting the same data sets, you know exactly what I mean. The approach I've given here is only for performance purposes.M. van Damhttps://www.blogger.com/profile/12610668749799675317noreply@blogger.comtag:blogger.com,1999:blog-28215292.post-5962498258813086282018-02-12T20:29:03.980+01:002018-02-12T20:29:03.980+01:00I looked at the comments on StackOverflow and I ag...I looked at the comments on StackOverflow and I agree partially with "Your Common Sense", but again that's looking at it from one point of view. His PDO resource is amazing and a good reference if you want to learn more about using PDO to the full extend.<br /><br />My use case was that I needed to process many records and I didn't wanted to clutter the memory by pulling all data into memory, therefor I switched over to an Iterator instead of using a Traversable return type. Secondly I needed to filter based on user interaction and didn't want to make a detour to the database again as the FilterIterator classes provide a powerful way of doing this to the same extend as PDO. The only benefit of filtering with PDO instead of DB is that in most engines the query is not cacheable if you add filters in your query. And since with an Iterator I'm looking at a single record at a time, I don't really care if it's the full query or not. The fact I can pull it straight out of the database cache is my little win.<br /><br />I don't think my article is a complete hoax as he describes it, but I do believe it leaves plenty of room for improvement as any piece of code is only 1% finished.M. van Damhttps://www.blogger.com/profile/12610668749799675317noreply@blogger.comtag:blogger.com,1999:blog-28215292.post-19405150231936514502017-12-02T16:14:32.001+01:002017-12-02T16:14:32.001+01:00The comparison is a bit weird, because in the firs...The comparison is a bit weird, because in the first example you're moving all columns into PHP, whereas in the second example you're iterating over them one by one.<br />Could you please also include the following use case in your comparison? (Sorry for the bad formatting...)<br /><br />$pdo = new \PDO(<br /> $config['db']['dsn'],<br /> $config['db']['username'],<br /> $config['db']['password']<br />);<br /><br />$sql = 'SELECT * FROM `gen_contact` ORDER BY `contact_modified` DESC';<br /><br />$stmt = $pdo->prepare($sql);<br />$stmt->execute();<br /><br />echo 'Getting the contacts that changed the last 3 months' . PHP_EOL;<br />while (($data = $stmt->fetch(\PDO::FETCH_OBJ))) {<br /> $dt = new \DateTime('2015-04-01 00:00:00');<br /> if ($dt->format('Y-m-d') . '00:00:00' < $row->contact_modified) {<br /> echo sprintf(<br /> '%s (%s)| modified %s',<br /> $row->contact_name,<br /> $row->contact_email,<br /> $row->contact_modified<br /> ) . PHP_EOL;<br /> }<br />}Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-28215292.post-90183502368561912862017-11-09T15:58:57.936+01:002017-11-09T15:58:57.936+01:00Reply to your article -> https://stackoverflow....Reply to your article -> https://stackoverflow.com/a/39410972/5441700Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-28215292.post-8241983234316640782017-08-12T10:10:24.214+02:002017-08-12T10:10:24.214+02:00hmmmmhmmmmJeCksOnhttps://www.blogger.com/profile/12087778649552689484noreply@blogger.comtag:blogger.com,1999:blog-28215292.post-24475053398112983642017-07-24T10:10:14.422+02:002017-07-24T10:10:14.422+02:00Hi - thanks for this. I would never have thought t...Hi - thanks for this. I would never have thought to try this method. However, there is a discussion of StackOverflow which suggests it is reinventing the wheel: <br />https://stackoverflow.com/questions/39410722/creating-pdo-iterator<br />Do have any thoughts on what is said there?<br />Thanks again.ChrisHugheshttps://www.blogger.com/profile/02932232257767131972noreply@blogger.com