[odb-users] 2.4.0: Object loading views with std::map container

[Quentin Deldycke]

Boris,

The purpose of this is a compatibility layer with an old application.
Where i don't want to enter the high-level code. I want only to operate
at low level.

Mainly, the old system disable:

   - optimized views
   - lazy loading

For new code, we use lazy loading.
With it we can achieve really good performance also.

With the old code, for one object, we had ~15 SELECT queries
(at each iteration of loop) + 2 SELECT for maps
for object under it (1-1 relations). This switch from 16 queries to 1
(+ 2 at iteration for maps) give us this performance bump.

I wanted to see if to continue this and switch to 1 unique SELECT
would be better for my usage.


I am implementing this evolution to my solution, (with maps still
loading at iteration).
I will send you the performance improvement across all system.
We have some treatments > 1h where we would be happy to see
such improvements.
Then i will see how i can impement the maps improvement. As i am
not sure of the improvement it will be.



--
Deldycke Quentin


On 17 February 2015 at 16:35, Boris Kolpackov <boris at codesynthesis.com>
wrote:

> Hi Quentin,
>
> Quentin Deldycke <quentindeldycke at gmail.com> writes:
>
> > I don't understand how to create the view for an object having a map on
> it.
> > I have a structure like this:
> >
> > class B
> > {
> > int x;
> > int y;
> > int z;
> > }
> >
> > class A
> > {
> > int a;
> > int b;
> > int c;
> > std::map<int, B> example;
> > }
>
> What's the purpose of the view you are trying to create? If it is
> to efficiently loading an object with a map (or any other container),
> then there is not much you can do. Containers are "heavy".
>
> If you try to load it with a single SELECT statement, then, for each
> A object, you will get a row for each B object that it contains in
> a map. All the A data will be simply duplicated. Probably not very
> efficient.
>
> If you don't do it with a single SELECT, then you will have to execute
> a separate SELECT for the container and then one to load each B object
> that it points to.
>
> I think the most efficient way to load a large number of A objects
> will be as follows:
>
> 1. First load all the B objects that the A objects will point to into
>    the cache.
>
> 2. Then load all the A objects you are interested in.
>
> So, something like this:
>
> #pragma db view object(B) object(A inner) query(distinct)
> struct B_for_A
> {
>   shared_ptr<B> b;
> };
>
> session s;
> for (auto& b: db.query<B_for_A> (odb::query<B_for_A>::A::a < 10))
>   ; // All we need is to get B's into the cache.
>
> for (auto& a: db.query<A> (odb::query<A>::a < 10))
> {
>   // All the B's come from the cache.
> }
>
>
> > By the way, using this feature, to dump (threw network) the database, we
> > manage to be 300% faster. And i think we can reach even more increase
> > because there is still 2 queries for ~10 B class for each A class which
> are
> > run at each iteration.
>
> That's an impressive speedup. Let me know how the above approach compares
> to a straight load, I am very interested.
>
> Boris
>