PHP Red Bean ORM Performance issue

Question

I am making a WebService in php which my website will use to query for information with Ajax calls.

At first i just did it the standard way with the built in php mysql lib, and wrote all the query's manually and made the whole data model in MySQL Workbench and so forth. This was VERY time consuming and if i had to change the data model afterwards everything would start to become very complicated so i decided to look for a PHP ORM, and i found RedBean which seems to me to be pure magic and joy.

Except i struggle very much with performance issues. My site is a site for users to create their own lists of tv series. i query an external source for a serie and insert it into my database if its not already there otherwise i get it from my own database ofc.

the xml i get back from this external source lists the serie, seasons, episodes and so on and i store it all like this.

function InsertSerie($serie) {
    $serieBean = $this->CreateSerieBean($serie->Series);
    $genreBeans = $this->CreateGenreBeans($serie->Series->Genre);
    $actorBeans = $this->CreateActorBeans($serie->Series->Actors);
    $episodeBeans = array();
    foreach ($serie->Episode as $episode) {
        $episodeBean = $this->CreateEpisodeBean($episode);
        $seasonBean = $this->CreateSeasonBean($episode);
        $writerBeans = $this->CreateWriterBeans($episode->Writer);
        $guestBeans = $this->CreateActorBeans($episode->GuestStars);
        $directorBeans = $this->CreateDirectorBeans($episode->Director);
        R::associate($episodeBean, $seasonBean);
        foreach ($writerBeans as $bean) {
            R::associate($episodeBean, $bean);
        }
        foreach ($guestBeans as $bean) {
            R::associate($episodeBean, $bean);
        }
        foreach ($directorBeans as $bean) {
            R::associate($episodeBean, $bean);
        }
        $episodeBeans[] = $episodeBean;
    }
    foreach ($genreBeans as $bean) {
        R::associate($serieBean, $bean);
    }
    foreach ($actorBeans as $bean) {
        R::associate($serieBean, $bean);
    }
    foreach ($episodeBeans as $bean) {
        R::associate($serieBean, $bean);
    }
}

function CreateGenreBeans($genres) {
    if(empty($genres)) { return; }
    $genre = explode("|", $genres);
$genreBeans = array();
foreach ($genre as $g) {
    if($g != '') {
            $genreBeans[] = $this->CreateGenreBean($g);
        }
    }
    return $genreBeans;
}

function CreateGenreBean($genre) {
    $bean = R::dispense('genre');
    $bean->name = (string)$genre;
    return $bean;
}

function CreateDirectorBeans($directors) {
    if(empty($directors)) { return; }
    $director = explode("|", $directors);
$directorBeans = array();
foreach ($director as $d) {
    if($d != '') {
            $directorBeans[] = $this->CreateDirectorBean($d);
        }
    }
    return $directorBeans;
}

function CreateDirectorBean($director) {
    $bean = R::dispense('director');
    $bean->name = (string)$director;
    return $bean;
}

function CreateActorBeans($actors) {
    if(empty($actors)) { return; }
    $actor = explode("|", $actors);
$actorBeans = array();
foreach ($actor as $a) {
    if($a != '') {
            $actorBeans[] = $this->CreateActorBean($a);
        }
    }
    return $actorBeans;
}

function CreateActorBean($actor) {
    $bean = R::dispense('actor');
    $bean->name = (string)$actor;
    return $bean;
}

function CreateWriterBeans($writers) {
    if(empty($writers)) { return; }
    $writer = explode("|", $writers);
$writerBeans = array();
foreach ($writer as $w) {
    if($w != '') {
            $writerBeans[] = $this->CreateWriterBean($w);
        }
    }
    return $writerBeans;
}

function CreateWriterBean($writer) {
    $bean = R::dispense('writer');
    $bean->name = (string)$writer;
    return $bean;
}

function CreateSerieBean($serie) {  
    $bean = R::dispense('serie');
    $bean->serie_id = (string)$serie->id;
    $bean->airs_day_of_week = (string)$serie->Airs_DayOfWeek;
    $bean->airs_time = (string)$serie->Airs_Time;
    $bean->content_rating = (string)$serie->ContentRating;
    $bean->first_aired = (string)$serie->FirstAired;
    $bean->imdb_id = (string)$serie->IMDB_ID;
    $bean->language = (string)$serie->Language;
    $bean->network = (string)$serie->Network;
    $bean->overview = (string)$serie->Overview;
    $bean->rating = (string)$serie->Rating;
    $bean->rating_count = (string)$serie->RatingCount;
    $bean->run_time = (string)$serie->Runtime;
    $bean->serie_name = (string)$serie->SeriesName;
    $bean->status = (string)$serie->Status;
    $bean->last_updated = (string)$serie->lastupdated;
    $bean->thumbnail = (string)$serie->thumbnail;
    return $bean;
}

