FuelPHP Forums

kondiz

I'm developing app and I'm using the provided ORM functionality. The problem is with following tables:

Table: pdm_data

Massive table (350+ columns, many rows)
Table data is rather static (updates only once a day)
Primary key: obj_id

Table: change_request

Only few columns
Data changes often (10-20 times / min)
References primary key (obj_id from table pdm_data)

Users can customize datasheet that is visible to them, eg. they can save filters (eg. change_request.obj_id=34 AND pdm_data.state = 6) on columns which then are translated to query realtime with ORM. The querying with ORM is really slow as the table pdm_data is large. The largest problem seems to be in ORM: even if the query itself is relatively fast model hydration etc. takes many seconds. Ideal solution would be to cache results from pdm_data table as it is rather static.

Harro

Nobody's stopping you from using Cache to cache an Orm object?

But the problem may be in the way you access the data. With a filter like that, you get a subset of the entire one-2-many that exists between change_request and pdm_data, which is variable, and therefore difficult to cache.

I can't really understand why hydration would take seconds, unless you really fetch an enormous amount of records, for which Orm isn't designed. Perhaps you're better off with a normal DB query with a join?

kondiz

Hello Harro!

Thanks for the quick reply!

My query is:

$query = Model_Changerequest::query()->related('pdmdata');

foreach($filters as $filter)

{

$query->where($filter[0], $filter[1], $filter[2]); //Filter[0] can be pdmdata.column

}

Now, most of the delay comes from fetching every column from table pdmdata (3-4mb / query), and only few kbs from changerequest.

Custom query would solve the problem, but model objects are used widely afterwards.

Is there any way to create objects with pre-fetched relational data by using views?

Harro

The problem with so much data, is that there's hardly an alternative, because it would mean holding all that data in memory. This query (I hope it's an example!) pulls in all change requests, with all their related pdmdata.

Orm models support views without problems.

Alternative may be to run a standard DB query, and use as_object('Model_Something') to get the result returned as Orm model objects. Since there are no relations involved, in general they are a lot faster than Orm hydration.

kondiz

Thanks Harro,

I think I'll try running it as a standard query (or two, as I have to also get the related data from "pdmdata"-table). The downside is that if filter contains where-clauses targeting "pdmdata"-table, they have to be parsed separately and matched to "changerequest" data.

One last question: can SQL view be used with ORM, so that the result is array of objects with eager loaded objects.

Not real code but to demonstrate:

$crs = Model_Changerequest::query()->use_view('myAwesomeView')->get();

echo $crs[7]->pdmdata->obj_id //prefetched relation

Thank you for your advice!

Harro

No, it doesn't work that way.

From a peek in the code, it seams that the only support for views is to be able to swap a table for a view. So the result of the view will not be hydrated into related objects.

If you can work with a flattened result (like the plain result of a JOIN), then views might be useful.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion