Производительность JQGrid медленно в большом наборе данных ⇐ Javascript

[Anonymous]

Я пытаюсь получить данные из базы данных, чтобы показать в JQGrid.
Этот код работает хорошо и быстро для небольших данных. Но когда он показывает тысячи данных, они становятся действительно медленными, как более 2 минут, что является проблемой для пользователя. Все еще медленно, когда я получаю тысячи данных.
*** есть ли способ улучшить это? *** < /p>
Примечание. Приведен ниже код, работающий с Java Spring Mvc < /p>
Другое примечание: в HTML я использую IMUI: ListTable причина IM работает с средой Intramart, но в основном используется JQGRID
repository.javaобразнойpublic List selectGRList(String search_transtype, String[] search_grno, String search_grdatef,
String search_grdatet, String[] search_deliverynote, String[] search_materialno, String user_cd)
throws Exception {

System.out.println("At InvoicingRepository.selectGrList()");

try {
InvoicingWorkflowService invoicingWorkflowService = new InvoicingWorkflowService();
ItemListNode[] ItemListNodeArray2 = invoicingWorkflowService.getItemsByCategory("vat_master");
String[] validfromArray = new String[ItemListNodeArray2.length];
for (int i = 0; i < ItemListNodeArray2.length; i++) {
validfromArray = ItemListNodeArray2.getItemCd();
}

// Sort validfromArray to improve searching later
Arrays.sort(validfromArray);

String doc_type = "ZN01";
String plant = "D4N1";

SQLManager sqlManager = new SQLManager();
String sql = "SELECT * FROM v_gr_list_w_price_v2 ";
Collection parameters = new ArrayList();
boolean hasWhere = false;

// Apply filters (as before)
if (!user_cd.equals("tenant")) {
sql += "WHERE vendor_code LIKE ? ";
parameters.add(user_cd.substring(0, 7) + "%");
hasWhere = true;
}

// TR Type filtering
if (search_transtype != null && !search_transtype.isEmpty()) {
sql += (hasWhere ? "AND " : "WHERE ") + "tr_type = ? ";
parameters.add(search_transtype);
hasWhere = true;
}

// Date filtering
if (!search_grdatef.isEmpty() && !search_grdatet.isEmpty()) {
sql += (hasWhere ? "AND " : "WHERE ") + "gr_date BETWEEN ? AND ? ";
parameters.add(search_grdatef);
parameters.add(search_grdatet);
hasWhere = true;
}

// Filter for GR number
if (search_grno != null && search_grno.length > 0 && !search_grno[0].isEmpty()) {
sql += (hasWhere ? "AND " : "WHERE ") + "gr_doc IN ("
+ String.join(", ", Collections.nCopies(search_grno.length, "?")) + ") ";
parameters.addAll(Arrays.asList(search_grno));
hasWhere = true;
}

// Filter for delivery note
if (search_deliverynote != null && search_deliverynote.length > 0 && !search_deliverynote[0].isEmpty()) {
sql += (hasWhere ? "AND " : "WHERE ") + "delivery_note IN ("
+ String.join(", ", Collections.nCopies(search_deliverynote.length, "?")) + ") ";
parameters.addAll(Arrays.asList(search_deliverynote));
hasWhere = true;
}

// Filter for material number
if (search_materialno != null && search_materialno.length > 0 && !search_materialno[0].isEmpty()) {
sql += (hasWhere ? "AND " : "WHERE ") + "material_number IN ("
+ String.join(", ", Collections.nCopies(search_materialno.length, "?")) + ") ";
parameters.addAll(Arrays.asList(search_materialno));
}

long startTime = System.currentTimeMillis(); // Start timing

List sqlResults = (List) (sqlManager
.select(InvoicingGRListModel.class, sql, parameters));

long endTime = System.currentTimeMillis(); // End timing

System.out.println("Query Execution Time: " + (endTime - startTime) + " ms");

List result = new ArrayList();
DecimalFormat decimalFormat = new DecimalFormat("#,###.##");

// Loop through each record and populate the map
for (InvoicingGRListModel invoicingGRListModel : sqlResults) {
Integer grDate = Integer.parseInt(invoicingGRListModel.getGr_date());

// Use binary search to find the maxDate instead of iterating through all values
int maxDate = findMaxDate(validfromArray, grDate);

// Cache the result of invoicingWorkflowService.getItem() once per loop
// iteration
// Correct the type here to match the actual return type
Item item = invoicingWorkflowService.getItem(Integer.toString(maxDate));
Integer validto = Integer.parseInt(item.getItemShortName());
String vatrate = item.getItemName();
BigDecimal vatPercentage = new BigDecimal(vatrate).divide(BigDecimal.valueOf(100.0), 4,
RoundingMode.HALF_UP);

// Calculations
BigDecimal amount = new BigDecimal(invoicingGRListModel.getAmount());
BigDecimal net_price = new BigDecimal(invoicingGRListModel.getNet_price());
BigDecimal vat_amount = amount.multiply(vatPercentage).setScale(2, RoundingMode.HALF_UP);
BigDecimal total_price = net_price.add(vat_amount).setScale(2, RoundingMode.HALF_UP);

// Create the map for this record
Map valueMap = new HashMap();
valueMap.put("f_doc_type", invoicingGRListModel.getDoc_type());
valueMap.put("f_plant_code", invoicingGRListModel.getPlant_code());
valueMap.put("f_gr_no_line", invoicingGRListModel.getGr_no_line());
valueMap.put("f_gr_no", invoicingGRListModel.getGr_doc());
valueMap.put("f_gr_line", invoicingGRListModel.getGr_doc_item());
valueMap.put("f_gr_date", invoicingGRListModel.getGr_date());
valueMap.put("f_delivery_note", invoicingGRListModel.getDelivery_note());
valueMap.put("f_item", invoicingGRListModel.getItem());
valueMap.put("f_material_number", invoicingGRListModel.getMaterial_number());
valueMap.put("f_vendor_code", invoicingGRListModel.getVendor_code());
valueMap.put("f_vendor_name", invoicingGRListModel.getVendor_name());
valueMap.put("f_po_no", invoicingGRListModel.getPo_no());
valueMap.put("f_po_item", invoicingGRListModel.getPo_item());
valueMap.put("f_material_name", invoicingGRListModel.getMaterial_name());
valueMap.put("f_qty", invoicingGRListModel.getGr_quantity());
valueMap.put("f_unit", invoicingGRListModel.getUnit());
valueMap.put("f_pricing_date", invoicingGRListModel.getPricing_date());
valueMap.put("f_purch_group", invoicingGRListModel.getPurch_group());
valueMap.put("f_currency", invoicingGRListModel.getCurrency());
valueMap.put("f_vat_percent", vatrate);
valueMap.put("f_price", decimalFormat.format(net_price.doubleValue()));
valueMap.put("f_amount_item", decimalFormat.format(amount.doubleValue()));
valueMap.put("f_vat_amount", decimalFormat.format(vat_amount.doubleValue()));
valueMap.put("f_transaction_type", invoicingGRListModel.getTr_type());

// Add the populated map to the result list
result.add(valueMap);
}

return result;

} catch (SQLException | AccessSecurityException | IllegalArgumentException | InstantiationException
| IllegalAccessException | InvocationTargetException | NamingException e) {
e.printStackTrace();
throw new Exception("DB error in selectGRList()", e);
}
}
< /code>
javascript: < /p>
$('#requestSearchGR').click(function() {
var searchtranstype = $('#i_transaction_type').val();
var searchgrno = $('#i_search_grno').val();
var searchgrdatef = $('#i_search_grdate_f').val();
var searchgrdatet = $('#i_search_grdate_t').val();
var searchdeliverynote = $('#i_search_deliverynote').val();
var searchmaterialno = $('#i_search_materialno').val();

// Validate Date Search
if ((searchgrdatef.length == 0 && searchgrdatet.length != 0) || (searchgrdatef.length != 0 && searchgrdatet.length == 0)) {
showErrorDialog("Please input both Date From and To, if searching by GR Date!");
return;
}

$('#loadingIndicator').show();
$('#progressText').text('Loading data...');

let progressUpdate = setInterval(function() {
let progress = parseInt($('#progressText').text().replace(/\D/g, '')) || 0;
if (progress < 80) $('#progressText').text('Loading data... ');
}, 1000); // Update every 1s instead of 500ms

// Start the timer
const startTime = performance.now();

$.ajax({
url: 'invoicing/searchgr',
type: 'POST',
data: {
transaction_type: searchtranstype,
search_grno: searchgrno,
search_grdate_f: searchgrdatef,
search_grdate_t: searchgrdatet,
search_deliverynote: searchdeliverynote,
search_materialno: searchmaterialno
},
dataType: 'json',
cache: false,
success: function(returnObj) {
// Stop the timer
const endTime = performance.now();
const duration = (endTime - startTime) / 1000; // Time in seconds
console.log('Data loading time: ' + duration + ' seconds'); // Log it or display somewhere

clearInterval(progressUpdate);
$('#progressText').text('Loading data... ');
setTimeout(() => $('#loadingIndicator').hide(), 500);

if (!returnObj.gridGRList || returnObj.gridGRList.length === 0) {
showErrorDialog("No Data Found!");
$("#listSearchGR").clearGridData();
} else {
$("#listSearchGR").clearGridData().setGridParam({
data: returnObj.gridGRList.filter((data) => !filter.includes(data.f_gr_no_line))
}).trigger("reloadGrid");
}
},
error: function(error) {
clearInterval(progressUpdate);
$('#loadingIndicator').hide();
console.log(JSON.stringify(error));
$("#listSearchGR").clearGridData();
}
});
});

< /code>
html: < /p>































Подробнее здесь: https://stackoverflow.com/questions/794 ... ge-dataset