How to optimize SQL query in Anylogic

Question

I am generating Agents with parameter values coming from SQL table in Anylogic. when agent is generated at source I am doing a v look up in table and extracting corresponding values from table. For now it is working perfectly but it is slowing down the performance.

Structure of Table looks like this

I am querying the data from this table with below code

    double value_1 = (selectFrom(account_details)
 .where(account_details.act_code.eq(z))
 .list(account_details.avg_value)).get(0);
 
 double value_min = (selectFrom(account_details)
 .where(account_details.act_code.eq(z))
 .list(account_details.min_value)).get(0);
  double value_max = (selectFrom(account_details)
 .where(account_details.act_code.eq(z))
 .list(account_details.max_value)).get(0);

// Fetch the cluster number from account table
int cluster_num = (selectFrom(account_details)
 .where(account_details.act_code.eq(z))
 .list(account_details.cluster)).get(0);
 
 int act_no  = (selectFrom(account_details)
 .where(account_details.act_code.eq(z))
 .list(account_details.actno)).get(0);
 
String pay_term  = (selectFrom(account_details)
 .where(account_details.act_code.eq(z))
 .list(account_details.pay_term)).get(0);
 
String pay_term_prob  = (selectFrom(account_details)
 .where(account_details.act_code.eq(z))
 .list(account_details.pay_term_prob)).get(0);

But this is very slow and wants to improve the performance. someone mentioned that we can create a Java class and then add the table into collection . Is there any example where I can refer. I am finding it difficult to put entire code.

I have created a class using below code:

public class Customer {
private String act_code;
private int actno;
private double avg_value;
private String pay_term;
private String pay_term_prob;
private int cluster;
private double min_value;
private double max_value;

public String getact_code() {
    return act_code;
}
public void setact_code(String act_code) {
    this.act_code = act_code;
}

public int getactno() {
    return actno;
}
public void setactno(int actno) {
    this.actno = actno;
}


public double getavg_value() {
    return avg_value;
}

public void setavg_value(double avg_value) {
    this.avg_value = avg_value;
}
public String getpay_term() {
    return pay_term;
}

public void setpay_term(String pay_term) {
    this.pay_term = pay_term;
}


public String getpay_term_prob() {
    return pay_term_prob;
}

public void setpay_term_prob(String pay_term_prob) {
    this.pay_term_prob = pay_term_prob;
}


public int cluster() {
    return cluster;
}

public void setcluster(int cluster) {
    this.cluster = cluster;
}


public double getmin_value() {
    return min_value;
}
public void setmin_value(double min_value) {
    this.min_value = min_value;
}


public double getmax_value() {
    return max_value;
}
public void setmax_value(double max_value) {
    this.max_value = max_value;
}



} 

Created collection object like this:

Pls provide an reference to add this database table into collection as a next step. then I want to query the collection based on the condition

Answer 1

You are on the right track here!

Every time you access the database to read data there is a computational overhead. So the best option is to access the database only once, at the start of the model. Create all the objects you need, store other data you will need later into Java classes, and then use the Java classes.

My suggestion is to create a Java class for each row in your table, like you have done. And then create a map object - like you have done, but with the key as String and the value as this new object.

Then on model start you can populate this map as follows:

List<Tuple> rows = selectFrom(customer).list();

for (Tuple row : rows) {
    Customer customerData = new Customer(
        row.get( customer.act_code ),
        row.get( customer.actno ),
        row.get( customer.avg_value )
    );
    mapOfCustomerData.put(customerData.act_code, customerData);
}

Where mapOfCustomerData is a linkedHashMap and customer is the name of the table

See the model created in this blog post for more details and an example on using a scenario object to store all the data from the Database in a separate object

Note: The code above is just an example - read this blog post for more details on using the AnyLogic INternal Database

Answer 2

Before using Java classes, try this first: click the "index" tickbox for all columns that you query with a WHERE clause.