-2

I need to fetch huge data in my code. I am using zf2, currently I am using Doctrine2 to deal with my database. But I just found that ORM queries are taking much time than plain mysql queries. So could you please suggest me the best way to fetch huge data from database in zf2.

rekire
  • 47,260
  • 30
  • 167
  • 264
Deepanshu Goyal
  • 2,738
  • 3
  • 34
  • 61
  • You should also note what you are trying to do with that data – rekire Mar 12 '14 at 06:02
  • @rekire Well I am working on a site, in future it'll be having huge data and lot of joins on the tables. When I fetch the same at many pages of my site depending on conditions, I just found that ORM is slow in fetching data from plain sql. – Deepanshu Goyal Mar 12 '14 at 06:05
  • IMHO that description is too vague, however I would create views for that join stuff. So the ORM should get your data much faster. With views the DBMS can join that data more effective. – rekire Mar 12 '14 at 06:10
  • @rekire so you would prefer ORM over plain MySql Query ? But what if I dont want to create views. – Deepanshu Goyal Mar 12 '14 at 06:16
  • @rekire sorry I am unable to give clarity to my ques, I am just so amatuer at these database things – Deepanshu Goyal Mar 12 '14 at 06:18
  • As always it depends on your needs and I have no idea what you need so I write comments with hints. Objects can be more helpful based on the exact scenario I just mean that a view makes joins faster even for plain sql. I mean do you process that data or do you some kind of statistics? Do you need to display all data at once etc – rekire Mar 12 '14 at 06:20
  • @rekire sometimes I need to manipulate it and display it and some times display it at once – Deepanshu Goyal Mar 12 '14 at 06:25
  • Depending on the ORM and the pattern it implements. Using an ORM is just a convenient way to query DB, get object and work with your object model. When you do not know things about DB, how to optimize request etc..., an ORM can be faster, it has its own memory and resource management. On the other hand, you can plug your code on the PDO abstraction layer of the ORM, take advantage of configuration, and abstraction layer, and use plain SQL – SmasherHell Mar 12 '14 at 08:15
  • I can't imagine a site that requires so much data. If it's for a list of information, pagination would reduce 50,000 results to blocks of 100 results. The other option is to use the abstraction layer to fetch the results without ORM. (as @SmasherHell) mentioned. – lukeocodes Mar 12 '14 at 18:10

1 Answers1

8

There are a few things to consider here:

  1. By default, Doctrine will hydrate the database records into php objects (entities): it fills the entities with the data from your query. This is done by "smart guessing" so to speak, which is relatively slow. Hydrate to arrays, and you are getting a much faster response. Read more about hydration in the manual.
  2. By default, Doctrine does not use any caching to parse your mappings or when transforming the DQL into SQL. You can use caching to speed things up. Also, Doctrine is faster dealing with read-only entities than making them read/write. Read more about Doctrine performance issues in the manual.
  3. Rendering 50,000 rows in a html table (or whatever) is a pain for your render engine (Zend\View / php) and your DOM (in the browser). The query might be optimized and load fairly quickly, rendering all these results into a view and displaying them into a browser will be really slow as well.
  4. Using pagination will decrease the size of your dataset, speeding up the query and the rendering at the same time.
  5. Using an ORM to help you with mapping, database abstraction and so on is really nice, but comes with the trade-off: performance. Using a library like Doctrine inherently increases execution time: you will never reach the performance of the php mysql functions in Doctrine.

So:

  1. Try to decrease the number of results from one query, to speed up the database query, hydration and rendering
  2. Try tuning performance with Doctrine by using array hydration, caching and read-only entities.
  3. If you need to fastest way possible, don't use Doctrine. Try out Zend\Db or write all things in sql yourself
Jurian Sluiman
  • 13,498
  • 3
  • 67
  • 99