ORM is normally meant to mean a comprehensive strategy for treating
tables as objects, and to recast relational concepts in object oriented
terms.
The examples in the past few posts just ain't ORM, there is no
showdown. The examples are necessary, clever, and intelligent examples
of simple abstractions of the interface of PHP to a SQL database.
They don't display ORM, so they can't be a showdown between ORM and SQL.
A better title might be "Manual coding vs library routines."
Ben Sgro (ProjectSkyLine) wrote:
Hello,
INSERT INTO users
(first_name,last_name,organization,address1, ... 20 more fields
..., birthday, favorite_color)
VALUES
(?,?,?,?,... 20 more question marks ...,?,?)
",array($first_name,$last_name,$organization,$address1,... 20 more
fields...,$favorite_color,$birthday);
Why not just do:
SET ...
first_name = $firstname,
last_name = $lastname, ...
Which is *basically* the same as what your saying ORM is useful for in
this case...
- Ben
----- Original Message ----- From: "Paul Houle" <[EMAIL PROTECTED]>
To: "NYPHP Talk" <talk@lists.nyphp.org>
Sent: Saturday, September 15, 2007 12:50 PM
Subject: [nyphp-talk] ORM vs SQL: the ultimate showdown
I think of ORM as a partial solution to the problems of building
database-backed web apps. People call database applications "CRUD"
apps, short for
Create
Recall
Update
Delete
ORM is good for Create, and for many cases of Update and Delete.
It can be used to do Recall, but can be disastrously slow for
generating many kinds of reports.
Just the other day I was working with a Java Servlet-based
webapp. I entered a text string with a single quote, which caused
an error. The cause was code that would have looked like this in PHP:
$conn->Execute("INSERT INTO users (first_name,last_name,organization)
VALUES ('$first_name','$last_name','$organization'");
This would have been OK if they'd written something like
$first_name=addslashes($_POST["first_name"]);
$last_name=addslashes($_POST["last_name"]);
$organization=addslashes($_POST["organization"]);
But they didn't. Neither do most developers. Now, there are
tools that help with this: for instance, some database API's will
automatically quote things if you use placeholders. This style of
doing things has it's own problems: let's say the user table has a
lot of fields...
$conn->Execute("
INSERT INTO users
(first_name,last_name,organization,address1, ... 20 more fields
..., birthday, favorite_color)
VALUES
(?,?,?,?,... 20 more question marks ...,?,?)
",array($first_name,$last_name,$organization,$address1,... 20 more
fields...,$favorite_color,$birthday);
Notice the error? I didn't get the order of the fields quite
right , so the 'birthday' and 'favorite color' fields will get
scrambled. Imagine how hard it would have been to have noticed the
error in the middle of the field list! I recently caught this one in
a Cold Fusion application -- this kind of code gets hard to maintain.
Enter ORM. Syntax varies, but this kind of insert is simple and
maintainable with an ORM system:
$t=$db->users;
$r=$t->new_record();
$r->first_name=$_POST["first_name"];
$r->last_name=$_POST["last_name"];
$r->organization=$_POST["organization"];
...
$r->insert();
$user_id=$r->user_id;
The ORM system takes care of quotes and bad data for you. You're
not likely to introduce bugs when you add and remove fields, and
they'll be transparent if you do. If you want to make life really
easy, you might write a subroutine that uses the ORM's introspection
capability, so you can just write
populate_from_post($r,$_POST);
that scans through the database fields, and automatically assigns
post variables down to fields. Talk about zero maintainance!
-------
ORM and other SQL-phobic approaches can also get you into big
trouble. I once inherited a PHP app, using MS SQL server as the back
end that had an administrative interface that listed all of the users
in the system. It worked just fine in testing, but got REALLY slow
when a few hundred users joined the system... It could take more
than 100 seconds to produce the screen!
The problem was that the system did between 10 and 20 queries to
produce each output row. It first did a query that retrieved a few
hundred rows, and then it did more queries to get all the details.
Doing 5000 queries took a really long time.
We changed the application to use prepared statements and found
that the query took 10 seconds. This was acceptable, but still
pretty bad.
Although this system didn't use an ORM, the general approach was
that encouraged by most ORM systems -- I don't think any ORM system
would have been smart enough to consolidate the multiple queries into
a smaller number, because the individual-row queries were
implementing quite complicated business rules that involved counting
rows with certain attributes, preparing thresholds, etc.
An ORM system might or might not be smart enough to generate
prepared statements. The difference between a 'smart' and 'dumb' ORM
would be a factor of 10 in performance in this case.
Later on I wrote a 'pure SQL' query that calculated everything in
one complex query. It used a number of subselects... I thought it
was pretty straightforward, but many people aren't comfortable with
queries that are this complex. It did the job in 0.1 seconds!!
That's a factor of 100x better than I got from running separate
statements.
-----------------------
Similar issues turn up with UPDATEs and DELETEs. ORM is quite
efficient if you only want to update one or two records at a time,
but imagine you want to update 100,000 rows. (Turning off service
for people who didn't pay their bills, reset scores in an online
game to zero, ...) It can be thousands of times faster to do
something like:
UPDATE user SET score=0;
than to write some loop that runs hundreds of thousands of queries.
Once you add in the issues of concurrency and transactions, the
'pure SQL' solution looks a lot better -- it's automatically
protected by the transactional integrity of the database. Probably
80% of people working with an ORM system will forget to put the whole
thing in a transaction: so it won't be reliable. The 20% of the
people who do put it in a transaction will hold locks on the database
for hundreds or thousands of times longer than they need to... Which
slows down the site for everybody else.
--------------------------
The gold standard is use an ORM system that's tightly integrated
with your framework when it's appropriate: when you're manipulating
a few rows at a time. You'll get big gains in maintainability. When
you're updating 1000+ rows or generating complex reports, you need
different tools. I've built a few systems that integrate data grid
display on the client with a prebuilt set of data fields that can be
incorporated into the query, using subselects to 'join' data from
other tables -- this gets the silver... The gold medal would go to a
system that uses the database metadata from an ORM system to help you
build queries. Hook this up to an AJAX data grid, say the one from
ext, and you've got a rails killer...
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com www.andromeda-project.org
631-689-7200 Fax: 631-689-0527
cell: 631-379-0010
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
