Cached Queries

[angelxmoreno]

any plans or ideas of how to implement cached results, records or recordsets?

[gugglegum]

I think it's better to use third-party caching library that can cache objects.

[froschdesign]

@angelxmoreno
The Mapper class includes already an identity map:

https://github.com/atlasphp/Atlas.Mapper/blob/a674403aa696b70904cf55af71c8cec21c4a9d09/src/Mapper.php#L29

But the question is: why do you think a cache is needed?

[angelxmoreno]

@gugglegum I attempted to use symfony cache and added a trait on my MapperSelect exposing a fetchCachedRecordSet. I then went ahead and added a \Serializable implementation on my RecordSet in order for the cache engine to safely serialize the RecordSet object. I ran into many issues in implementing a way to create a RecordSet from an array of values. However i ran into issues when attempting to create the RecordSet when it contained associated data ( relationship data).

This is mainly the reason why I asked the question. To see if there is a known and working solution before I dug deeper.

@froschdesign ty for pointing that out. That seems to be memoizing the objects but what I am looking for is a solution for storing data for a few minutes.

Looking at the identityMap property does shed light on a few things given my previous failed implementation. Ty for highlighting this.

It would be great if we had some documentation on how to implement some type of cached queries implementation. If I am successful I can write something but that is going to take me a little time.

[froschdesign]

@angelxmoreno

...what I am looking for is a solution for storing data for a few minutes.

Why do you need this? Do you have performance problems or something else?

[pmjones]

@angelxmoreno I opine that the place for caching is in your domain layer repositories, not your persistence layer mappers. That is, cache your domain objects, not your persistence objects.

Now, having said that, I presume that your motivation for caching is a performance issue of some sort. Is that the case? If so, are you able to describe it here?

[angelxmoreno]

I am working on CLI scripts that generate reports based on a massive mysql db. The various reports paginate through a few million rows of data to generate these reports. I would like to increase the performance of the CLI scripts by caching frequently made sql calls.

[gugglegum]

@angelxmoreno I don't think you need to go this way. In pagination you're fetching different data every time. So if you will cache them, you will get performance only on the next report generation. But in this case your reports may be outdated. Also, take a notice that if data is changing while you paginate it, the data in pages can be shifted. So some records can be fetched twice or some missed. Usually, when you making a report, you don't need to fetch whole database into your script, you need to use some JOINS and GROUP BY clauses to allow MySQL to prepare needle data for you. And even if you relally need to fetch and iterate big amount of data in your script, it's better to not use pagination. Use yield* methods which do not load all results into PHP memory at once. Or even try to use unbuffered query (you may need 2 parallel MySQL connections to make additional queries while you iterate results from unbuffered query). This trick allow you to fetch from MySQL really huge amount of data with very low memory consumption in PHP. Also, optimize your queries, use "DESCRIBE SELECT" syntax to see which query conditions works without indexes ("using filesort"). Optimized big queries may work not very slower than cache.

[angelxmoreno]

ty