The query takes a long time result in a 504 timeout error?

Question

everyone. I am currently maintaining an old system using struts2 and hibernate. There is a function which is formatting data in Excel then download it according to the query parameters. Usually, 3000 records was added per day, and usually we just need to check today's data, which is down about 3000 records in Excel format.

It works fine. Then I got a new demand, for every record, I need to get two position information about the record. Well, the position information doesn't exist in our database. I need to request two interfaces to get them. I simply do this in a for loop, that is when I got the data returned from database, then loop through the array list, for every record, request for the two position information it needed. Until I got all the message, then I format them in Excel, and then respond to front end.

The problem is, the server responds too slow. When it about 200、300 records, it works fine. But when it comes to 3000 records, I got a 504 timeout error.

I don't think 3000 records is large, but I think for every record sending a request then parse the respond message is time consuming. I think I'm doing it the wrong way, but I have little experience dealing with this situation. So, can I get some suggestion? Thanks in advance.

Edit: I record time when downloading 200 records, with and without external requests, and the pseudo code here. I thought the external requests is the main reason. with external requests with external requests with external requests without external requests without external requests without external requests

    public byte[] methodName() {

  // 13 title
  String[] title = { "title1", "title2", "title3", ... , "title13" };
  // result is the records returned from db select
  String[][] data = new String[result.size()*20][title.length];

  int row = 0, col = 0;

  SomeEntity someEntity = null;

  System.out.println("with external requests");
  System.out.println("before loop-->" + System.currentTimeMillis() + " - " + new Date().toString());

  for (Object object : result) {
    someEntity = (SomeEntity) object;
    col = 0;
    data[row][col++] = someEntity.getTitle1();
    data[row][col++] = someEntity.getTitle2();
    // add other data
    ...
    // get location, two similar requests
    data[row][col++] = getXXXLocation(someEntity.getLongitude(), someEntity.getLatitude());
    data[row][col++] = getXXXLocation(someEntity.getMctId(), someEntity.getTerId());
    row++;
  }
  // then generate the ExcelModel
  System.out.println("after loop-->" + System.currentTimeMillis() + " - " + new Date().toString());
  ExcelModel excelModel = new ExcelModel("filename");
  excelModel.addColumnsTitle(title);
  System.out.println("before generate excel-->" + System.currentTimeMillis() + " - " + new Date().toString());
  byte[] aws = excelModel.generateExcelFile(data);
  System.out.println("after generate excel-->" + System.currentTimeMillis() + " - " + new Date().toString());

  return aws;
}

Answer 1

This is not a real answer, but it is too long for a comment.

I thought the main problem is the external requests.

Obviously it's the external calls. Almost all the time spent is TTFB. But you still don't know where that time actually goes.

There are basically three costs. The first is the cost of executing your code, formatting the requests and parsing the responses. As this appears to be negligible, we can move right on. The second is the fixed cost of executing a call: sending stuff across the network. This is a toll you pay 6000 times. The third is the cost of retrieving the data: this is also a toll you pay 6000 times, but depending on the data source the cost may not be fixed (e.g. database caching can offset repeated queries).

If most of the time spent is on network calls then (unless you can persuade your network admin there's a problem with the line) you can't do much except reduce the number of calls. That is, extend the remote interface with services which accept and return bulk bulk payloads.

If most of the time is spent in data retrieval you need to look at the database queries (or whatever) and try to tune them.

Clearly I'm making these suggestions without knowing anything about your organisation or your responsibilities/authority. If the external service is truly external you may not be able to get improvements.

Alternative solutions?

• Concurrent processing: have multiple threads calling the remote server. You will need to rewrite your code, but at least that's under your control.
• Increasing the timeout: just give your program more time to complete. However, presuming the cost is linear it will take ten minutes to process 3000 pairs of calls. Your users may not want their screen to hang for that long. So...
• Asynchronous submission. The user fires off a request, moves on to the next task and some time later the finished Excel file is popped into their inbox.