PHP Database writing performance PDO VS ORM

Question

We need to insert records in mysql quite fast for the purpose of syncing it with an other database.

Which performs faster inserts. PHP PDO or some ORM (propel/doctrine)

Isn't the proper storage engine more important? – undefined Nov 29 '12 at 07:46 — undefined, Nov 29 '12 at 07:46

score 8 · Accepted Answer · answered Nov 29 '12 at 07:45

8

Think about it for a second.

An ORM will involve creating entities and then the code will look at the mappings to figure out how to change that into SQL, etc.

Using PDO is just straight SQL statements. You give it a string of SQL and it'll run that.

PDO wins.

answered Nov 29 '12 at 07:45

Jani Hartikainen

42,745
10
68
86

Jani Hartikainen, you are right, this is what comes to my mind. But when i read the advantages of ORM, several people write about faster performance, it can provide ease while dealing with database but how it can be faster then native insert queries. – user1551373 Nov 29 '12 at 07:50
@user1551373 Good ORMs will generate SQL which is fast. For example, if you have multiple inserts to the same table, they will generate a single insert with multiple rows of data instead of one insert per row. They will also run queries in transactions which can speed them up. However, this will not make it faster than running the same sort of queries by hand. The speed benefit with ORMs is that you don't need to think about how to optimize your queries to run fast, since the ORM is able to figure out the optimal way to write the query for you (but not always) – Jani Hartikainen Nov 29 '12 at 09:11
1

So what you suggest. I am writing a script which will run as corn job and will insert records in relational model (40M to 70M record in each table). Should I stick with PDO or use propel or doctrine? – user1551373 Nov 29 '12 at 09:17
1

@user1551373 definitely go with straight SQL. Although if possible, I would consider using the import tool `mysqlimport` (http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html) or the SQL query `LOAD DATA INFILE` (http://dev.mysql.com/doc/refman/5.1/en/load-data.html) – Jani Hartikainen Nov 29 '12 at 09:22
Jani Hartikainen, actually we cannot use mysqlimport. Because we need to read the couch db's changes api and based on document type it return we have to insert data in several mysql tables. Its a kind of migration from couch db to mysql – user1551373 Nov 29 '12 at 09:28
ORM, In addition to write Optimal Query also use IdMap Design Pattern to return data that it keeps in RAM without hitting the Database, if criteria to get record is Primary Key. Additionally, you can use Memcached with Doctrine which is a Distributed Cache which cache not only the results but also caches the DQL in its SQL counterpart such that it does not need to interpret SQL everytime you run same query. Doctrine is scalable with any database including Distributed Key-Value paired databases which are lightening-fast with Big Data. – Fakhar Anwar Jan 04 '18 at 18:14

score 0 · Answer 2 · edited Oct 26 '21 at 05:23

ORM provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.

PDO does not provide a database abstraction. which means it does rewrite SQL or emulate missing features.

You should use a Data abstraction layer if you need that facility. ORM gives that facility.

Note: when performance is the only criterion it's better to use raw SQL queries as more abstraction will relatively slower the app still it's negligible to differentiate but when we expect database abstraction in our application it's better to use ORM.

PHP Database writing performance PDO VS ORM

2 Answers2