Doctrine Optimization

Transcript

1. Doctrine Optimization php|tek, May 25, 2011
2. Anna Filina PHP Quebec - user group, organizer ConFoo - non for profit Web conference, organizer FooLab Inc. - IT solutions for businesses, founder I write code
3. Content Doctrine 1.2 optimization Profiling tools Find bad DQL Fix bad DQL Doctrine 2.0 Share your DQL concerns
4. Tools Used Doctrine 1.2 MySQL 5.1 PHP 5.3 Xdebug eZ Components (now Zeta Components)
5. Database Schema Invoice (2000 rows) Items (7000 rows) Tags (1-2 per item)
6. Let’s make it explode!
7. Two queries $query1 = Doctrine::getTable('Invoice') ->createQuery('inv') ->leftJoin('inv.Items.Tags') ->where('inv.number LIKE "%10"') ->orderBy('inv.subtotal DESC'); $query2 = Doctrine::getTable('Invoice') ->createQuery('inv') ->leftJoin('inv.Items.Tags');
8. Execution Time Average ezcDebug timed each query 3x Query 1: 0.20 sec Query 2: 16.26 sec What’s taking so long?
9. MySQL or PHP? Query took 0.002 sec in MySQL ~18,000 objects in PHP Doctrine hydrates results
10. Hydration Process Create objects Copy properties Reindex results Convert results to multi-dimensional arrays/ collections And lots of other good (and slow) things
11. Query Variations ->leftJoin('inv.Items'); ; ->leftJoin('inv.Items.Tags'); ->limit(25); No relations: 2.39 sec (~4x faster) Limit results: 0.21 sec (~77x faster) No tags: 9.21 sec
12. Doctrine_Pager $pager = new Doctrine_Pager( $query, $page, $pageSize ); $results = $pager->execute();
13. More Bad DQL Examples
14. Count $inv = Doctrine::getTable('Invoice') ->createQuery('inv') ->leftJoin('inv.Items') ->execute(); $inv[0]->Items->count();
15. Count ->leftJoin('inv.Items'); ->addSelect('(SELECT COUNT(*) FROM InvoiceItem item WHERE item.invoice_id = inv.id ) AS num_items') $inv[0]->num_items; Using left join: 8.91 sec Using subquery: 2.39 sec
16. Memory ->execute(); ->execute(Doctrine_Core::HYDRATE_ARRAY); Using default hydration: 10.24 MB Using HYDRATE_ARRAY: 0.64 MB, ~16x less
17. Lazy Loading $invoice->Items[0]->Tags[0]; ->leftJoin('inv.Items.Tags tag') Relations without leftJoin: 0.10 sec Relations with leftJoin: 0.02 sec, 5x faster Lazy loading especially bad when iterating
18. Lazy Loading $inv->Items[] = $item; $invoiceItem = new InvoiceItem(); $invoiceItem->invoice_id = $inv->id; $invoiceItem->item_id = $item->id; Using relation: 0.03 sec Using associative entity: 0.00065 sec (~46x faster)
19. Lessons Learned Limit results Select only what needed Use COUNT subqueries where appropriate Use ARRAY hydration when possible Avoid lazy-loading in most cases Also look into database optimization (index columns, de-normalization, storage engine, etc.) This also applies to Doctrine 2.0
20. Doctrine 2.0
21. Lighter and Faster 1 year of planning, 98% rewrite Independent model Faster hydration (3x) New lazy loading techniques Batch processing
22. Examples $iteratable = $query->iterate(); foreach ($iteratable as $item) {} Hydrate the results on-demand in an iteration COUNT(inv.Items) ... GROUP BY inv.Items COUNT relations $item1->persist(); $item2->persist(); $entityManager->flush(); Smart batch saving
23. Caching Layers Annotations to ClassMetadata DQL to SQL SQL to Collection
24. Resources Code used in this talk: http://annafilina.com/blog/wp-content/uploads/ doctrine-opt.zip Tips from the Doctrine team: http://www.doctrine-project.org/projects/orm/1.2/ docs/manual/improving-performance/en Derick Rethan’s “Profiling PHP Applications” Ligaya Turmelle’s “Optimizing MySQL Essentials”
25. Special thanks to Guilherme Blanco & Raphael Dohms