Overblog
Edit post Follow this blog Administration + Create my blog
Website Development, App Development and Digital Marketing - AResourcepool Blog

Discover latest news, offers and update for AResourcepool. Keep updated with new Website and Mobile app Development Technologies

Common PHP Database Problem : The n+1 pattern

If you are working with large application in which the code first retrieves a list of entities… Just say, “customers”. So if you want to know the details of each customer from “customers”, then retrieves them one by one to get the details for each entity.

 

We call it the n+1 pattern… one query is used to retrieve the list of all the entities from database and after then one query for each of the n entities. Its ok when n=10, but what about when you have big database records suppose that n=100 or n=1000? Then the inefficiency really kicks in this scenario…

Following program is an example of such a schema.

 

Program 1. Schema.sql

 

DROP TABLE IF EXISTS authors;

CREATE TABLE authors (

  id MEDIUMINT NOT NULL AUTO_INCREMENT,

  name TEXT NOT NULL,

  PRIMARY KEY ( id )

);

 

DROP TABLE IF EXISTS books;

CREATE TABLE books (

  id MEDIUMINT NOT NULL AUTO_INCREMENT,

  author_id MEDIUMINT NOT NULL,

  name TEXT NOT NULL,

  PRIMARY KEY ( id )

);

 

INSERT INTO authors VALUES ( null, 'Jack Herrington' );

INSERT INTO authors VALUES ( null, 'Dave Thomas' );

 

INSERT INTO books VALUES ( null, 1, 'Code Generation in Action' );

INSERT INTO books VALUES ( null, 1, 'Podcasting Hacks' );

INSERT INTO books VALUES ( null, 1, 'PHP Hacks' );

INSERT INTO books VALUES ( null, 2, 'Pragmatic Programmer' );

INSERT INTO books VALUES ( null, 2, 'Ruby on Rails' );

INSERT INTO books VALUES ( null, 2, 'Programming Ruby' );

 

The above schema is solid and there is nothing wrong in it. But the problem is in the PHP code that accesses the database to find all the books of a specific author, as program shown below.

 

Program 2. Get.php

 

<?php

require_once('DB.php');

 

$dsn= 'mysql://root:password@localhost/good_books';

$db =& DB::Connect( $dsn, array() );

if (PEAR::isError($db)) { die($db->getMessage()); }

 

functionget_author_id( $name )

{

  global $db;

 

  $res = $db->query( "SELECT id FROM authors WHERE name=?",

    array( $name ) );

  $id = null;

  while( $res->fetchInto( $row ) ) { $id = $row[0]; }

  return $id;

}

 

functionget_books( $id )

{

  global $db;

 

  $res = $db->query( "SELECT id FROM books WHERE author_id=?",

    array( $id ) );

  $ids = array();

  while( $res->fetchInto( $row ) ) { $ids []= $row[0]; }

  return $ids;

}

 

functionget_book( $id )

{

  global $db;

 

  $res = $db->query( "SELECT * FROM books WHERE id=?", array( $id ) );

  while( $res->fetchInto( $row ) ) { return $row; }

  return null;

}

 

$author_id = get_author_id( 'Jack Herrington' );

$books = get_books( $author_id );

foreach( $books as $book_id ) {

  $book = get_book( $book_id );

  var_dump( $book );

}

?>

 

Just go through the above code at bottom… then you most likely will think that it’s perfect code… first get the author “Id” then get a list of books and after that get information about each book… it is looking clean code but it’s not efficient… Look at the code… we are running too many query to get one book information… that’s good coding practice.

Now the solution of the above problem is to have one function that performs one bulk query. See the PHP program given below.

 

Program 3. Get_good.php

 

<?php

require_once('DB.php');

 

$dsn= 'mysql://root:password@localhost/good_books';

$db =& DB::Connect( $dsn, array() );

if (PEAR::isError($db)) { die($db->getMessage()); }

 

functionget_books( $name )

{

  global $db;

 

  $res = $db->query(

    "SELECT books.* FROM authors,books WHERE

      books.author_id=authors.id AND authors.name=?",

      array( $name ) );

  $rows = array();

  while( $res->fetchInto( $row ) ) { $rows []= $row; }

  return $rows;

}

 

$books = get_books( 'Jack Herrington' );

var_dump( $books );

?>

 

Now the above problem in which we were running too many query to get one book information is now optimized.

More information visit us - https://aresourcepool.com/

                  

Share this post
Repost0
To be informed of the latest articles, subscribe:
Comment on this post

Deals, Discount Offer, Coupons, Online Shopping USA Deals360.us 06/10/2019 08:31

Get Deals, Discounts Offers in USA, Sale, Buy and Online Shopping with Cheap Price, Best Coupons, Promo Code and Special Offers in USA by Deals360.us. Deals360 offer all types of Deals, Discount Offer in United State.