RedBean ORM performance [closed]

Question

I would like to know whether Redbean ORM can be used for performance oriented scenarios like social networking web apps or no and is it stable even if thousands of data are pulled by multiple users at same time. Also I'd like to know whether Redbean consumes more memory space.

Can anyone offer a comparison study of Doctrine-Propel-Redbean?

Jefe

Writing pure SQL in any scenario will give you much more optimal code. If you care about performance, then just drop the whole ORM-idea. – Braiding

@tereško if tis possible, can you give the pros and cons of orm with respect to pure sql according to your experience and also i will google the topic at same time. – Jefe

Answer 1

@tereško if tis possible, can you give the pros and cons of orm with respect to pure sql according to your experience and also i will google the topic at same time. – Jaison Justus

Well .. explaining this in 600 characters would be hard.

One thing I must clarify: this is about ORMs in PHP, though i am pretty sure it applies to some Ruby ORMs too and maybe others.

In brief, you should avoid them, but if you have to use an ORM, then you will be better of with Doctrine 2.x , it's the lesser evil. (Implements something similar to DataMapper instead of ActiveRecord).

Case against ORMs

The main reason why some developers like to use ORMs is also the worst thing about them: it is easy to do simple thing in ORM, with very minor performance costs. This is perfectly fine.

1. Exponential complexity

The problem originates in people to same tool for everything. If all you have is a hammer (..) type of issue. This results in creating a technical debt.

At first it is easy to write new DB related code. And maybe, because you have a large project, management in first weeks (because later it would case additional issues - read The Mythical Man-Month, if interested in details) decides to hire more people. And you end up preferring people with ORM skills over general SQL.

But, as project progresses, you will begin to use ORM for solving increasingly complex problems. You will start to hack around some limitations and eventually you may end up with problems which just cannot be solved even with all the ORM hacks you know ... and now you do not have the SQL experts, because you did not hire them.

Additionally most of popular ORMs are implementing ActiveRecord, which means that your application's business logic is directly coupled to ORM. And adding new features will take more and more time because of that coupling. And for the same reason, it is extremely hard to write good unit-tests for them.

2. Performance

I already mentioned that even simple uses of ORM (working with single table, no JOIN) have some performance costs. It is due to the fact that they use wildcard * for selecting data. When you need just the list of article IDs and titles, there is no point on fetching the content.

ORMs are really bad at working with multiple tables, when you need data based on multiple conditions. Consider the problem:

Database contains 4 tables: Projects, Presentations, Slides and Bulletpoints.

• Projects have many Presentations
• Presentations have many Slides
• Slides have many Bulletpoitns

And you need to find content from all the Bulletpoints in the Slides tagged as "important" from 4 latest Presentations related to the Projects with ids 2, 4 and 8.

This is a simple JOIN to write in pure SQL, but in any ORM implementation, that i have seen, this will result in 3-level nested loop, with queries at every level.

---

P.S. there are other reasons and side-effects, but they are relatively minor .. cannot remember any other important issues right now.

Photomicrograph

BTW : interesting article on the subject Object-Relational Mapping is the Vietnam of Computer Science – Braiding

@BTW also the nice article ORM is the Vietnam of CS :) +1 for you – Jefe

let me learn more about orm with your words and the other article in my mind.. thanks once more to both of my friends.. – Jefe

@JaisonJustus , BTW i an acronym for "by the way". – Braiding

hahaaa... :) i thought its @BTW... i was in hurry looking this post. – Jefe

I see your point but I don't think that is a reason not to use ORM. As you said, you just need to hire some SQL Experts and make sure to write complexe queries in pure SQL. ORM does not take SQL away from you. The problem is where to use ORM and where SQL but belive me, you will figure that out if your page gets slow. Still +1 for the nice answer! – Lakin

Well explained opinion, but I can't find the answer to the question here. – Portent

@AJweb , because it was intended as "long form" replay to the stuff in comments, and not really as answer to initial question. – Braiding

I know for a fact that the Kohana ORM can use JOINs for relationships using the 'with' chainable method: kohanaframework.org/3.2/guide/api/ORM#with – Triphthong

@PhillipWhelan , well .. Kohana's "ORM" is more like a glorified query builder then an ORM. When you have to make a complicated request , you end up 1:1 translating pure SQL to kORM-ized method calls, which turns it back into SQL. You end up just obscuring your query and, in the processes, loosing the control over the resulting SQL. – Braiding

@Photomicrograph Most ORMs include some form of query builder, doctrine even adds an extra layer to it with their DQL language. – Triphthong

ORM is good for rapid development. When your queries are complex write in in plain SQL. I mix both. – Girgenti

