search

ODOT-PTS / TNExT

a web-based software tool developed for the visualization, analysis, and reporting of regional and statewide transit networks in the state of Oregon
16 stars 4 forks source link

Title VI/Employment Time-out #140

Closed PPaulsonOregonDOT closed 6 years ago

PPaulsonOregonDOT commented 6 years ago

In both the Oregon DOT environment and outside of it, it appears that the Title VI report: https://oregon-staging.tnext.io/TNAtoolAPI-Webapp/Emp.html?&n=fAs&dbindex=12 and the employment report at https://oregon-staging.tnext.io/TNAtoolAPI-Webapp/Emp.html?&n=fAs&dbindex=12 time out and do not complete their respective reports.

PPaulsonOregonDOT commented 6 years ago

We need to add the flexible reporting report at https://oregon-staging.tnext.io/TNAtoolAPI-Webapp/FlexRep.html to the list of reports that is failing to complete, at least in the ODOT environment.

PPaulsonOregonDOT commented 6 years ago

In the last week, the Title VI report has been completing within a minute or two. In doing further investigation, the employment report for the September 2017 database completes in a similar amount of time from both instances of the tool: https://oregon.tnext.io/TNAtoolAPI-Webapp/Emp.html?&n=gjs&dbindex=10 https://oregon-staging.tnext.io/TNAtoolAPI-Webapp/Emp.html?&n=gjs&dbindex=10

but does not complete when May 2018 is selected: https://oregon-staging.tnext.io/TNAtoolAPI-Webapp/Emp.html?&n=gjs&dbindex=12 https://oregon.tnext.io/TNAtoolAPI-Webapp/Emp.html?&n=gjs&dbindex=12&dataSet=lodes_blocks_wac&reportType=Counties&projectionYear=current&Sradius=0.25&LOS=2

Looking at the difference in the paths for the two reports that do not complete, is it possible that the report is trying to call a broken url?

ODOT-RPTD-mb commented 6 years ago

10/10/2018 oregon-staging.tnext.io; Sept 2018 db; title VI report is completing, employment report not completing.

irees commented 6 years ago

It looks like the Title VI report timeouts were related to the proxy configuration problem fixed in #144. The Employment report timeouts are in the database query itself, which pegs the database cpu at 100% and did not complete after an hour. I have the query copied out and debugging now.

