What is the fastest way to look up continuous data on Anylogic (Java, SQL)

I have a database table of 900,000 rows. This table contains power, braking power, speed log from train trips as a function of distance.

1. Origin
2. Destination
3. Distance travelled from departing point
4. Power reading in MW
5. Braking power reading in MW
6. Speed reading in KPH

There are 13 different destinations and outbound and inbound trips have different sets of data so there are 26 datasets.

The aim is to determine the power at a given distance for a particular combination of origin & destination.

I have attempted two different methods. These are working but are extremely slow (roughly 6 seconds per hour). Given that I need to run this simulation for long periods of time, potentially up to 25 years). This would take me over 350+ hours to run this.

Method 1) Using selectFrom to obtain an array of data immediately before and after the travelled distance to interpolate.

selectFrom(table).where(table.origin.eq(origin), table.destination.eq(destination), table.power.between(currentDistance-1,currentDistance+1) ).ArrayOfDoubles(table.power)

This returns an array of roughly <20 elements containing the power readings. then, interpolate using the first and last element of the array to obtain power at given distance.

Method 2) Feeding TableFunction the argument and values data when agent instance is created

tableFunction.setArgumentsAndValues(distanceArray,powerArray);

This method also works, but the simulation becomes stagnant as ~30-40 agents are produced per day.

Answer

Database access is a slow and costly excercise and should only be done once if possible, i.e. load all data at the start of the simulation into memory. You can do that by:

1. Create a java class that represents the same data as in that database table
2. Save all rows as instances of that class at model start-up into a map – you can use Origin/Destination as the key (use Anylogic’s Pair object) and the class instance as the value
3. do the lookup using the map and you will no longer need to do database queries at runtime

You will have a slower setup, but much faster execution