view takes 20 times as long with annotations

[miguelcleon]

This view https://github.com/ODM2/ODM2-performance-optimization/blob/master/timeseriesresultvaluesextwannotations.sql

takes over 20 times longer then this view https://github.com/ODM2/ODM2-performance-optimization/blob/master/timeseriesresultvaluesext.sql

I'm trying to create a view for exporting data including time series result annotations for data quality but the view with annotations is taking over 20 times as long as the one without. Any suggestions on a better approach? @smrgeoinfo maybe?

The only thing I can think to do right now is not use the left joins and add time series values with annotations as a seperate file, but I don't really want to do that.

[miguelcleon]

Nevermind the long running view has a bug, I see it now. This is fixed now, for the one sample query I tried, the view with annotations only took a few dozen milliseconds longer then the other view.

[miguelcleon]

I'm thinking that I can further improve these views by making these materialized views.

[miguelcleon]

@emiliom @valentinedwv @horsburgh Do you guys think materialized views might be a good approach to denormalizing ODM2 for improved performance? One draw back is that the materialized views need to be recreated when there are changes to the underlaying data.

[miguelcleon]

I tried it out with the view in timeseriesresultvaluesextwannotations.sql a test query went from 3.4 seconds to 2.1 seconds so it does seem to help.

[emiliom]

@miguelcleon, I have some, but very limited experience with materialized views in postgresql, with ODM2. It was promising, but I don't remember doing any formal performance comparisons.

But more broadly, any and all optimization strategies are worth exploring, specially if they have limited side effects or are not hard to implement. Materialized views should be explored, I think. Of course, tools like odm2api probably can't take advantage of them directly if they operate on the ODM2 "raw" schema.

One caveat to keep in mind, possibly, is that it's a feature that may not exist in all 4 RDBMS we're trying to support with ODM2. I'm not sure they exist in MySQL (doubt it, but don't know), and I'd be surprised if they exist in SQLite. Then again, the topic of optimization is likely to come up to system-specific issues and solutions pretty quickly.

[valentinedwv]

We should just ponder a flattened table that contains the information we need.

…

On Wed, Feb 1, 2017 at 10:24 AM, Emilio Mayorga ***@***.***> wrote: @miguelcleon <https://github.com/miguelcleon>, I have some, but very limited experience with materialized views in postgresql, with ODM2. It was promising, but I don't remember doing any formal performance comparisons. But more broadly, any and all optimization strategies are worth exploring, specially if they have limited side effects or are not hard to implement. Materialized views should be explored, I think. Of course, tools like odm2api probably can't take advantage of them directly if they operate on the ODM2 "raw" schema. One caveat to keep in mind, possibly, is that it's a feature that may not exist in all 4 RDBMS we're trying to support with ODM2. I'm not sure they exist in MySQL (doubt it, but don't know), and I'd be surprised if they exist in SQLite. Then again, the topic of optimization is likely to come up to system-specific issues and solutions pretty quickly. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAz_SPr_O_bSPwaCuYh7R3HxniGDojqBks5rYM3EgaJpZM4LzIz4> .

[smrgeoinfo]

That’s an approach I’ve been trying. I’ve created views but haven’t done any testing to see if they help.

[miguelcleon]

@valentinedwv then it's a question how that table gets created, would odm2api create it? That is something that would be interesting to explore with SQLAlchemy, to see if it would work in all 4 RDBMS. Otherwise it seems like it would be a headache to come up with another standard way.

The materialized view is really just postgreSQL creating a a table from a view. So it's easy to create a flattened table once you know what your view should be. However, then the question of the other RDBMS' come into play as @emiliom suggests.

[miguelcleon]

Yeah, so this is two questions, What is the definition of the flattened tables? It would be best if we can all agree on this.

Two, how do you create the flattened tables? This is probably RDBMS dependent, they would probably just be views in SQLite, for example.

[emiliom]

I'm not sure how productive it is to have generic discussions about flattened tables or materialized views, in the abstract. Specific ones are probably more constructive. And for that, opening specific issues would be more helpful.

[miguelcleon]

Ok, I've added a new issue here #5
I've also posted an additional view and changed the file structure for this repo.