I'm trying to retrieve data from database to show in a jqgrid. This code works well and fast for small data. But when it show thousands of data it gets real slow, like more than 2 mins which is an issue for the user.
Now, in the jqgrid i've used a paging but it seems still slow when i retrieve thousands of data. ***Is there any way to improve this? ***
note : in below code im working with java spring mvc
another note : in the html i'm using the imui:listTable cause im working with an intramart environment but its basically using jqGrid
Repository.java
public List<Map<String, Object>> 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[i] = ItemListNodeArray2[i].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<Object> 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<InvoicingGRListModel> sqlResults = (List<InvoicingGRListModel>) (sqlManager
.select(InvoicingGRListModel.class, sql, parameters));
long endTime = System.currentTimeMillis(); // End timing
System.out.println("Query Execution Time: " + (endTime - startTime) + " ms");
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
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<String, Object> 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);
}
}
javascript :
$('#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();
}
});
});
html :
<imui:listTable id="listSearchGR" class="listSearchGR" name="listSearchGR" data="${listSearchGIResult}" autoWidth="true" height="250" multiSelect="true" checkBoxOnly="true" loadonce="true">
<pager rowNum="10" rowList="5,10,25, 50, 100, 200, 500" />
<cols>
<col name="f_plant_code" caption="Plant Code" />
<col name="f_doc_type" hidden="true"/>
<col name="f_gr_no_line" hidden="true" />
<col name="f_gr_no" caption="GR No" sortType="text" />
<col name="f_gr_line" caption="GR Line" />
<col name="f_gr_date" caption="GR Date" />
<col name="f_delivery_note" caption="Delivery Note" />
<col name="f_item" caption="Item" hidden="true" />
<col name="f_material_number" caption="Material No" />
<col name="f_material_name" caption="Material Name" />
<col name="f_qty" caption="Qty" align="right" />
<col name="f_unit" caption="Unit" />
<col name="f_pricing_date" caption="Pricing Date" />
<col name="f_purch_group" caption="Purchasing Group" />
<col name="f_currency" caption="Currency" />
<col name="f_price" caption="Price" align="right" />
<col name="f_amount_item" caption="Amount" align="right" />
<col name="f_vat_percent" caption="VAT %" align="right" />
<col name="f_vat_amount" caption="VAT" align="right" />
<col name="f_transaction_type" caption="Trans. Type" align="right" />
<col name="f_vendor_code" caption="Vendor Code" align="right" hidden="true" />
<col name="f_vendor_name" caption="Vendor Name" align="right" hidden="true" />
<col name="f_po_no" caption="Po No" align="right" hidden="true" />
<col name="f_po_item" caption="Po Item" align="right" hidden="true" />
</cols>
</imui:listTable>
I do not think that the problem is jqGrid, but rather the design of your code.
What I see, is that you first load the entire data from the server in ajax cal and the you do clear grid and then you do filter data and then you load the data in the grid. In this case paging in the grid will not help you, since the entire data is already loaded in the grid
The code that slow your process imho is here:
success: function(returnObj) {
....
$("#listSearchGR").clearGridData().setGridParam({
data: returnObj.gridGRList.filter((data) => !filter.includes(data.f_gr_no_line))
}).trigger("reloadGrid");
...
}
Try to change your conception to load small portion from the server