Speed up get all forecasts

[peterdudfield]

Need to speed up
nowcasting_api |
{
"message": "Process Time 21.726077556610107 http://api-dev.nowcasting.io/v0/solar/GB/gsp/forecast/all/?historic=true",
"filename": "main.py",
"lineno": 193,
"level": "debug",
"timestamp": "2023-08-10T09:25:20.313845Z"
}

similar to #259

SQL

Select * FROM forecast 
JOIN forecast_value_latest ON forecast.id = forecast_value_latest.forecast_id 
JOIN model ON forecast_value_latest.model_id = model.id 
JOIN location ON location.id = forecast.location_id 
LEFT OUTER JOIN model AS model_1 ON model_1.id = forecast.model_id 
LEFT OUTER JOIN location AS location_1 ON location_1.id = forecast.location_id 
LEFT OUTER JOIN forecast_value AS forecast_value_1 ON forecast.id = forecast_value_1.forecast_id 
LEFT OUTER JOIN input_data_last_updated AS input_data_last_updated_1 ON input_data_last_updated_1.id = forecast.input_data_last_updated_id
WHERE location.gsp_id IN (%(gsp_id_1_1)s, %(gsp_id_1_2)s, %(gsp_id_1_3)s, %(gsp_id_1_4)s, %(gsp_id_1_5)s, %(gsp_id_1_6)s, %(gsp_id_1_7)s, %(gsp_id_1_8)s, %(gsp_id_1_9)s, %(gsp_id_1_10)s, %(gsp_id_1_11)s, %
....
gsp_id_1_308)s, %(gsp_id_1_309)s, %(gsp_id_1_310)s, %(gsp_id_1_311)s, %(gsp_id_1_312)s, %(gsp_id_1_313)s, %(gsp_id_1_314)s, %(gsp_id_1_315)s, %(gsp_id_1_316)s, %(gsp_id_1_317)s) AND forecast.historic = true AND forecast_value_latest.target_time >= %(target_time_1)s AND model.name = %(name_1)s ORDER BY location.gsp_id, forecast.created_utc DESC 

[peterdudfield]

Seems like the query takes

• 5 seconds to get the data from the database
• ?5 seconds to transfer the data?
• 5 change to Pydantic objects from orm and normalize
• 5 to deliver data from the API

[peterdudfield]

Some initial ideas might be to stream the data into maybe 10 gsp chunks

look at

• https://stribny.name/blog/2020/07/real-time-data-streaming-using-fastapi-and-websockets/
• https://stribny.name/blog/fastapi-video/ for reference

[peterdudfield]

I'm start to think the simpliest way would be to and start_gsp_id and end_gsp_id as optional variables in this route. Then the front end e.t.c can call what ever they want