function CreateSeasonBean($episode) {
    $bean = R::dispense('season');
    $bean->season_id = (string)$episode->seasonid;
    $bean->season_number = (string)$episode->SeasonNumber;
    return $bean;
}

function CreateEpisodeBean($episode) {
    $bean = R::dispense('episode');
    $bean->episode_id = (string)$episode->id;
    $bean->episode_name = (string)$episode->EpisodeName;
    $bean->episode_number = (string)$episode->EpisodeNumber;
    $bean->first_aired = (string)$episode->FirstAired;
    $bean->imdb_id = (string)$episode->IMDB_ID;
    $bean->language = (string)$episode->Language;
    $bean->overview = (string)$episode->Overview;
    $bean->rating = (string)$episode->Rating;
    $bean->rating_count = (string)$episode->RatingCount;
    $bean->last_updated = (string)$episode->lastupdated;
    return $bean;
}

The problem is it takes around 5 minutes to insert one serie and it inserts duplicates as well, doing R::freeze(); doesn't help performance either.

Q: How can i fix this issue, what can i do to make redbean perform better, what can i do with my own code to make it work better, or should i simply use a different solution/approach framework etc?

Tried shared lists like suggested but with the same result.

function InsertSerie($serie) {
    $serieBean = $this->CreateSerieBean($serie->Series);
    ...
    foreach ($serie->Episode as $episode) {
        $episodeBean = $this->CreateEpisodeBean($serieBean ,$episode);
        ...
        $this->CreateDirectorBeans($serieBean, $episode->Director);
        $serieBean->sharedEpisode[] = $episodeBean;
    }
    R::store($serieBean);
}

function CreateDirectorBeans($bean, $directors) {
    if(empty($directors)) { return; }
    $director = explode("|", $directors);
    foreach ($director as $d) {
        if($d != '') {
            $bean->sharedDirector[] = $this->CreateDirectorBean($d);
        }
    }
}

function CreateDirectorBean($director) {
    $bean = R::dispense('director');
    $bean->name = (string)$director;
    return $bean;
}
    ....

Capitulum

Solving performance issues starts with profiling. This is because it makes little sense to try to fix a problem, before you have identified what the problem is. You can use microtime() or a tool like xdebug. – Berretta

I don't know how to narrow the question more then this :/ i guess i'm using redbean the wrong way here doing allot of transactions instead of one big batch or something like that? – Capitulum

profiling means measure how long certain parts of your code take to execute. The purpose is to identify the slowest parts, as often there will be one thing in particular that stands out. Then, you can focus your efforts where you reap the most reward for your time. – Berretta

I personally never used redbean ORM, but it seems to me that every associate()-call will execute a sql-query. Isn't there a way to add all relationships at once? Profiling and checking what queries are run is a good way to start though. – Overreach

@Overreach seems so, i tried shared lists like suggested with just one big R::store with the same result tho. :/ – Capitulum

Answer 1

I finally found out how to lower the execution time from 5 minutes to about 11sec, still takes allot of time but considering the amount of data and work it has to do i think its good for that hardware.

I added these code lines

R::Begin();
R::associate($bean1, $bean2);
...
R::commit();

Now it gathers all the work and performs it in one big transaction like the unit of work pattern. Also to prevent inserting duplicates i switched to using

$bean = R::findOrDispense($type, $sql, $values);

Then i just return the bean if it already exists if not i create a new one and return it.

Capitulum

Answer 2

Have you tried using shared lists instead of looping through relations?

$serieBean->sharedActor[] = $actorBeans;
$serieBean->sharedEpisode[] = $episodeBeans;

http://www.redbeanphp.com/manual/shared_lists

Politico

only the serie table was created and with only serie data inserted, no other tables... – Capitulum

actually i made a mistake, i was to quick and didn't read that shared is a keyword and that the rest of the list has to match the bean type. Now it works just like previously, inserting duplicates and it takes like 5 minutes to insert a serie. The cpu and ram usage on the server seems very low but i can hear the disk work like hell, but don't think that's the issue here – Capitulum

I have read something about unit of work, which is a container for all your work and it does all the sql in one transaction. redbeanphp.com/community/wiki/index.php/Unit_of_Work But I can't find any code examples as how to obtain a $uow object which i can addWork(); too... – Capitulum