There are 2 maps, in one 120 elements, in another 40. There is a search, that is, all elements of map 2 are first sorted with the first element of the first map, it all goes into the database. The element of the first map + elements in turn of the second map (for example: 123456, 456123; 123456, 789654, etc.).

There is such a class, with the method, this method is called each time when iterating through two maps

@Service public class GetDistanceBetweenStationsImpl extends ConnectionDB implements GetDistanceBetweenStations { // Подключаем логгер private static Logger logger = LoggerFactory.getLogger(GetDistanceBetweenStationsImpl.class); private GetDistanceBetweenStationsImpl() { } @Override public List<Integer> getDistanceBetweenStations(String keyOfStationDeparture, String keyOfStationDestination) { ResultSet resultSet; CallableStatement callableStatement = null; List<Integer> listResult = new ArrayList<>(); try (Connection connection = getDataSource().getConnection()) { // Подготавливаем запрос callableStatement = connection.prepareCall(" { call getdistancetest(?,?) } "); // Определяем значения параметров callableStatement.setString(1, keyOfStationDeparture); callableStatement.setString(2, keyOfStationDestination); // Выполняем запрос resultSet = callableStatement.executeQuery(); // Вычитываем полученное значение while (resultSet.next()) { listResult.add(resultSet.getInt(1)); } logger.debug("Get distance for: {}", keyOfStationDeparture + "_" + keyOfStationDestination + ": " + listResult.get(0)); } catch (SQLException sqlEx) { logger.error("Ошибка запроса {} - {}", callableStatement, sqlEx.getMessage()); } return listResult; } }

That is, as you can see, 2 station codes are transmitted (departure station, destination station) in the base in the procedure the distance between these stations is searched.

Here is the code for searching and calling the request, here I have a check, if this combination has already been done, then I don’t climb into the database

 Iterator<Map.Entry<Integer, Route>> iterator = mapOfRoutes.entrySet().iterator(); while (iterator.hasNext()) { Map.Entry<Integer, Route> entry = iterator.next(); for (int i = 0; i < listOfWagons.size(); i++) { String stationCode1 = listOfWagons.get(i).getKeyOfStationDestination(); String stationCode2 = entry.getValue().getKeyOfStationDeparture(); String key = stationCode1 + "_" + stationCode2; // Заполняем мапы расстояний if (!rootMapWithDistanceMoreMaxDist.containsKey(key)) { if (!rootMapWithDistances.containsKey(key)) { List<Integer> listDistance = getDistanceBetweenStations.getDistanceBetweenStations(stationCode1, stationCode2); int distance = listDistance.get(0); if (distance == -1) { logger.error("Проверьте код станции {}"); } } else { if (distance != -20000) { rootMapWithDistances.put(key, listDistance); } else { rootMapWithDistanceMoreMaxDist.put(key, listDistance); } } } } } }

My problem is that with a small amount of elements in the map, for example, 60 in the first and 30 in the second, and depending on the repetitions, all this of course works quickly, about 15 seconds.

But when I made 120 elements in the first and 40 in the second, I reduced the number of repeated elements in the file, from where I load everything, then now I have the whole thing working for a minute 45 seconds. And I just can’t reduce this time, I’ve already made half of the request, I left only the station codes, and still.

Tell me, please, how can I speed up my access to the database, well, it can not be that 3500 queries work so long, I saw 30,000 queries in 30 seconds working out.

I create connection through org.apache.tomcat.jdbc.pool.DataSource

 <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" lazy-init="false" destroy-method="close"> <property name="driverClassName" value="org.postgresql.Driver"/> <property name="url" value="строка к БД"/> <property name="username" value="логин"/> <property name="password" value="пароль"/> </bean>

Base this Postgres. Tell me, please, how to optimize my appeal, can I create some kind of pools? On the Internet I tried the methods that were offered, did not help me.

  • Why such a complex structure? Can't make one normal query / procedure / view and get what you need at once? - Igor Kudryashov
  • @IgorKudryashov what do you mean? One time it will not be possible to do, I have more than one request. Just the whole result goes further into processing, where it is sorted and sorted. - Vladislav
  • It is difficult to advise something concrete, your architecture is not clear and the final goal is not clear. - Igor Kudryashov
  • The ultimate goal is the distribution of cars by the minimum distance to the station of the start of the flight. At the entrance comes two files with an excl with wagons where they stand and flights, this is exactly the two mapy. Then each station of the car goes over the stations where flights start. Formed final mapa with distances and then already goes selection. Here I have a problem just for the formation of a mapy with distributions due to the slow mining of toli db, toli on the side of java - Vladislav
  • Try increasing the pool size. Perhaps some sorting happens in the database, then you need to optimize the database so that they are executed in memory and not in a swap. - Roman C

0