with aids as (SELECT DISTINCT a.defaultid AS aid FROM gtfs_agencies AS a LEFT OUTER JOIN user_selected_agencies AS b ON (b.username = 'D646E30B229C724035F8E568906777B3' AND a.defaultid = b.agency_id) WHERE b.hidden IS NOT true), svcids AS ((select serviceid_agencyid, serviceid_id, 'Wed 27 Jun 2018' as day from gtfs_calendars gc inner join aids on gc.serviceid_agencyid = aids.aid where startdate::int<=20180627 and enddate::int>=20180627 and wednesday = 1 and serviceid_agencyid||serviceid_id not in (select serviceid_agencyid||serviceid_id from gtfs_calendar_dates where date='20180627' and exceptiontype=2) union select serviceid_agencyid, serviceid_id, 'Wed 27 Jun 2018' from gtfs_calendar_dates gcd inner join aids on gcd.serviceid_agencyid = aids.aid where date='20180627' and exceptiontype=1)), trips as (select trip.agencyid as aid, trip.id as tripid, trip.route_id as routeid, round((map.length)::numeric,2) as length, map.tlength as tlength, map.stopscount as stops    from svcids inner join gtfs_trips trip using(serviceid_agencyid, serviceid_id)  inner join census_counties_trip_map map on trip.id = map.tripid and trip.agencyid = map.agencyid), service as (select COALESCE(+ sum(length),0) as svcmiles, COALESCE(+ sum(tlength),0) as svchours, COALESCE(+ sum(stops),0) as svcstops from trips), stopsatlos as (select stime.stop_agencyid as aid, stime.stop_id as stopid, stop.location as location, count(trips.aid) as service    from gtfs_stops stop inner join gtfs_stop_times stime on stime.stop_agencyid = stop.agencyid and stime.stop_id = stop.id    inner join trips on stime.trip_agencyid =trips.aid and stime.trip_id=trips.tripid group by stime.stop_agencyid, stime.stop_id, stop.location having count(trips.aid)>=2), stops as (select stime.stop_agencyid as aid, stime.stop_id as stopid, stop.location as location, min(stime.arrivaltime) as arrival, max(stime.departuretime) as departure, count(trips.aid) as service    from gtfs_stops stop inner join gtfs_stop_times stime on stime.stop_agencyid = stop.agencyid and stime.stop_id = stop.id    inner join trips on stime.trip_agencyid =trips.aid and stime.trip_id=trips.tripid where stime.arrivaltime>0 and stime.departuretime>0   group by stime.stop_agencyid, stime.stop_id, stop.location), popatlos as (select    c000,   ca01,   ca02,   ca03,   ce01,   ce02,   ce03,   cns01,  cns02,  cns03,  cns04,  cns05,  cns06,  cns07,  cns08,  cns09,  cns10,  cns11,  cns12,  cns13,  cns14,  cns15,  cns16,  cns17,  cns18,  cns19,  cns20,  cr01,   cr02,   cr03,   cr04,   cr05,   cr07,   ct01,   ct02,   cd01,   cd02,   cd03,   cd04,   cs01,   cs02,lodes_blocks_wac.blockid, blocks.urbanid, blocks.regionid, blocks.congdistid, blocks.placeid   from lodes_blocks_wac inner join stopsatlos on st_dwithin(lodes_blocks_wac.location,stopsatlos.location,402.335)    inner join census_blocks blocks ON lodes_blocks_wac.blockid = blocks.blockid GROUP BY lodes_blocks_wac.blockid, blocks.urbanid, blocks.regionid, blocks.congdistid, blocks.placeid), popatlos1 as (select sum(c000) AS c000los, sum(ca01) AS ca01los, sum(ca02) AS ca02los, sum(ca03) AS ca03los, sum(ce01) AS ce01los, sum(ce02) AS ce02los, sum(ce03) AS ce03los, sum(cns01) AS cns01los, sum(cns02) AS cns02los, sum(cns03) AS cns03los, sum(cns04) AS cns04los, sum(cns05) AS cns05los, sum(cns06) AS cns06los, sum(cns07) AS cns07los, sum(cns08) AS cns08los, sum(cns09) AS cns09los, sum(cns10) AS cns10los, sum(cns11) AS cns11los, sum(cns12) AS cns12los, sum(cns13) AS cns13los, sum(cns14) AS cns14los, sum(cns15) AS cns15los, sum(cns16) AS cns16los, sum(cns17) AS cns17los, sum(cns18) AS cns18los, sum(cns19) AS cns19los, sum(cns20) AS cns20los, sum(cr01) AS cr01los, sum(cr02) AS cr02los, sum(cr03) AS cr03los, sum(cr04) AS cr04los, sum(cr05) AS cr05los, sum(cr07) AS cr07los, sum(ct01) AS ct01los, sum(ct02) AS ct02los, sum(cd01) AS cd01los, sum(cd02) AS cd02los, sum(cd03) AS cd03los, sum(cd04) AS cd04los, sum(cs01) AS cs01los, sum(cs02) AS cs02los, LEFT(blockid,5) AS countyid FROM popatlos GROUP BY LEFT(blockid,5)), popserved as (select  c000*(stops.service) as c000served,  ca01*(stops.service) as ca01served,  ca02*(stops.service) as ca02served,  ca03*(stops.service) as ca03served,  ce01*(stops.service) as ce01served,  ce02*(stops.service) as ce02served,  ce03*(stops.service) as ce03served,  cns01*(stops.service) as cns01served,  cns02*(stops.service) as cns02served,  cns03*(stops.service) as cns03served,  cns04*(stops.service) as cns04served,  cns05*(stops.service) as cns05served,  cns06*(stops.service) as cns06served,  cns07*(stops.service) as cns07served,  cns08*(stops.service) as cns08served,  cns09*(stops.service) as cns09served,  cns10*(stops.service) as cns10served,  cns11*(stops.service) as cns11served,  cns12*(stops.service) as cns12served,  cns13*(stops.service) as cns13served,  cns14*(stops.service) as cns14served,  cns15*(stops.service) as cns15served,  cns16*(stops.service) as cns16served,  cns17*(stops.service) as cns17served,  cns18*(stops.service) as cns18served,  cns19*(stops.service) as cns19served,  cns20*(stops.service) as cns20served,  cr01*(stops.service) as cr01served,  cr02*(stops.service) as cr02served,  cr03*(stops.service) as cr03served,  cr04*(stops.service) as cr04served,  cr05*(stops.service) as cr05served,  cr07*(stops.service) as cr07served,  ct01*(stops.service) as ct01served,  ct02*(stops.service) as ct02served,  cd01*(stops.service) as cd01served,  cd02*(stops.service) as cd02served,  cd03*(stops.service) as cd03served,  cd04*(stops.service) as cd04served,  cs01*(stops.service) as cs01served,  cs02*(stops.service) as cs02served,  blocks.blockid, census_blocks.urbanid, census_blocks.regionid, census_blocks.congdistid, census_blocks.placeid      from lodes_blocks_wac blocks inner join stops on st_dwithin(blocks.location, stops.location,402.335)    inner join census_blocks ON blocks.blockid=census_blocks.blockid    GROUP BY stops.service, blocks.blockid,census_blocks.urbanid, census_blocks.regionid, census_blocks.congdistid, census_blocks.placeid),  popserved1 as (select sum(c000served) AS c000served, sum(ca01served) AS ca01served, sum(ca02served) AS ca02served, sum(ca03served) AS ca03served, sum(ce01served) AS ce01served, sum(ce02served) AS ce02served, sum(ce03served) AS ce03served, sum(cns01served) AS cns01served, sum(cns02served) AS cns02served, sum(cns03served) AS cns03served, sum(cns04served) AS cns04served, sum(cns05served) AS cns05served, sum(cns06served) AS cns06served, sum(cns07served) AS cns07served, sum(cns08served) AS cns08served, sum(cns09served) AS cns09served, sum(cns10served) AS cns10served, sum(cns11served) AS cns11served, sum(cns12served) AS cns12served, sum(cns13served) AS cns13served, sum(cns14served) AS cns14served, sum(cns15served) AS cns15served, sum(cns16served) AS cns16served, sum(cns17served) AS cns17served, sum(cns18served) AS cns18served, sum(cns19served) AS cns19served, sum(cns20served) AS cns20served, sum(cr01served) AS cr01served, sum(cr02served) AS cr02served, sum(cr03served) AS cr03served, sum(cr04served) AS cr04served, sum(cr05served) AS cr05served, sum(cr07served) AS cr07served, sum(ct01served) AS ct01served, sum(ct02served) AS ct02served, sum(cd01served) AS cd01served, sum(cd02served) AS cd02served, sum(cd03served) AS cd03served, sum(cd04served) AS cd04served, sum(cs01served) AS cs01served, sum(cs02served) AS cs02served, LEFT(blockid,5) AS countyid from popserved GROUP BY LEFT(blockid,5)), tempstops as (select id, agencyid, blockid, location from gtfs_stops stop inner join aids on stop.agencyid = aids.aid),  census as (select block.blockid, block.urbanid, block.regionid, block.congdistid, block.placeid  from census_blocks block inner join tempstops on st_dwithin(block.location, tempstops.location, 402.335)  group by block.blockid),  popwithinx as (select  sum(c000) AS c000withinx, sum(ca01) AS ca01withinx, sum(ca02) AS ca02withinx, sum(ca03) AS ca03withinx, sum(ce01) AS ce01withinx, sum(ce02) AS ce02withinx, sum(ce03) AS ce03withinx, sum(cns01) AS cns01withinx, sum(cns02) AS cns02withinx, sum(cns03) AS cns03withinx, sum(cns04) AS cns04withinx, sum(cns05) AS cns05withinx, sum(cns06) AS cns06withinx, sum(cns07) AS cns07withinx, sum(cns08) AS cns08withinx, sum(cns09) AS cns09withinx, sum(cns10) AS cns10withinx, sum(cns11) AS cns11withinx, sum(cns12) AS cns12withinx, sum(cns13) AS cns13withinx, sum(cns14) AS cns14withinx, sum(cns15) AS cns15withinx, sum(cns16) AS cns16withinx, sum(cns17) AS cns17withinx, sum(cns18) AS cns18withinx, sum(cns19) AS cns19withinx, sum(cns20) AS cns20withinx, sum(cr01) AS cr01withinx, sum(cr02) AS cr02withinx, sum(cr03) AS cr03withinx, sum(cr04) AS cr04withinx, sum(cr05) AS cr05withinx, sum(cr07) AS cr07withinx, sum(ct01) AS ct01withinx, sum(ct02) AS ct02withinx, sum(cd01) AS cd01withinx, sum(cd02) AS cd02withinx, sum(cd03) AS cd03withinx, sum(cd04) AS cd04withinx, sum(cs01) AS cs01withinx, sum(cs02) AS cs02withinx, LEFT(blockid,5) AS countyid FROM census INNER JOIN lodes_blocks_wac USING(blockid) GROUP BY countyid), totalpop AS (SELECT lodes_blocks_wac.*, LEFT(lodes_blocks_wac.blockid,5) AS countyid, census_blocks.urbanid, census_blocks.regionid, census_blocks.congdistid, census_blocks.placeid  FROM lodes_blocks_wac INNER JOIN census_blocks USING(blockid)), totalpop1 AS (SELECT sum(c000) AS c000, sum(ca01) AS ca01,sum(ca02) AS ca02,sum(ca03) AS ca03,sum(ce01) AS ce01,sum(ce02) AS ce02,sum(ce03) AS ce03,sum(cns01) AS cns01,sum(cns02) AS cns02,sum(cns03) AS cns03,sum(cns04) AS cns04,sum(cns05) AS cns05,sum(cns06) AS cns06,sum(cns07) AS cns07,sum(cns08) AS cns08,sum(cns09) AS cns09,sum(cns10) AS cns10,sum(cns11) AS cns11,sum(cns12) AS cns12,sum(cns13) AS cns13,sum(cns14) AS cns14,sum(cns15) AS cns15,sum(cns16) AS cns16,sum(cns17) AS cns17,sum(cns18) AS cns18,sum(cns19) AS cns19,sum(cns20) AS cns20,sum(cr01) AS cr01,sum(cr02) AS cr02,sum(cr03) AS cr03,sum(cr04) AS cr04,sum(cr05) AS cr05,sum(cr07) AS cr07,sum(ct01) AS ct01,sum(ct02) AS ct02,sum(cd01) AS cd01,sum(cd02) AS cd02,sum(cd03) AS cd03,sum(cd04) AS cd04,sum(cs01) AS cs01,sum(cs02) AS cs02,countyid FROM totalpop GROUP BY countyid) select popserved1.*, popatlos1.*, popwithinx.*, totalpop1.*, census_counties.countyid AS areaid, census_counties.cname AS areaname FROM census_counties LEFT JOIN popserved1 USING(countyid) LEFT JOIN popatlos1 USING(countyid) LEFT JOIN popwithinx USING(countyid) LEFT JOIN totalpop1 USING(countyid)

Update: trying again, the above query did complete, but it took about 15 minutes. Working to figure out ways to improve the query and/or the approach to generating the query.

irees commented 6 years ago

I improved the speed of the employment queries (should take about 5-10s now) by adding a spatial index on lodes_blocks_wac.location. I did this for all databases in both prod and dev. Not sure why the index was missing - when I create a new database locally from scratch, it is present.