In doctrine you can prefetch related objects through joins. I'm sure other orms also have features like this. So there are no '3-level nested loop'. – Serrano

Answer 2

I feel Tereško's answer is not quite right.

Firstly it does not address the original question. It's indeed a case against ORMs, and I agree with the problems described in his answer. That's why I wrote RedBeanPHP. Just because most ORMs fail to make your life a bit easier does not mean the concept of an object relational mapping system is flawed. Most ORMs try to hide SQL, which is why JOINs get so complex; they need to re-invent something similar in an object oriented environment. This is where RedBeanPHP differs, as it does not hide SQL. It creates readable, valid SQL tables that are easy to query. Instead of a fabricated query language RedBeanPHP uses plain old SQL for record and bean retrieval. In short; RedBeanPHP works with SQL rather than against it. This makes it a lot less complex.

And yes, the performance of RedBeanPHP is good. How can I be so sure? Because unlike other ORMs, RedBeanPHP distinguishes between development mode and production mode. During the development cycle the database is fluid; you can add entries and they will be added dynamically. RedBeanPHP creates the columns, indexes, guesses the data types etc. It even stretches up columns if you need more bytes (higher data type) after a while. This makes RedBeanPHP extremely slow, but only during development time when speed should not be an issue. Once you are done developing you use freeze the database with a single mode specifier R::freeze() and no more checks are done. What you are left with is a pretty straight forward database layer on your production server. And because not much is done, performance is good.

Yes, I know, I am the author of RedBeanPHP so I am biased. However I felt like my ORM was being viewed in the same light as the other ORMs, which prompted me to write this. If you want to know more, feel free to consult the RedBeanPHP website, and here is a discussion on performance.

At our company we use RedBeanPHP for embedded systems as well as financial business systems, so it seems to scale rather well.

Together, me and the RedBeanPHP community are sincerely trying to make the ORM world a better place; you can read the mission statement here.

Good luck with your project and I hope you find the technical solution you are looking for.

Priest

RedBeanPHP is missing support for table prefixes, an ugly wart of a feature but widely used nonetheless. It also does not feel entirely integrated since the chainable queries do not directly return a list of ORM objects. You need to explicitly call R::convertToBeans for that. – Triphthong

@PhillipWhelan - I should think RedBean would welcome a feature request. You raised it on the bug tracker? :) – Amidst

@Amidst the issue has cropped up on the mailing list and has been decided against for performance reasons as well as to able to support some of the newer features in RedBeanPHP 3. While in theory I can agree with this the true nature of real life work doesn't always allow for this. – Triphthong

I would also add that the performance question is probably less one of the ORM code than of the underlying database size and query complexity. Especially regarding the latter, RB does sooner or later stop and you're left with manually crafted SQL queries- which are as good or bad as you write them. This is not a case against RB but for it's flexibility... – Wavelength

I just gave up doctrine2 thanks to its failure in executing complex query and begin trying out redbeanphp. So far what I like from redbean is how you don't have to drift away from SQL queries. What I don't like is its lack of table prefix support and naming in general - I prefer to write table name order_detail or OrderDetail rather than orderdetail, but maybe that's just me. – Melitta

Oh and redbeanphp's naming convention is superb! Doctrine forces you to map every single field of your tables and that just sucked. – Melitta

How do I use RedBeanPHP with Memcached? Thanks Gabor for creating RedBean! – Sudduth

Answer 3

