kelatebaru

Queries are gaining more and more complexity, data is getting bigger and bigger. Most optimizations in database technology are done in the database server. This is an approach to optimize queries on the client side. With this ORM, queries ... don't select more data than needed contain less joins when data is expected to be consistent can be written manually in pure SQL are not written in a new query language We need a good API, so ... it should be easy to learn method names must be short and intuitive the goal is to map datasets and relations to objects the API should offer method chaining special features like auto-increments should be included the code should be small, no getters and setters the database schema is created before writing PHP code relationships should be defined in the database, not in the code we get low latencies combined with low memory usage To make things easier, we make some restrictions: only UTF-8 only MySQL/MariaDB (mysqli) only PHP 5.4.0+ only buffer...

Lessons learned: Prepared statements are 13 percent faster than normal statements with escaping Prepared statements are 8 percent faster than normal statements without escaping To get improvements, you need at least 10000 inserts for 1 statement Using insert...set is 0.5-1 percent faster than insert...values Here is the code: $db = new mysqli('127.0.0.1', 'root', '', 'test'); $db->query('create table if not exists prep (i1 int, i2 int, s1 varchar(255)) engine=myisam'); $db->query('truncate table prep'); $start = microtime(true); $stmt = $db->prepare('insert into prep (i1,i2,s1) values (?,?,?)'); $i=0; $j=0; $s=null; $stmt->bind_param('iis', $i, $j, $s); for ($i=0; $i<100000; $i++) { $j = $i*2; $s = 'hello world'.$i; $stmt->execute(); } echo 'prep values '.number_format(microtime(true)-$start, 2)."\n"; assert($db->query('select count(*) from prep')->fetch_ro...

ORM has attracted a lot of attention in the last years. So let's get a bit deeper into it. The biggest advantage of ORM is also the biggest disadvantage: queries are generated automatically queries can't be optimized queries select more data than needed, things get slower, more latency (some ORMs fetch all datasets of all relations of an object even though only 1 attribute is read) compiling queries from ORM code is slow (ORM compiler written in PHP) SQL is more powerful than ORM query languages database abstraction forbids vendor specific optimizations Other problems coming up with ORM compiling ORM logic from phpDoc instructions or XML files is slow, but can be cached ORM validates relations and field names outside the database, but can't keep relations consistent ORM libraries are often used in projects without making a benchmark before ORM libraries are often used because the documentation of the library says it is very fast ORM libraries are often used by default with...