quicker loading national?

[peterdudfield]

I noticed sometimes the national plot takes a long time to load.

I wonder if its possible to load the different components but make them not depend on each other. For example as soon as the PVLive data is loaded, it could show that?

is this possible, or / and how long would this refactoring take? @braddf @rachel-labri-tipton

[braddf]

This should actually factor into the loading states task! We can use these to "release" the components/inform them of what they should/shouldn't expect to have, rather than just "have data" or "don't have data"

[peterdudfield]

hmm, Are there two things here?

1. One speeding up individual tasks, having data or not
2. decouple some API queries, so that as soon as each one is ready, it shows up

[rachel-labri-tipton]

I definitely agree with @braddf. I agree with those two things, @peterdudfield. I might add a third (and maybe first thing but also maybe not necessary?) which could help with the other tasks here.

1. define in simple terms the functionalities of the chart and the different objects we work with that are coming from the database or being generated in the frontend.

2. One speeding up individual tasks, having data or not

3. decouple some API queries, so that as soon as each one is ready, it shows up

In terms of time, maybe a week or two?

I liked the spike task method and think it could be useful for looking at each of these things and then it'll take a bit more time to implement.

[peterdudfield]

Here's my quick spike of work for speeding up SQL queries, but getting less data.

Originally = 13 seconds Speed up = 1

Originally query

SELECT DISTINCT ON (location.gsp_id) * FROM forecast 
 JOIN forecast_value ON forecast.id = forecast_value.forecast_id 
 JOIN model ON model.id = forecast.model_id 
 JOIN location ON location.id = forecast.location_id 
 WHERE forecast.created_utc >= '2023-07-20 21:30'
 AND forecast.historic = false 
 AND forecast_value.target_time >= '2023-07-20 21:30'
 AND model.name = 'cnn'
 and gsp_id < 200
 ORDER BY location.gsp_id, forecast.created_utc DESC 

Only get data every 2 hours

SELECT DISTINCT ON (location.gsp_id) * FROM forecast 
 JOIN forecast_value ON forecast.id = forecast_value.forecast_id 
 JOIN model ON model.id = forecast.model_id 
 JOIN location ON location.id = forecast.location_id 
 WHERE forecast.created_utc >= '2023-07-20 21:30'
 AND forecast.historic = false 
 AND forecast_value.target_time >= '2023-07-20 21:30'
 and EXTRACT(MINUTE FROM forecast_value.target_time) = 0
 and EXTRACT(HOUR FROM forecast_value.target_time)% 2 = 0
 AND model.name = 'cnn'
 and gsp_id < 200
 ORDER BY location.gsp_id, forecast.created_utc DESC 

[peterdudfield]

speed up national loading from the api should be quicker with https://github.com/openclimatefix/uk-pv-forecast-blend/issues/1 too

[peterdudfield]

I'll close thise now as its loading around 1 second