I differ from @tereško here - ORMs can make database queries easier to write and easier to maintain. There is some great work going into Propel and Doctrine, in my opinion - take advantage of them! There are a number of performance comparisons on the web, and check out NotORM as well (I've not used it but they do some comparisons to Doctrine, if I recall correctly).

If you get to a point where your throughput requires you to do raw SQL then optimise at that point. But in terms of reducing your bug count and increasing your productivity, I think that your savings will fund a better server anyway. Of course, your mileage may vary.

I don't know RedBean, incidentally, but I am mildly of the view that Propel is faster than Doctrine in most cases, since the classes are pre-generated. I used Propel when it was the only option and have stuck with it, though I certainly wouldn't be averse to using Doctrine.

2018 update

Propel 2 is still in alpha after a number of years, and is in need of a number of large refactoring projects, which sadly were not getting done. Although the maintainers say that this alpha is good to use in production, since it has good test coverage, they have now started on Propel 3. Unfortunately, this has not actually had any releases yet, at the time of my writing this, despite the repository being a year old.

While I think Propel was a great project, I wonder if it is best to use something else for the time being. It could yet rise from the ashes!

Amidst

in case of pulling more than 1000 of records from db using orm does it consume more space? most probably i think the orm output will be in form of array of object. – Jefe

Generally speaking, you should be wary of holding large numbers of rows in memory regardless of your ORM. That number sounds fine for an offline process, but not for a web request. Instead, you should do it in pieces: do 20 selects of 50 elements each, say; that will keep memory usage down. Or, you could do this part with raw db calls (via an ORM to maintain db independence) if it's a small part of your application. Lastly (and best yet) see if you can move this thing to an offline process. – Amidst

hmmm nice spark. so you prefer to manage the data access from database in a managed way. and should be managed by us. – Jefe

I should have mentioned that an ORM will let you do things like nested sets really easily, or various behaviours (e.g. versionable, timestampable, archivable). Or, if you have a master/slave db config, some ORMs will send writes to one db and reads from several dbs using a round-robin scheme - just by setting a config value. – Amidst

have you tried using doctrine or propel? – Jefe

Yep, Propel. I am a fan! No Doctrine experience. – Amidst

can you share some tutorial and article on using and how to install it without using pear by using tar balls. also using propel – Jefe

Google "propel without pear"? Plenty of results, including one on my blog :). Edit: don't spend too much time deciding between the ORM systems. Choose one you like and run with it. Between your system now with zero users and your future Facebook-sized system, you will probably rewrite two or three times. Get it working first. – Amidst

why i am floating on deciding the orm, because i didn't find nice article on installing propel or doctrine without pear on mac i tried a lot but not succeeded. – Jefe

let us continue this discussion in chat – Amidst

Since my blog appears to have dropped out of Google (grr), the article I was referring to is here. It's for 1.5 but should work fine on 1.6. – Amidst

Answer 4

I would go with "Horses for Courses" situation that utilizes a mix and match of both the worlds. I have built few large scale applications using RedBean, so my comment will focus purely on RedBean and not on other ORMs.

IS RedBean ORM SLOW?

Well, it depends on how you use it. In certain scenarios, it's faster than traditional query because RedBean cache the result for few seconds. Reusing the query will produce result faster. Have a look at the log using R::debug(true); It always shows

"SELECT * FROM `table` -- keep-cache"

Scenario 1: Fetching All (*)

In RedBean if you query

$result = R::findOne('table', ' id = ?', array($id));

This is represented as

$result= mysql_query("Select * from TABLE where id =".$id);

You may argue that if the table is having multiple columns why should you query (*).

Scenario 2: Single column

Fetching a single column

R::getCol( 'SELECT first_name FROM accounts' );

Like i mentioned "Horses for Courses", developers should not simply rely on FindOne, FindAll, FindFirst, FindLast but also carefully draft what they really need.

Scenario 3: Caching

When you don't need caching, you can disable at application level which isn't an ideal situation

R::$writer->setUseCache(true);

RedBean suggests that if you don't want to disable caching at the application level you should use traditional query with no-cache parameter like $result = R::exec("SELECT SQL_NO_CACHE * FROM TABLE");

This perfectly solves the problem of fetching real-time data from table by completely discarding query cache.

Scenario 4: Rapid Development

Using ORM makes your application development really fast, developers can code using ORM 2-3x faster than writing SQL.

Scenario 5: Complex Queries & Relationships

RedBean presents a really nice way of implementing complex queries and one-to-many or many-to-many relationships

Plain SQL for complex queries

$books = R::getAll( 'SELECT 
    book.title AS title, 
    author.name AS author, 
    GROUP_CONCAT(category.name) AS categories FROM book
    JOIN author ON author.id = book.author_id
    LEFT JOIN book_category ON book_category.book_id = book.id
    LEFT JOIN category ON book_category.category_id = category.id 
    GROUP BY book.id
    ' );
    foreach( $books as $book ) {
        echo $book['title'];
        echo $book['author'];
        echo $book['categories'];
    }

OR RedBean way of handling many-t-to-many relationships

list($vase, $lamp) = R::dispense('product', 2);

$tag = R::dispense( 'tag' );
$tag->name = 'Art Deco';

//creates product_tag table!
$vase->sharedTagList[] = $tag;
$lamp->sharedTagList[] = $tag;
R::storeAll( [$vase, $lamp] );

Performance Issues

The arguments like ORMs are typically slow, consumes more memory and tends to make an application slow. I think they are not talking about RedBean.

We have tested it with MySQL and Postgres both, trust me performance was never a bottleneck.

There is no denying that ORMs adds up little overhead and tend to make your application slower ( just a little ). Using ORM is primarily a trade-off between developer time and slightly slower runtime performance. My strategy is to first build the application end-to-end with the ORM then based on test cases, tweak the speed critical modules to use straight data access.

Viticulture