I33Buckler commented on issue #1287: URL: https://github.com/apache/age/issues/1287#issuecomment-1793277565
> @I33Buckler If you are using that function, with the **O(N^3)** runtime, or greater, I wouldn't wait as it could take a very long time. You need to find a way to write a more efficient query to do what you are doing. Thank you. Hence the question. I modified the example described at [Is it possible to create a graph in AGE using existing table in the database?](https://stackoverflow.com/questions/75178525/is-it-possible-to-create-a-graph-in-age-using-existing-table-in-the-database) to load directly from a table as shown in the code below. The problem was that running this process as is without segmentation resulted in an unexpected crash of the query. I tested the process by segmenting at tc_date which created 23 segments and running a couple of tests. Each test ran successfully unlike the full loading of the table. Hence the decision to loop the process over tc_date processing each segment separately. Until I am able to determine the cause of the crash and possible solution the segmentation is the best response. Any suggestions on improving the process below are welcome. ```SQL -- Function to load database row into graph schema CREATE OR REPLACE FUNCTION create_stop_times(graph_name text, tc_date date, trip_id text, stop_id text, arrival_time text, arrival_time_sec bigint, departure_time text, departure_time_sec bigint, stop_sequence bigint, pickup_type int, drop_off_type int) returns text LANGUAGE plpgsql VOLATILE as $stop_times$ declare nodename text := graph_name || '.' || 'stop_times'; BEGIN execute format ('select * from cypher(''%1$s'', $$match (t:trips),(s:stops) where t.id=%2$s and s.id=%3$s and t.tc_date=%11$s and t.tc_date=s.tc_date create(t)<- [:PART_OF_TRIP]- (st:stop_times {tc_date: %11$s, arrival_time: %4$s, arrival_time_seconds: %5$s, departure_time: %6$s, departure_time_seconds: %7$s, stop_sequence: %8$s, pickup_type: %9$s, drop_off_type: %10$s})- [:LOCATED_AT]->(s)$$) as (st agtype);', quote_ident(graph_name),quote_ident(trip_id),quote_ident(stop_id), quote_ident(arrival_time),to_char(arrival_time_sec,'999999'), quote_ident(departure_time),to_char(departure_time_sec,'999999'), to_char(stop_sequence,'9999'),to_char(pickup_type,'9'),to_char(drop_off_type,'9'), format('"%s"',to_char(tc_date,'yyyy-mm-dd'))); return nodename; END $stop_times$ ; -- Create table with row number for referencing during step through create temp table stop_times as select dense_rank() over (order by tc_date,trip_id) as trip_rank, row_number() over (partition by tc_date,trip_id order by stop_sequence) as row_num,* from allservices.stop_times order by row_num; create index on stop_times(tc_date); create index on stop_times(trip_id); create index on stop_times(stop_id); create index on stop_times (trip_rank); create index on stop_times (row_num); create unique index on stop_times (trip_rank,row_num); create unique index on stop_times (trip_rank,stop_sequence); -- Step through table to create graph nodes do $$ declare graphname varchar(32); row_selection record; loop_counter int:= 0; graph_start timestamp; graph_end timestamp; duration time; begin graph_start := clock_timestamp(); graph_end := clock_timestamp(); raise notice 'Start of query: %',graph_start; for row_selection in (select row_num from stop_times) order by row_num loop perform create_stop_times('transport_network', a.tc_date,a.trip_id,a.stop_id, a.arrival_time,a.arrival_time_sec, a.departure_time,a.departure_time_sec, a.row_num,a.pickup_type,a.drop_off_type) from stop_times a where a.row_num=row_selection.row_num; end loop; graph_end := clock_timestamp(); raise notice 'End of query: %',graph_end; raise notice 'Duration of graph query: %', graph_end-graph_start; end;$$; ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: dev-unsubscr...@age.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
