General Performance Issues Illustrated with a Specific Method

Cloud HIMS is a JSF/JPA (EclipseLink 2.3)/MySQL application designed to capture data in Health Care Centres across Sri Lanka. We've recently migrated to ObjectDB to improve performance. Despite the migration, we're experiencing general performance issues across all transactions. As requested in a seperate thread, I'm isolating a specific issue to illustrate the broader problem.

Method: listPatientsByIDsWithBasicData
Duration: Takes around 3 seconds to search
 

Search Method

public List<ClientBasicData> listPatientsByIDsWithBasicData(String ids) {
        Long st = new Date().getTime();
        System.out.println("listPatientsByIDsWithBasicData - start - " + st);
        List<ClientBasicData> cs = new ArrayList<>();
        if (ids == null || ids.trim().equals("")) {
            return cs;
        }
        String jpql;
        Map m;
        m = new HashMap();
        jpql = "select new lk.gov.health.phsp.pojcs.ClientBasicData("
                + "c.id, "
                + "c.phn, "
                + "c.person.name, "
                + "c.person.nic, "
                + "c.person.phone1, "
                + "c.person.address "
                + ") "
                + " from Client c "
                + " where c.retired=false "
                + " and "
                + " ("
                + " lower(c.phn)=:q "
                + " or "
                + " lower(c.person.localReferanceNo)=:q "
                + " or "
                + " lower(c.person.ssNumber)=:q "
                + " or "
                + " c.person.phone1=:q "
                + " or "
                + " c.person.phone2=:q "
                + " or "
                + " lower(c.person.nic)=:q "
                + " ) ";
        m.put("q", ids.trim().toLowerCase());
        System.out.println("m = " + m);
        System.out.println("j = " + jpql);
        cs = (List<ClientBasicData>) getFacade().findLightsByJpql(jpql, m);
        System.out.println("cs size = " + cs.size());
        Long ed = new Date().getTime();
        System.out.println("listPatientsByIDsWithBasicData - end - " + ed);
        System.out.println("listPatientsByIDsWithBasicData - Duration - " + (ed - st));
        return cs;
    }

Search Method Output

  listPatientsByIDsWithBasicData - start - 1695401112698]]
  m = {q=0715812399}]]
  j = select new lk.gov.health.phsp.pojcs.ClientBasicData(c.id, c.phn, c.person.name, c.person.nic, c.person.phone1, c.person.address )  from Client c  where c.retired=false  and  ( lower(c.phn)=:q  or  lower(c.person.localReferanceNo)=:q  or  lower(c.person.ssNumber)=:q  or  c.person.phone1=:q  or  c.person.phone2=:q  or  lower(c.person.nic)=:q  ) ]]
  cs size = 7]]
  listPatientsByIDsWithBasicData - end - 1695401115663]]
  listPatientsByIDsWithBasicData - Duration - 2965]]

  listPatientsByIDsWithBasicData - start - 1695401637568]]
  m = {q=0715812399}]]
  j = select new lk.gov.health.phsp.pojcs.ClientBasicData(c.id, c.phn, c.person.name, c.person.nic, c.person.phone1, c.person.address )  from Client c  where c.retired=false  and  ( lower(c.phn)=:q  or  lower(c.person.localReferanceNo)=:q  or  lower(c.person.ssNumber)=:q  or  c.person.phone1=:q  or  c.person.phone2=:q  or  lower(c.person.nic)=:q  ) ]]
  cs size = 7]]
  listPatientsByIDsWithBasicData - end - 1695401640561]]
  listPatientsByIDsWithBasicData - Duration - 2993]]

Entity Classes

Client

package lk.gov.health.phsp.entity;

import java.io.Serializable;
import java.util.Date;
import javax.jdo.annotations.Index;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import lk.gov.health.phsp.pojcs.Identifiable;

@Entity
@Table
public class Client implements Serializable , Identifiable {

// <editor-fold defaultstate="collapsed" desc="Attributes">
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private static final long serialVersionUID = 1L;

    @Index
    @OneToOne(cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    private Person person;

    @Index
    private String phn;

    private String comments;
    /*
    Create Properties
     */
    @ManyToOne(fetch = FetchType.LAZY)
    private WebUser createdBy;
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date createdAt;
    @Temporal(javax.persistence.TemporalType.DATE)
    private Date createdOn;
    @ManyToOne(fetch = FetchType.LAZY)
    private Institution createInstitution;
    @ManyToOne(fetch = FetchType.LAZY)
    private Institution poiInstitution;
    /*
    Last Edit Properties
     */
    @ManyToOne(fetch = FetchType.LAZY)
    private WebUser lastEditBy;
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date lastEditeAt;
    /*
    Retire Reversal Properties
     */
    @ManyToOne(fetch = FetchType.LAZY)
    private WebUser retiredReversedBy;
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date retiredReversedAt;
    /*
    Retire Properties
     */
    private boolean retired;
    private boolean reservedClient;
    @ManyToOne(fetch = FetchType.LAZY)
    private WebUser retiredBy;
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date retiredAt;
    private String retireComments;

    // </editor-fold>   
// <editor-fold defaultstate="collapsed" desc="Overrides">
    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {

        if (!(object instanceof Client)) {
            return false;
        }
        Client other = (Client) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Client{" + "phn=" + phn + '}';
    }

// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="Getters & Setters">
    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Person getPerson() {
        if (person == null) {
            person = new Person();
        }
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    public WebUser getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(WebUser createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public WebUser getLastEditBy() {
        return lastEditBy;
    }

    public void setLastEditBy(WebUser lastEditBy) {
        this.lastEditBy = lastEditBy;
    }

    public Date getLastEditeAt() {
        return lastEditeAt;
    }

    public void setLastEditeAt(Date lastEditeAt) {
        this.lastEditeAt = lastEditeAt;
    }

    public boolean isRetired() {
        return retired;
    }

    public void setRetired(boolean retired) {
        this.retired = retired;
    }

    public WebUser getRetiredBy() {
        return retiredBy;
    }

    public void setRetiredBy(WebUser retiredBy) {
        this.retiredBy = retiredBy;
    }

    public Date getRetiredAt() {
        return retiredAt;
    }

    public void setRetiredAt(Date retiredAt) {
        this.retiredAt = retiredAt;
    }

    public String getRetireComments() {
        return retireComments;
    }

    public void setRetireComments(String retireComments) {
        this.retireComments = retireComments;
    }

    public WebUser getRetiredReversedBy() {
        return retiredReversedBy;
    }

    public void setRetiredReversedBy(WebUser retiredReversedBy) {
        this.retiredReversedBy = retiredReversedBy;
    }

    public Date getRetiredReversedAt() {
        return retiredReversedAt;
    }

    public void setRetiredReversedAt(Date retiredReversedAt) {
        this.retiredReversedAt = retiredReversedAt;
    }

    public String getPhn() {
        return phn;
    }

    public void setPhn(String phn) {
        this.phn = phn;
    }

// </editor-fold>

    
    
    public Institution getCreateInstitution() {
        return createInstitution;
    }

    public void setCreateInstitution(Institution createInstitution) {
        this.createInstitution = createInstitution;
    }

    public String getComments() {
        return comments;
    }

    public void setComments(String comments) {
        this.comments = comments;
    }

    public boolean isReservedClient() {
        return reservedClient;
    }

    public void setReservedClient(boolean reservedClient) {
        this.reservedClient = reservedClient;
    }

    public Date getCreatedOn() {
        return createdOn;
    }

    public void setCreatedOn(Date createdOn) {
        this.createdOn = createdOn;
    }

    public Institution getPoiInstitution() {
        return poiInstitution;
    }

    public void setPoiInstitution(Institution poiInstitution) {
        this.poiInstitution = poiInstitution;
    }

    
    
}

Person Class

package lk.gov.health.phsp.entity;

import java.io.Serializable;
import java.util.Date;
import javax.jdo.annotations.Index;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.Transient;
import lk.gov.health.phsp.pojcs.Identifiable;
import org.joda.time.LocalDate;
import org.joda.time.Period;
import org.joda.time.PeriodType;

/**
 *
 * @author Dr. M. H. B. Ariyaratne, MBBS, PGIM Trainee for MSc(Biomedical
 * Informatics)
 */
@Entity
@Table
public class Person implements Serializable, Identifiable {

// <editor-fold defaultstate="collapsed" desc="Persistant Attributes">
    static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.EAGER)
    private Item title;
    private String name;

    @ManyToOne(fetch = FetchType.EAGER)
    private Item sex;

    @ManyToOne(fetch = FetchType.EAGER)
    private Item citizenship;

    @ManyToOne(fetch = FetchType.EAGER)
    private Item ethinicGroup;

    @ManyToOne(fetch = FetchType.EAGER)
    private Item religion;

    @ManyToOne(fetch = FetchType.EAGER)
    private Item mariatalStatus;

    @ManyToOne(fetch = FetchType.EAGER)
    private Item educationStatus;

    private String occupation;

    @Index
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date dateOfBirth;

    private boolean dobIsAnApproximation;

    @Lob
    private String address;

    @Index
    private String phone1;
    @Index
    private String phone2;
    @Index
    private String email;
    @Index
    private String nic;
    @Index
    private String passportNumber;
    @Index
    private String localReferanceNo;
    @Index
    private String ssNumber;

    private String website;
    private String drivingLicenseNumber;

    @ManyToOne(fetch = FetchType.EAGER)
    private Area gnArea;
    @ManyToOne(fetch = FetchType.EAGER)
    private Area dsArea;
    @ManyToOne(fetch = FetchType.EAGER)
    private Area phmArea;
    @ManyToOne(fetch = FetchType.EAGER)
    private Area mohArea;
    @ManyToOne(fetch = FetchType.EAGER)
    private Area district;
    @ManyToOne(fetch = FetchType.EAGER)
    private Area province;

    //Created Properties
    @ManyToOne(fetch = FetchType.LAZY)
    private WebUser createdBy;
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date createdAt;
    @ManyToOne(fetch = FetchType.LAZY)
    private WebUser editer;
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date editedAt;

    //Retairing properties
    private boolean retired;
    @ManyToOne(fetch = FetchType.LAZY)
    private WebUser retiredBy;
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date retiredAt;
    private String retireComments;

// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="Transient Attributes">
    @Transient
    private boolean ageCalculated = false;
    @Transient
    private int ageMonths;
    @Transient
    private int ageDays;
    @Transient
    private int ageYears;
    @Transient
    private String age;
    @Transient
    private long ageInDays;
    @Transient
    private int serealNumber;
    @Transient
    private String transPhoneNumbers;
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="Functions">

    public void calAgeFromDob() {
        ageCalculated = true;
        setAge("");
        setAgeInDays(0l);
        setAgeMonths(0);
        setAgeDays(0);
        setAgeYears(0);
        if (getDateOfBirth() == null) {
            return;
        }

        LocalDate dob = new LocalDate(getDateOfBirth());
        LocalDate date = new LocalDate(new Date());

        Period period = new Period(dob, date, PeriodType.yearMonthDay());
        setAgeYears(period.getYears());
        setAgeMonths(period.getMonths());
        setAgeDays(period.getDays());
        if (getAgeYears() > 12) {
            setAge(period.getYears() + " years.");
        } else if (getAgeYears() > 0) {
            setAge(period.getYears() + " years and " + period.getMonths() + " months.");
        } else {
            setAge(period.getMonths() + " months and " + period.getDays() + " days.");
        }
        period = new Period(dob, date, PeriodType.days());
        setAgeInDays((long) period.getDays());
    }

    public String getAge() {
        if (!ageCalculated) {
            calAgeFromDob();
        }
        return age;
    }

    public Long getAgeInDays() {
        if (!ageCalculated) {
            calAgeFromDob();
        }
        return ageInDays;
    }

    public int getAgeMonths() {
        if (!ageCalculated) {
            calAgeFromDob();
        }
        return ageMonths;
    }

    public int getAgeDays() {
        if (!ageCalculated) {
            calAgeFromDob();
        }
        return ageDays;
    }

    public int getAgeYears() {
        if (!ageCalculated) {
            calAgeFromDob();
        }
        return ageYears;
    }

    public String getNameWithTitle() {
        String temT;
        if (getTitle() != null) {
            temT = getTitle().name + " " + getName();
        } else {
            temT = getName();
        }
        return temT;
    }
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="Getters & Setters">

    /**
     * @return the gnArea
     */
    public Area getGnArea() {
        return gnArea;
    }

    /**
     * @param gnArea the gnArea to set
     */
    public void setGnArea(Area gnArea) {
        this.gnArea = gnArea;
    }

    /**
     * @return the dsArea
     */
    public Area getDsArea() {
        return dsArea;
    }

    /**
     * @param dsArea the dsArea to set
     */
    public void setDsArea(Area dsArea) {
        this.dsArea = dsArea;
    }

    /**
     * @return the phmArea
     */
    public Area getPhmArea() {
        return phmArea;
    }

    /**
     * @param phmArea the phmArea to set
     */
    public void setPhmArea(Area phmArea) {
        this.phmArea = phmArea;
    }

    /**
     * @return the mohArea
     */
    public Area getMohArea() {
        return mohArea;
    }

    /**
     * @param mohArea the mohArea to set
     */
    public void setMohArea(Area mohArea) {
        this.mohArea = mohArea;
    }

    /**
     * @return the district
     */
    public Area getDistrict() {
        return district;
    }

    /**
     * @param district the district to set
     */
    public void setDistrict(Area district) {
        this.district = district;
    }

    /**
     * @return the province
     */
    public Area getProvince() {
        return province;
    }

    /**
     * @param province the province to set
     */
    public void setProvince(Area province) {
        this.province = province;
    }

    /**
     * @param ageMonths the ageMonths to set
     */
    public void setAgeMonths(int ageMonths) {
        this.ageMonths = ageMonths;
    }

    /**
     * @param ageDays the ageDays to set
     */
    public void setAgeDays(int ageDays) {
        this.ageDays = ageDays;
    }

    /**
     * @param ageYears the ageYears to set
     */
    public void setAgeYears(int ageYears) {
        this.ageYears = ageYears;
    }

    /**
     * @param age the age to set
     */
    public void setAge(String age) {
        this.age = age;
    }

    /**
     * @param ageInDays the ageInDays to set
     */
    public void setAgeInDays(long ageInDays) {
        this.ageInDays = ageInDays;
    }

    public Item getSex() {
        return sex;
    }

    public void setSex(Item sex) {
        this.sex = sex;
    }

    public String getRetireComments() {
        return retireComments;
    }

    public void setRetireComments(String retireComments) {
        this.retireComments = retireComments;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public WebUser getEditer() {
        return editer;
    }

    public void setEditer(WebUser editer) {
        this.editer = editer;
    }

    public Date getEditedAt() {
        return editedAt;
    }

    public void setEditedAt(Date editedAt) {
        this.editedAt = editedAt;
    }

    public WebUser getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(WebUser createdBy) {
        this.createdBy = createdBy;
    }

    public String getPhone2() {
        return phone2;
    }

    public void setPhone2(String phone2) {
        this.phone2 = phone2;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name.toUpperCase();
    }

    public boolean isRetired() {
        return retired;
    }

    public void setRetired(boolean retired) {
        this.retired = retired;
    }

    public Date getRetiredAt() {
        return retiredAt;
    }

    public void setRetiredAt(Date retiredAt) {
        this.retiredAt = retiredAt;
    }

    public WebUser getRetiredBy() {
        return retiredBy;
    }

    public void setRetiredBy(WebUser retiredBy) {
        this.retiredBy = retiredBy;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address.toUpperCase();
    }

    public String getPassportNumber() {
        return passportNumber;
    }

    public void setPassportNumber(String passportNumber) {
        this.passportNumber = passportNumber;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getWebsite() {
        return website;
    }

    public void setWebsite(String website) {
        this.website = website;
    }

    public String getDrivingLicenseNumber() {
        return drivingLicenseNumber;
    }

    public void setDrivingLicenseNumber(String drivingLicenseNumber) {
        this.drivingLicenseNumber = drivingLicenseNumber;
    }

    public Item getTitle() {
        return title;
    }

    public void setTitle(Item title) {
        this.title = title;
    }

    public Date getDateOfBirth() {
        return dateOfBirth;
    }

    public void setDateOfBirth(Date dateOfBirth) {
        ageCalculated = false;
        this.dateOfBirth = dateOfBirth;
    }

    public String getPhone1() {
        return phone1;
    }

    public void setPhone1(String phone1) {
        this.phone1 = phone1;
    }

    public String getNic() {
        return nic;
    }

    public void setNic(String nic) {
        this.nic = nic;
    }

    public int getSerealNumber() {
        return serealNumber;
    }

    public void setSerealNumber(int serealNumber) {
        this.serealNumber = serealNumber;
    }

    public Item getCitizenship() {
        return citizenship;
    }

    public void setCitizenship(Item citizenship) {
        this.citizenship = citizenship;
    }

    public Item getEthinicGroup() {
        return ethinicGroup;
    }

    public void setEthinicGroup(Item ethinicGroup) {
        this.ethinicGroup = ethinicGroup;
    }

    public Item getReligion() {
        return religion;
    }

    public void setReligion(Item religion) {
        this.religion = religion;
    }

    public Item getMariatalStatus() {
        return mariatalStatus;
    }

    public void setMariatalStatus(Item mariatalStatus) {
        this.mariatalStatus = mariatalStatus;
    }

    public Item getEducationStatus() {
        return educationStatus;
    }

    public void setEducationStatus(Item educationStatus) {
        this.educationStatus = educationStatus;
    }

// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="Over-rides">
    @Override
    public int hashCode() {
        int hash = 0;
        hash += (getId() != null ? getId().hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        if (!(object instanceof Person)) {
            return false;
        }
        Person other = (Person) object;
        if ((this.getId() == null && other.getId() != null) || (this.getId() != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return getName();
    }
// </editor-fold>

    public boolean isDobIsAnApproximation() {
        return dobIsAnApproximation;
    }

    public void setDobIsAnApproximation(boolean dobIsAnApproximation) {
        this.dobIsAnApproximation = dobIsAnApproximation;
    }

    public String getOccupation() {
        return occupation;
    }

    public void setOccupation(String occupation) {
        this.occupation = occupation;
    }

    public String getTransPhoneNumbers() {
        boolean phoneOneNotBlank = false;
        boolean phoneTwoNotBlank = false;
        if (phone1 != null && !phone1.trim().equals("")) {
            phoneOneNotBlank = true;
        }
        if (phone2 != null && !phone2.trim().equals("")) {
            phoneTwoNotBlank = true;
        }
        if (phoneOneNotBlank && phoneTwoNotBlank) {
            transPhoneNumbers = phone1 + ", " + phone2;
        } else if (phoneOneNotBlank) {
            transPhoneNumbers = phone1;
        } else if (phoneTwoNotBlank) {
            transPhoneNumbers = phone2;
        } else {
            transPhoneNumbers = "";
        }
        return transPhoneNumbers;
    }

    public boolean isAgeCalculated() {
        return ageCalculated;
    }

    public void setAgeCalculated(boolean ageCalculated) {
        this.ageCalculated = ageCalculated;
    }

    public String getLocalReferanceNo() {
        return localReferanceNo;
    }

    public void setLocalReferanceNo(String localReferanceNo) {
        this.localReferanceNo = localReferanceNo;
    }

    public String getSsNumber() {
        return ssNumber;
    }

    public void setSsNumber(String ssNumber) {
        this.ssNumber = ssNumber;
    }

}

Other Java Classes

package lk.gov.health.phsp.pojcs;

import java.io.Serializable;
import java.util.Date;
import lk.gov.health.phsp.bean.CommonController;

/**
 *
 * @author buddhika
 */
public class ClientBasicData implements Serializable {

    private String phn;
    private String gnArea;
    private String createdInstitution;
    private String phone;
    private String name;
    private Date dataOfBirth;
    private Date createdAt;
    private String sex;
    private int ageInYears;
    private Long id;
    private String nic;
    private String address;

    public ClientBasicData() {
    }

    public ClientBasicData(String phn, String gnArea, String createdInstitution, Date dataOfBirth, String sex) {
        this.phn = phn;
        this.gnArea = gnArea;
        this.createdInstitution = createdInstitution;
        this.dataOfBirth = dataOfBirth;
        this.sex = sex;
    }

    public ClientBasicData(String phn, String gnArea, String createdInstitution, Date dataOfBirth, Date createdAt, String sex) {
        this.phn = phn;
        this.gnArea = gnArea;
        this.createdInstitution = createdInstitution;
        this.dataOfBirth = dataOfBirth;
        this.createdAt = createdAt;
        this.sex = sex;
    }
    
    public ClientBasicData(String phn,String name, String nic, String address, String phone, String gnArea, String createdInstitution, Date dataOfBirth, Date createdAt, String sex) {
        this.phn = phn;
        this.name = name;
        this.nic = nic;
        this.address = address;
        this.phone = phone;
        this.gnArea = gnArea;
        this.createdInstitution = createdInstitution;
        this.dataOfBirth = dataOfBirth;
        this.createdAt = createdAt;
        this.sex = sex;
    }

    public ClientBasicData(Long id, String phn, String gnArea, String createdInstitution, Date dataOfBirth, Date createdAt, String sex, String nic) {
        this.id = id;
        this.phn = phn;
        this.gnArea = gnArea;
        this.createdInstitution = createdInstitution;
        this.dataOfBirth = dataOfBirth;
        this.createdAt = createdAt;
        this.sex = sex;
        this.nic = nic;
    }
    
    public ClientBasicData(Long id, String phn, String gnArea, String createdInstitution, Date dataOfBirth, Date createdAt, String sex, String nic, String name) {
        this.id = id;
        this.phn = phn;
        this.gnArea = gnArea;
        this.createdInstitution = createdInstitution;
        this.dataOfBirth = dataOfBirth;
        this.createdAt = createdAt;
        this.sex = sex;
        this.nic = nic;
        this.name = name;
    }

    public ClientBasicData(Long id, String phn, String name, String sex, String nic, String phone, String address) {
        this.phn = phn;
        this.phone = phone;
        this.name = name;
        this.sex = sex;
        this.id = id;
        this.nic = nic;
        this.address = address;
    }

    
    public ClientBasicData(Long id, String phn, String name, String nic, String phone, String address) {
        this.phn = phn;
        this.phone = phone;
        this.name = name;
        this.id = id;
        this.nic = nic;
        this.address = address;
    }
    
    
    public String getPhn() {
        return phn;
    }

    public void setPhn(String phn) {
        this.phn = phn;
    }

    public String getGnArea() {
        return gnArea;
    }

    public void setGnArea(String gnArea) {
        this.gnArea = gnArea;
    }

    public String getCreatedInstitution() {
        return createdInstitution;
    }

    public void setCreatedInstitution(String createdInstitution) {
        this.createdInstitution = createdInstitution;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getDataOfBirth() {
        return dataOfBirth;
    }

    public void setDataOfBirth(Date dataOfBirth) {
        this.dataOfBirth = dataOfBirth;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAgeInYears() {
        ageInYears = CommonController.ageFromDob(dataOfBirth);
        return ageInYears;
    }

    public void setAgeInYears(int ageInYears) {
        this.ageInYears = ageInYears;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getNic() {
        return nic;
    }

    public void setNic(String nic) {
        this.nic = nic;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

}

Persistence.xml file

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="hmisPU">
        <description>cHIMS</description>
        <provider>com.objectdb.jpa.Provider</provider>
        <properties>
            <property name="javax.persistence.jdbc.url" value="objectdb://192.168.1.155/chims.odb"/>
            <property name="javax.persistence.jdbc.user" value="admin"/>
            <property name="javax.persistence.jdbc.password" value="admin"/>
            <property name="javax.persistence.schema-generation.database.action" value="create-or-extend-tables"/>
        </properties>
    </persistence-unit>
</persistence>

Method in Abstract Facade

public List<?> findLightsByJpql(String jpql, Map<String, Object> parameters) {
        Query qry = getEntityManager().createQuery(jpql);
        Set<Map.Entry<String, Object>> entries = parameters.entrySet();

        for (Map.Entry<String, Object> entry : entries) {
            String paramName = entry.getKey();
            Object paramValue = entry.getValue();

            if (paramValue instanceof Date) {
                qry.setParameter(paramName, (Date) paramValue, TemporalType.DATE);
            } else {
                qry.setParameter(paramName, paramValue);
            }
        }

        List<?> resultList;
        try {
            resultList = qry.getResultList();
        } catch (Exception e) {
            resultList = new ArrayList<>();
        }

        return resultList;
    }

You should be able to get the results in milliseconds rather than 3 seconds. However, you may have to adjust your object model to support these queries. Apparently you have no indexes defined (see this manual page regarding indexes) so a full scan of all ClientBasicData instances is needed. How many such objects do you have? What is the total space that they consume (check in the Explorer)?

I don't think you even bothered to even read my pos before replying. In the output, the exact number of miliseconds are clearly displayed. Please check the code above and you will see all the searchable attributs have been indexed. You keep on pointing to the documents and please note that I have been following these documents for about 7 years now. You do not try to uderstand the issue, instead try to pass the ball to me. The large size of the database (64 GB) may be the issue. I think there is an issue with the configuration. Can you please advice after reading that. Please refer to my first post on https://www.objectdb.com/forum/2857 for details.

Further to the above reply, the ClientBasicData is NOT an entity. Its just a java class to loads data. Its the Client and Person classes that are JPA entities. Thay are properly indexed.

> I don't think you even bothered to even read my pos before replying.

That is not true, but you are right about the indexing, ClientBasicData is not relevant and Client has indexes. That was an honest mistake. Such mistakes can be avoided in the future if you follow the posting instruction and post minimal examples (for example, if you isolated the issue then ClientBasicData, which is not relevant to the issue, would have been eliminated). Unfortunately all your posts today violate the posting rules and if you want to continue to use this free support service please follow the rules (see other recent posts from other users: shorter, focused and only with relevant details).

> In the output, the exact number of miliseconds are clearly displayed

I think there is a misunderstanding. The meaning of "You should be able to get the results in milliseconds rather than 3 seconds" was that the execution time should be much faster than 3 seconds, i.e. maybe less than 100ms. See the sentence after in #2, which is connected to the first sentence.

Unfortunately not everything can be solved just by changing the configuration, it is not always that simple.

More information is needed. How many Client and Person instances the database contains? How much spaces these classes consume (out of the 64GB, you can check it in the Explorer schema tab).

You should also run this query with logging enabled in the configuration and post the results:

<general>
 <logger name="*" level="info" /> 
 <logger name="query" level="debug" /> 
</general>

In addition, you should try (for diagnosis):

1. The same query but without all the lower expressions.
2. Similar queries but with fewer expression in the OR (try different combinations).
3. Adding index to the retired field in client.

This is the process of isolating the real issue, and it should always be done before posting on this forum.

If I have violated the rules, please forgive me. I have misunderstood the 3 seconds phrase and sorry for that. 

Below is the relevant log.

 [2023-09-23 00:13:10 #168 query.manager]
Query "select new lk.gov.health.phsp.pojcs.ClientBasicData(c.id, c.phn, c.person.name, c.person.nic, c.person.phone1, c.person.address )  from Client c  where c.retired=false  and  ( lower(c.phn)=:q  or  lower(c.person.localReferanceNo)=:q  or  lower(c.person.ssNumber)=:q  or  c.person.phone1=:q  or  c.person.phone2=:q  or  lower(c.person.nic)=:q  ) " => 7 results in 4254 milliseconds.

lder name="c.person.name" type="java.lang.String" />
    <SimpleResultBuilder name="c.person.nic" type="java.lang.String" />
    <SimpleResultBuilder name="c.person.phone1" type="java.lang.String" />
    <SimpleResultBuilder name="c.person.address" type="java.lang.String" />
  </ObjectResultBuilder>
</newQueryResponse>

="0.00/0.00" variable="v$1(Person)" binding="bound(c.person)" />
      </extractPlan>
    </filterPlan>
  </multiVarPlan>
</newQueryProgram>

   <filterPlan 8.2002 2.34/3.15 or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))>
      <extractPlan 7.9671 2.11/3.15 v$1(Person)>
        <btreePlan 7.9671 2.11/3.15 v$1(Person) type(Person[set]) />
      </extractPlan>
    </filterPlan>
  </multiVarPlan>
</finalPlans>

<sub-planning vars="v$1" filter="">
    <step vars="()+v$1" future="true">
      <btreePlan complexity="12.4799" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
      <btreePlan complexity="12.5432" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
      <btreePlan complexity="12.6099" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
      <btreePlan complexity="12.7666" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
      <btreePlan complexity="13.9478" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
      <btreePlan complexity="14.3187" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
      <btreePlan complexity="17.5757" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
    </step>
    <selected-plans>
      <btreePlan complexity="12.4799" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
      <btreePlan complexity="12.5432" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
      <btreePlan complexity="12.6099" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
      <btreePlan complexity="12.7666" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
      <btreePlan complexity="13.9478" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
      <btreePlan complexity="14.3187" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
      <btreePlan complexity="17.5757" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
    </selected-plans>
  </sub-planning>
  <sub-planning vars="c,v$1" filter="and(or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q)),(c.retired=false),(c.person join v$1))">
    <step vars="(v$1)+c" future="true">
      <multiVarPlan complexity="24.0843" eval="10.01/9.45" var="c,v$1">
        <extractPlan complexity="17.5757" eval="5.26/6.30" variable="v$1(Person)">
          <btreePlan complexity="17.5757" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
        </extractPlan>
        <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
          <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
            <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
              <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="20.9885" eval="10.01/9.45" var="c,v$1">
        <extractPlan complexity="14.4799" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.4799" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
        </extractPlan>
        <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
          <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
            <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
              <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="21.0517" eval="10.01/9.45" var="c,v$1">
        <extractPlan complexity="14.5432" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.5432" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
        </extractPlan>
        <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
          <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
            <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
              <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="21.1184" eval="10.01/9.45" var="c,v$1">
        <extractPlan complexity="14.6099" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.6099" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
        </extractPlan>
        <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
          <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
            <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
              <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="21.2752" eval="10.01/9.45" var="c,v$1">
        <extractPlan complexity="14.7666" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.7666" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
        </extractPlan>
        <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
          <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
            <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
              <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="22.4563" eval="10.01/9.45" var="c,v$1">
        <extractPlan complexity="15.9478" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="13.9478" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
        </extractPlan>
        <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
          <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
            <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
              <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="22.8272" eval="10.01/9.45" var="c,v$1">
        <extractPlan complexity="16.3187" eval="6.61/6.30" variable="v$1(Person)">
          <btreePlan complexity="14.3187" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
        </extractPlan>
        <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
          <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
            <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
              <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
            </extractPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="49.2849" eval="11.25/12.60" var="c,v$1">
        <extractPlan complexity="17.5757" eval="5.26/6.30" variable="v$1(Person)">
          <btreePlan complexity="17.5757" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
        </extractPlan>
        <filterPlan complexity="15.8546" eval="4.95/6.30" filter="(c.person join v$1)">
          <filterPlan complexity="15.8334" eval="4.93/6.30" filter="(c.retired=false)">
            <filterPlan complexity="15.8122" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="44.8145" eval="11.25/12.60" var="c,v$1">
        <extractPlan complexity="14.4799" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.4799" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
        </extractPlan>
        <filterPlan complexity="15.8546" eval="4.95/6.30" filter="(c.person join v$1)">
          <filterPlan complexity="15.8334" eval="4.93/6.30" filter="(c.retired=false)">
            <filterPlan complexity="15.8122" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="44.941" eval="11.25/12.60" var="c,v$1">
        <extractPlan complexity="14.5432" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.5432" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
        </extractPlan>
        <filterPlan complexity="15.8546" eval="4.95/6.30" filter="(c.person join v$1)">
          <filterPlan complexity="15.8334" eval="4.93/6.30" filter="(c.retired=false)">
            <filterPlan complexity="15.8122" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="45.0744" eval="11.25/12.60" var="c,v$1">
        <extractPlan complexity="14.6099" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.6099" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
        </extractPlan>
        <filterPlan complexity="15.8546" eval="4.95/6.30" filter="(c.person join v$1)">
          <filterPlan complexity="15.8334" eval="4.93/6.30" filter="(c.retired=false)">
            <filterPlan complexity="15.8122" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="45.3878" eval="11.25/12.60" var="c,v$1">
        <extractPlan complexity="14.7666" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="12.7666" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
        </extractPlan>
        <filterPlan complexity="15.8546" eval="4.95/6.30" filter="(c.person join v$1)">
          <filterPlan complexity="15.8334" eval="4.93/6.30" filter="(c.retired=false)">
            <filterPlan complexity="15.8122" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="47.6569" eval="11.25/12.60" var="c,v$1">
        <extractPlan complexity="15.9478" eval="6.60/6.30" variable="v$1(Person)">
          <btreePlan complexity="13.9478" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
        </extractPlan>
        <filterPlan complexity="15.8546" eval="4.95/6.30" filter="(c.person join v$1)">
          <filterPlan complexity="15.8334" eval="4.93/6.30" filter="(c.retired=false)">
            <filterPlan complexity="15.8122" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="48.0279" eval="11.25/12.60" var="c,v$1">
        <extractPlan complexity="16.3187" eval="6.61/6.30" variable="v$1(Person)">
          <btreePlan complexity="14.3187" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
        </extractPlan>
        <filterPlan complexity="15.8546" eval="4.95/6.30" filter="(c.person join v$1)">
          <filterPlan complexity="15.8334" eval="4.93/6.30" filter="(c.retired=false)">
            <filterPlan complexity="15.8122" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
          </filterPlan>
        </filterPlan>
      </multiVarPlan>
    </step>
    <step vars="(c)+v$1" future="true">
      <multiVarPlan complexity="20.0665" eval="7.01/6.30" var="c,v$1">
        <filterPlan complexity="15.6003" eval="4.70/6.30" filter="(c.retired=false)">
          <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
            <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
          </extractPlan>
        </filterPlan>
        <filterPlan complexity="2.2331" eval="0.71/0.00" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
          <extractPlan complexity="2.0" eval="0.48/0.00" variable="v$1(Person)">
            <boundPlan complexity="0.0" eval="0.00/0.00" variable="v$1(Person)" binding="bound(c.person)" />
          </extractPlan>
        </filterPlan>
      </multiVarPlan>
      <multiVarPlan complexity="32.0007" eval="8.65/9.45" var="c,v$1">
        <filterPlan complexity="15.6003" eval="4.70/6.30" filter="(c.retired=false)">
          <extractPlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)">
            <btreePlan complexity="15.5791" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
          </extractPlan>
        </filterPlan>
        <filterPlan complexity="8.2002" eval="2.34/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
          <extractPlan complexity="7.9671" eval="2.11/3.15" variable="v$1(Person)">
            <btreePlan complexity="7.9671" eval="2.11/3.15" variable="v$1(Person)" scope="type(Person[set])" />
          </extractPlan>
        </filterPlan>
      </multiVarPlan>
    </step>
    <selected-plans>
      <multiVarPlan complexity="20.0665" eval="7.01/6.30" var="c,v$1" />
      <multiVarPlan complexity="32.0007" eval="8.65/9.45" var="c,v$1" />
    </selected-plans>
  </sub-planning>
</query-planning>

eval="2.74/2.71" var="$$owner,$$inv">
    <extractPlan complexity="5.2" eval="1.00/2.71" variable="$$owner(Coordinate)">
      <btreePlan complexity="5.2" eval="1.00/2.71" variable="$$owner(Coordinate)" scope="type(Coordinate[all])" />
    </extractPlan>
    <filterPlan complexity="0.0212" eval="0.02/0.00" filter="contains(:1,$$inv)">
      <boundPlan complexity="0.0" eval="0.00/0.00" variable="$$inv(Area)" binding="bound($$owner.area)" />
    </filterPlan>
  </multiVarPlan>
</cachedQueryProgram>
 

The log XML seems broken. Please post it as an attachment.

Please find as a text file.

You should have sent a log file with only one query execution, which matches exactly post #1 on this thread. It seems that this log file has many query executions (21?). It is unclear where the relevant query execution in the log file if it is here at all. You must start posting all the relevant information for an issue but nothing more.

We should probably stop this thread now and start again with a clean thread and a minimal runnable example. See posting instructions and examples of other recent threads. Post again after isolating an issue to the minimum possible (e.g. no irrelevant get/set methods, result classes, etc.).

What I have posted in the post directly is the log related only to the query. Then you said it incomplete. That is why I posted the whole log as an attachment. I will again get the log and post here. 

Please find the debug log related to the query. Hope you will attempt to explore what is the issue.

I have atteched the same as a txt file as well

[2023-09-23 22:46:58 #278 query.manager]
<queryRequest query="select new lk.gov.health.phsp.pojcs.ClientBasicData(c.id, c.phn, c.person.name, c.person.nic, c.person.phone1, c.person.address )  from Client c  where c.retired=false  and  ( lower(c.phn)=:q  or  lower(c.person.localReferanceNo)=:q  or  lower(c.person.ssNumber)=:q  or  c.person.phone1=:q  or  c.person.phone2=:q  or  lower(c.person.nic)=:q  ) " args="{q->0715812399}" transactionId="-1" />

[2023-09-23 22:46:58 #279 query.tokenizer]
<tokenList>
  <token name="select" code="132" postion="0:6" />
  <token name="new" code="122" postion="7:10" />
  <token name="lk" code="1" postion="11:13" />
  <token name="." code="6" postion="13:14" />
  <token name="gov" code="1" postion="14:17" />
  <token name="." code="6" postion="17:18" />
  <token name="health" code="1" postion="18:24" />
  <token name="." code="6" postion="24:25" />
  <token name="phsp" code="1" postion="25:29" />
  <token name="." code="6" postion="29:30" />
  <token name="pojcs" code="1" postion="30:35" />
  <token name="." code="6" postion="35:36" />
  <token name="ClientBasicData" code="1" postion="36:51" />
  <token name="(" code="14" postion="51:52" />
  <token name="c" code="1" postion="52:53" />
  <token name="." code="6" postion="53:54" />
  <token name="id" code="1" postion="54:56" />
  <token name="," code="9" postion="56:57" />
  <token name="c" code="1" postion="58:59" />
  <token name="." code="6" postion="59:60" />
  <token name="phn" code="1" postion="60:63" />
  <token name="," code="9" postion="63:64" />
  <token name="c" code="1" postion="65:66" />
  <token name="." code="6" postion="66:67" />
  <token name="person" code="1" postion="67:73" />
  <token name="." code="6" postion="73:74" />
  <token name="name" code="1" postion="74:78" />
  <token name="," code="9" postion="78:79" />
  <token name="c" code="1" postion="80:81" />
  <token name="." code="6" postion="81:82" />
  <token name="person" code="1" postion="82:88" />
  <token name="." code="6" postion="88:89" />
  <token name="nic" code="1" postion="89:92" />
  <token name="," code="9" postion="92:93" />
  <token name="c" code="1" postion="94:95" />
  <token name="." code="6" postion="95:96" />
  <token name="person" code="1" postion="96:102" />
  <token name="." code="6" postion="102:103" />
  <token name="phone1" code="1" postion="103:109" />
  <token name="," code="9" postion="109:110" />
  <token name="c" code="1" postion="111:112" />
  <token name="." code="6" postion="112:113" />
  <token name="person" code="1" postion="113:119" />
  <token name="." code="6" postion="119:120" />
  <token name="address" code="1" postion="120:127" />
  <token name=")" code="15" postion="128:129" />
  <token name="from" code="103" postion="131:135" />
  <token name="Client" code="1" postion="136:142" />
  <token name="c" code="1" postion="143:144" />
  <token name="where" code="150" postion="146:151" />
  <token name="c" code="1" postion="152:153" />
  <token name="." code="6" postion="153:154" />
  <token name="retired" code="1" postion="154:161" />
  <token name="=" code="21" postion="161:162" />
  <token name="false" code="2" postion="162:167" value="false" type="boolean" />
  <token name="and" code="73" postion="169:172" />
  <token name="(" code="14" postion="174:175" />
  <token name="lower" code="117" postion="176:181" />
  <token name="(" code="14" postion="181:182" />
  <token name="c" code="1" postion="182:183" />
  <token name="." code="6" postion="183:184" />
  <token name="phn" code="1" postion="184:187" />
  <token name=")" code="15" postion="187:188" />
  <token name="=" code="21" postion="188:189" />
  <token name=":" code="7" postion="189:190" />
  <token name="q" code="1" postion="190:191" />
  <token name="or" code="128" postion="193:195" />
  <token name="lower" code="117" postion="197:202" />
  <token name="(" code="14" postion="202:203" />
  <token name="c" code="1" postion="203:204" />
  <token name="." code="6" postion="204:205" />
  <token name="person" code="1" postion="205:211" />
  <token name="." code="6" postion="211:212" />
  <token name="localReferanceNo" code="1" postion="212:228" />
  <token name=")" code="15" postion="228:229" />
  <token name="=" code="21" postion="229:230" />
  <token name=":" code="7" postion="230:231" />
  <token name="q" code="1" postion="231:232" />
  <token name="or" code="128" postion="234:236" />
  <token name="lower" code="117" postion="238:243" />
  <token name="(" code="14" postion="243:244" />
  <token name="c" code="1" postion="244:245" />
  <token name="." code="6" postion="245:246" />
  <token name="person" code="1" postion="246:252" />
  <token name="." code="6" postion="252:253" />
  <token name="ssNumber" code="1" postion="253:261" />
  <token name=")" code="15" postion="261:262" />
  <token name="=" code="21" postion="262:263" />
  <token name=":" code="7" postion="263:264" />
  <token name="q" code="1" postion="264:265" />
  <token name="or" code="128" postion="267:269" />
  <token name="c" code="1" postion="271:272" />
  <token name="." code="6" postion="272:273" />
  <token name="person" code="1" postion="273:279" />
  <token name="." code="6" postion="279:280" />
  <token name="phone1" code="1" postion="280:286" />
  <token name="=" code="21" postion="286:287" />
  <token name=":" code="7" postion="287:288" />
  <token name="q" code="1" postion="288:289" />
  <token name="or" code="128" postion="291:293" />
  <token name="c" code="1" postion="295:296" />
  <token name="." code="6" postion="296:297" />
  <token name="person" code="1" postion="297:303" />
  <token name="." code="6" postion="303:304" />
  <token name="phone2" code="1" postion="304:310" />
  <token name="=" code="21" postion="310:311" />
  <token name=":" code="7" postion="311:312" />
  <token name="q" code="1" postion="312:313" />
  <token name="or" code="128" postion="315:317" />
  <token name="lower" code="117" postion="319:324" />
  <token name="(" code="14" postion="324:325" />
  <token name="c" code="1" postion="325:326" />
  <token name="." code="6" postion="326:327" />
  <token name="person" code="1" postion="327:333" />
  <token name="." code="6" postion="333:334" />
  <token name="nic" code="1" postion="334:337" />
  <token name=")" code="15" postion="337:338" />
  <token name="=" code="21" postion="338:339" />
  <token name=":" code="7" postion="339:340" />
  <token name="q" code="1" postion="340:341" />
  <token name=")" code="15" postion="343:344" />
</tokenList>

[2023-09-23 22:46:58 #280 query.tokenizer]
<token name="QUERY" code="67" postion="0:0">
  <token name="select" code="132" postion="0:6">
        <token name="new" code="122" postion="7:10">
          <token name="." code="6" postion="35:36">
            <token name="." code="6" postion="29:30">
              <token name="." code="6" postion="24:25">
                <token name="." code="6" postion="17:18">
                  <token name="." code="6" postion="13:14">
                    <token name="lk" code="1" postion="11:13" />
                    <token name="gov" code="1" postion="14:17" />
                  </token>
                  <token name="health" code="1" postion="18:24" />
                </token>
                <token name="phsp" code="1" postion="25:29" />
              </token>
              <token name="pojcs" code="1" postion="30:35" />
            </token>
            <token name="ClientBasicData" code="1" postion="36:51" />
            <token name="(" code="14" postion="51:52">
              <token name="." code="6" postion="53:54">
                <token name="c" code="1" postion="52:53" />
                <token name="id" code="1" postion="54:56" />
              </token>
              <token name="." code="6" postion="59:60">
                <token name="c" code="1" postion="58:59" />
                <token name="phn" code="1" postion="60:63" />
              </token>
              <token name="." code="6" postion="73:74">
                <token name="." code="6" postion="66:67">
                  <token name="c" code="1" postion="65:66" />
                  <token name="person" code="1" postion="67:73" />
                </token>
                <token name="name" code="1" postion="74:78" />
              </token>
              <token name="." code="6" postion="88:89">
                <token name="." code="6" postion="81:82">
                  <token name="c" code="1" postion="80:81" />
                  <token name="person" code="1" postion="82:88" />
                </token>
                <token name="nic" code="1" postion="89:92" />
              </token>
              <token name="." code="6" postion="102:103">
                <token name="." code="6" postion="95:96">
                  <token name="c" code="1" postion="94:95" />
                  <token name="person" code="1" postion="96:102" />
                </token>
                <token name="phone1" code="1" postion="103:109" />
              </token>
              <token name="." code="6" postion="119:120">
                <token name="." code="6" postion="112:113">
                  <token name="c" code="1" postion="111:112" />
                  <token name="person" code="1" postion="113:119" />
                </token>
                <token name="address" code="1" postion="120:127" />
              </token>
              <token name=")" code="15" postion="128:129" />
            </token>
          </token>
        </token>
  </token>
  <token name="from" code="103" postion="131:135">
        <token name="c" code="1" postion="143:144">
          <token name="Client" code="1" postion="136:142" />
        </token>
  </token>
  <token name="where" code="150" postion="146:151">
        <token name="and" code="73" postion="169:172">
          <token name="=" code="21" postion="161:162">
            <token name="." code="6" postion="153:154">
              <token name="c" code="1" postion="152:153" />
              <token name="retired" code="1" postion="154:161" />
            </token>
            <token name="false" code="2" postion="162:167" value="false" type="boolean" />
          </token>
          <token name="or" code="128" postion="315:317">
            <token name="or" code="128" postion="291:293">
              <token name="or" code="128" postion="267:269">
                <token name="or" code="128" postion="234:236">
                  <token name="or" code="128" postion="193:195">
                    <token name="=" code="21" postion="188:189">
                      <token name="lower" code="117" postion="176:181">
                        <token name="(" code="14" postion="181:182">
                          <token name="." code="6" postion="183:184">
                            <token name="c" code="1" postion="182:183" />
                            <token name="phn" code="1" postion="184:187" />
                          </token>
                          <token name=")" code="15" postion="187:188" />
                        </token>
                      </token>
                      <token name=":" code="7" postion="189:190">
                        <token name="q" code="1" postion="190:191" />
                      </token>
                    </token>
                    <token name="=" code="21" postion="229:230">
                      <token name="lower" code="117" postion="197:202">
                        <token name="(" code="14" postion="202:203">
                          <token name="." code="6" postion="211:212">
                            <token name="." code="6" postion="204:205">
                              <token name="c" code="1" postion="203:204" />
                              <token name="person" code="1" postion="205:211" />
                            </token>
                            <token name="localReferanceNo" code="1" postion="212:228" />
                          </token>
                          <token name=")" code="15" postion="228:229" />
                        </token>
                      </token>
                      <token name=":" code="7" postion="230:231">
                        <token name="q" code="1" postion="231:232" />
                      </token>
                    </token>
                  </token>
                  <token name="=" code="21" postion="262:263">
                    <token name="lower" code="117" postion="238:243">
                      <token name="(" code="14" postion="243:244">
                        <token name="." code="6" postion="252:253">
                          <token name="." code="6" postion="245:246">
                            <token name="c" code="1" postion="244:245" />
                            <token name="person" code="1" postion="246:252" />
                          </token>
                          <token name="ssNumber" code="1" postion="253:261" />
                        </token>
                        <token name=")" code="15" postion="261:262" />
                      </token>
                    </token>
                    <token name=":" code="7" postion="263:264">
                      <token name="q" code="1" postion="264:265" />
                    </token>
                  </token>
                </token>
                <token name="=" code="21" postion="286:287">
                  <token name="." code="6" postion="279:280">
                    <token name="." code="6" postion="272:273">
                      <token name="c" code="1" postion="271:272" />
                      <token name="person" code="1" postion="273:279" />
                    </token>
                    <token name="phone1" code="1" postion="280:286" />
                  </token>
                  <token name=":" code="7" postion="287:288">
                    <token name="q" code="1" postion="288:289" />
                  </token>
                </token>
              </token>
              <token name="=" code="21" postion="310:311">
                <token name="." code="6" postion="303:304">
                  <token name="." code="6" postion="296:297">
                    <token name="c" code="1" postion="295:296" />
                    <token name="person" code="1" postion="297:303" />
                  </token>
                  <token name="phone2" code="1" postion="304:310" />
                </token>
                <token name=":" code="7" postion="311:312">
                  <token name="q" code="1" postion="312:313" />
                </token>
              </token>
            </token>
            <token name="=" code="21" postion="338:339">
              <token name="lower" code="117" postion="319:324">
                <token name="(" code="14" postion="324:325">
                  <token name="." code="6" postion="333:334">
                    <token name="." code="6" postion="326:327">
                      <token name="c" code="1" postion="325:326" />
                      <token name="person" code="1" postion="327:333" />
                    </token>
                    <token name="nic" code="1" postion="334:337" />
                  </token>
                  <token name=")" code="15" postion="337:338" />
                </token>
              </token>
              <token name=":" code="7" postion="339:340">
                <token name="q" code="1" postion="340:341" />
              </token>
            </token>
            <token name=")" code="15" postion="343:344" />
          </token>
        </token>
  </token>
</token>

[2023-09-23 22:46:58 #281 query.compiler]
<queryCompilation query="select new lk.gov.health.phsp.pojcs.ClientBasicData(c.id, c.phn, c.person.name, c.person.nic, c.person.phone1, c.person.address )  from Client c  where c.retired=false  and  ( lower(c.phn)=:q  or  lower(c.person.localReferanceNo)=:q  or  lower(c.person.ssNumber)=:q  or  c.person.phone1=:q  or  c.person.phone2=:q  or  lower(c.person.nic)=:q  ) ">
  <symbolManager>
        <parameters>
          <q type="java.lang.String" />
        </parameters>
        <variables>
          <c varId="0" type="lk.gov.health.phsp.entity.Client" subtypes="include" typeId="12" binding="null" nav="111">
            <keys>
              <secondary ix="0" name="-120" nav="100" />
              <secondary ix="1" name="-121" nav="10" />
            </keys>
            <navigations>
              <id member="id" memberType="java.lang.Long" id="8" keyPos="0" />
              <person member="person" memberType="lk.gov.health.phsp.entity.Person" id="2" />
              <phn member="phn" memberType="java.lang.String" id="1" />
              <retired member="retired" memberType="boolean" id="0" />
            </navigations>
          </c>
          <v$1 varId="1" type="lk.gov.health.phsp.entity.Person" subtypes="include" typeId="7" binding="c.person" nav="11011111000">
            <keys>
              <secondary ix="0" name="-112" nav="0" />
              <secondary ix="1" name="-114" nav="1000" />
              <secondary ix="2" name="-126" nav="1000000000" />
              <secondary ix="3" name="-115" nav="10000000" />
              <secondary ix="4" name="-116" nav="0" />
              <secondary ix="5" name="-117" nav="100000" />
              <secondary ix="6" name="-118" nav="1000000" />
              <secondary ix="7" name="-119" nav="10000" />
            </keys>
            <navigations>
              <ssNumber member="ssNumber" memberType="java.lang.String" id="4" />
              <localReferanceNo member="localReferanceNo" memberType="java.lang.String" id="3" />
              <nic member="nic" memberType="java.lang.String" id="7" />
              <name member="name" memberType="java.lang.String" id="9" />
              <phone2 member="phone2" memberType="java.lang.String" id="6" />
              <address member="address" memberType="java.lang.String" id="10" />
              <phone1 member="phone1" memberType="java.lang.String" id="5" />
            </navigations>
          </v$1>
        </variables>
  </symbolManager>
  <queryCompData>
        <query exp="query(where(and(or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q)),(c.retired=false),(c.person join v$1))),select(c.id,c.phn,v$1.name,v$1.nic,v$1.phone1,v$1.address))" var="c,v$1" directVar="c,v$1" ext="11111111111">
          <where exp="where(and(or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q)),(c.retired=false),(c.person join v$1)))" var="c,v$1" directVar="c,v$1" ext="11111111">
            <and exp="and(or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q)),(c.retired=false),(c.person join v$1))" type="boolean" var="c,v$1" directVar="c,v$1" ext="11111111">
              <or exp="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))" type="boolean" var="c,v$1" directVar="c,v$1" ext="11111010">
                <equals exp="(toLower(v$1.nic)=:q)" type="boolean" var="v$1" directVar="v$1" ext="10000000">
                  <toLower exp="toLower(v$1.nic)" type="java.lang.String" var="v$1" directVar="v$1" ext="10000000">
                    <member exp="v$1.nic" type="java.lang.String" var="v$1" directVar="v$1" ext="10000000" memberName="nic" />
                  </toLower>
                  <parameter exp=":q" type="java.lang.String" var="" directVar="" ext="0" />
                </equals>
                <equals exp="(v$1.phone2=:q)" type="boolean" var="v$1" directVar="v$1" ext="1000000">
                  <member exp="v$1.phone2" type="java.lang.String" var="v$1" directVar="v$1" ext="1000000" memberName="phone2" />
                  <parameter exp=":q" type="java.lang.String" var="" directVar="" ext="0" />
                </equals>
                <equals exp="(v$1.phone1=:q)" type="boolean" var="v$1" directVar="v$1" ext="100000">
                  <member exp="v$1.phone1" type="java.lang.String" var="v$1" directVar="v$1" ext="100000" memberName="phone1" />
                  <parameter exp=":q" type="java.lang.String" var="" directVar="" ext="0" />
                </equals>
                <equals exp="(toLower(v$1.ssNumber)=:q)" type="boolean" var="v$1" directVar="v$1" ext="10000">
                  <toLower exp="toLower(v$1.ssNumber)" type="java.lang.String" var="v$1" directVar="v$1" ext="10000">
                    <member exp="v$1.ssNumber" type="java.lang.String" var="v$1" directVar="v$1" ext="10000" memberName="ssNumber" />
                  </toLower>
                  <parameter exp=":q" type="java.lang.String" var="" directVar="" ext="0" />
                </equals>
                <equals exp="(toLower(v$1.localReferanceNo)=:q)" type="boolean" var="v$1" directVar="v$1" ext="1000">
                  <toLower exp="toLower(v$1.localReferanceNo)" type="java.lang.String" var="v$1" directVar="v$1" ext="1000">
                    <member exp="v$1.localReferanceNo" type="java.lang.String" var="v$1" directVar="v$1" ext="1000" memberName="localReferanceNo" />
                  </toLower>
                  <parameter exp=":q" type="java.lang.String" var="" directVar="" ext="0" />
                </equals>
                <equals exp="(toLower(c.phn)=:q)" type="boolean" var="c" directVar="c" ext="10">
                  <toLower exp="toLower(c.phn)" type="java.lang.String" var="c" directVar="c" ext="10">
                    <member exp="c.phn" type="java.lang.String" var="c" directVar="c" ext="10" memberName="phn" />
                  </toLower>
                  <parameter exp=":q" type="java.lang.String" var="" directVar="" ext="0" />
                </equals>
              </or>
              <equals exp="(c.retired=false)" type="boolean" var="c" directVar="c" ext="1">
                <member exp="c.retired" type="boolean" var="c" directVar="c" ext="1" memberName="retired" />
                <literal exp="false" type="boolean" var="" directVar="" ext="0" />
              </equals>
              <join exp="(c.person join v$1)" type="boolean" var="c,v$1" directVar="c,v$1" ext="100">
                <member exp="c.person" type="lk.gov.health.phsp.entity.Person" var="c" directVar="c" ext="100" memberName="person" />
                <variable exp="v$1" type="lk.gov.health.phsp.entity.Person" var="v$1" directVar="v$1" ext="0" varId="1" />
              </join>
            </and>
          </where>
          <select exp="select(c.id,c.phn,v$1.name,v$1.nic,v$1.phone1,v$1.address)" var="c,v$1" directVar="c,v$1" ext="11110100010">
            <member exp="c.id" type="java.lang.Long" var="c" directVar="c" ext="100000000" memberName="id" />
            <member exp="c.phn" type="java.lang.String" var="c" directVar="c" ext="10" memberName="phn" />
            <member exp="v$1.name" type="java.lang.String" var="v$1" directVar="v$1" ext="1000000000" memberName="name" />
            <member exp="v$1.nic" type="java.lang.String" var="v$1" directVar="v$1" ext="10000000" memberName="nic" />
            <member exp="v$1.phone1" type="java.lang.String" var="v$1" directVar="v$1" ext="100000" memberName="phone1" />
            <member exp="v$1.address" type="java.lang.String" var="v$1" directVar="v$1" ext="10000000000" memberName="address" />
          </select>
        </query>
        <globals globalVariables="c,v$1" globalNavigations="11110100010" />
  </queryCompData>
</queryCompilation>

[2023-09-23 22:46:58 #282 query.compiler]
<query-planning>
  <sub-planning vars="c" filter="(c.retired=false)">
        <step vars="()+c" future="true">
          <filterPlan complexity="15.6015" eval="4.70/6.30" filter="(c.retired=false)">
            <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
              <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
            </extractPlan>
          </filterPlan>
          <filterPlan complexity="15.7525" eval="6.63/6.30" filter="(c.retired=false)">
            <extractPlan complexity="15.7313" eval="6.60/6.30" variable="c(Client)">
              <btreePlan complexity="13.7313" eval="4.11/6.30" variable="c(Client)" scope="index(-120[all])" />
            </extractPlan>
          </filterPlan>
          <filterPlan complexity="16.8123" eval="6.63/6.30" filter="(c.retired=false)">
            <extractPlan complexity="16.7911" eval="6.61/6.30" variable="c(Client)">
              <btreePlan complexity="14.7911" eval="4.44/6.30" variable="c(Client)" scope="index(-121[all])" />
            </extractPlan>
          </filterPlan>
        </step>
        <selected-plans>
          <filterPlan complexity="15.6015" eval="4.70/6.30" filter="(c.retired=false)" />
        </selected-plans>
  </sub-planning>
  <sub-planning vars="v$1" filter="">
        <step vars="()+v$1" future="true">
          <btreePlan complexity="12.4831" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
          <btreePlan complexity="12.5457" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
          <btreePlan complexity="12.6125" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
          <btreePlan complexity="12.7688" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
          <btreePlan complexity="13.9482" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
          <btreePlan complexity="14.3198" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
          <btreePlan complexity="14.745" eval="4.42/6.30" variable="v$1(Person)" scope="index(-126[all])" />
          <btreePlan complexity="17.5762" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
        </step>
        <selected-plans>
          <btreePlan complexity="12.4831" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
          <btreePlan complexity="12.5457" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
          <btreePlan complexity="12.6125" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
          <btreePlan complexity="12.7688" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
          <btreePlan complexity="13.9482" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
          <btreePlan complexity="14.3198" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
          <btreePlan complexity="14.745" eval="4.42/6.30" variable="v$1(Person)" scope="index(-126[all])" />
          <btreePlan complexity="17.5762" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
        </selected-plans>
  </sub-planning>
  <sub-planning vars="c,v$1" filter="and(or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q)),(c.retired=false),(c.person join v$1))">
        <step vars="(v$1)+c" future="true">
          <multiVarPlan complexity="24.0847" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="17.5762" eval="5.26/6.30" variable="v$1(Person)">
              <btreePlan complexity="17.5762" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="20.9916" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="14.4831" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.4831" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="21.0542" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="14.5457" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.5457" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="21.1211" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="14.6125" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.6125" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="21.2773" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="14.7688" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.7688" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="22.4568" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="15.9482" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="13.9482" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="22.8284" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="16.3198" eval="6.61/6.30" variable="v$1(Person)">
              <btreePlan complexity="14.3198" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="23.2535" eval="10.01/9.45" var="c,v$1">
            <extractPlan complexity="16.745" eval="6.61/6.30" variable="v$1(Person)">
              <btreePlan complexity="14.745" eval="4.42/6.30" variable="v$1(Person)" scope="index(-126[all])" />
            </extractPlan>
            <filterPlan complexity="3.2543" eval="3.71/3.15" filter="(c.retired=false)">
              <filterPlan complexity="3.2331" eval="3.69/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                <extractPlan complexity="3.0" eval="3.45/3.15" variable="c(Client)">
                  <btreePlan complexity="1.0" eval="0.90/3.15" variable="c(Client)" scope="index(-120[v$1->v$1])" />
                </extractPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="49.2877" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="17.5762" eval="5.26/6.30" variable="v$1(Person)">
              <btreePlan complexity="17.5762" eval="5.26/6.30" variable="v$1(Person)" scope="type(Person[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="44.822" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="14.4831" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.4831" eval="3.70/6.30" variable="v$1(Person)" scope="index(-116[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="44.9472" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="14.5457" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.5457" eval="3.72/6.30" variable="v$1(Person)" scope="index(-119[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="45.0808" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="14.6125" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.6125" eval="3.75/6.30" variable="v$1(Person)" scope="index(-118[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="45.3933" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="14.7688" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="12.7688" eval="3.80/6.30" variable="v$1(Person)" scope="index(-114[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="47.6597" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="15.9482" eval="6.60/6.30" variable="v$1(Person)">
              <btreePlan complexity="13.9482" eval="4.17/6.30" variable="v$1(Person)" scope="index(-117[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="48.0314" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="16.3198" eval="6.61/6.30" variable="v$1(Person)">
              <btreePlan complexity="14.3198" eval="4.29/6.30" variable="v$1(Person)" scope="index(-115[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="48.4565" eval="11.25/12.60" var="c,v$1">
            <extractPlan complexity="16.745" eval="6.61/6.30" variable="v$1(Person)">
              <btreePlan complexity="14.745" eval="4.42/6.30" variable="v$1(Person)" scope="index(-126[all])" />
            </extractPlan>
            <filterPlan complexity="15.8558" eval="4.95/6.30" filter="(c.person join v$1)">
              <filterPlan complexity="15.8346" eval="4.93/6.30" filter="(c.retired=false)">
                <filterPlan complexity="15.8134" eval="4.91/6.30" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
                  <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                    <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
                  </extractPlan>
                </filterPlan>
              </filterPlan>
            </filterPlan>
          </multiVarPlan>
        </step>
        <step vars="(c)+v$1" future="true">
          <multiVarPlan complexity="20.0677" eval="7.01/6.30" var="c,v$1">
            <filterPlan complexity="15.6015" eval="4.70/6.30" filter="(c.retired=false)">
              <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
            <filterPlan complexity="2.2331" eval="0.71/0.00" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="2.0" eval="0.48/0.00" variable="v$1(Person)">
                <boundPlan complexity="0.0" eval="0.00/0.00" variable="v$1(Person)" binding="bound(c.person)" />
              </extractPlan>
            </filterPlan>
          </multiVarPlan>
          <multiVarPlan complexity="32.0022" eval="8.65/9.45" var="c,v$1">
            <filterPlan complexity="15.6015" eval="4.70/6.30" filter="(c.retired=false)">
              <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
                <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
              </extractPlan>
            </filterPlan>
            <filterPlan complexity="8.2004" eval="2.34/3.15" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
              <extractPlan complexity="7.9673" eval="2.11/3.15" variable="v$1(Person)">
                <btreePlan complexity="7.9673" eval="2.11/3.15" variable="v$1(Person)" scope="type(Person[set])" />
              </extractPlan>
            </filterPlan>
          </multiVarPlan>
        </step>
        <selected-plans>
          <multiVarPlan complexity="20.0677" eval="7.01/6.30" var="c,v$1" />
          <multiVarPlan complexity="32.0022" eval="8.65/9.45" var="c,v$1" />
        </selected-plans>
  </sub-planning>
</query-planning>

[2023-09-23 22:46:58 #283 query.compiler]
<finalPlans>
  <multiVarPlan 20.0677 7.01/6.30 c,v$1>
        <filterPlan 15.6015 4.70/6.30 (c.retired=false)>
          <extractPlan 15.5803 4.68/6.30 c(Client)>
            <btreePlan 15.5803 4.68/6.30 c(Client) type(Client[all]) />
          </extractPlan>
        </filterPlan>
        <filterPlan 2.2331 0.71/0.00 or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))>
          <extractPlan 2.0 0.48/0.00 v$1(Person)>
            <boundPlan 0.0 0.00/0.00 v$1(Person) bound(c.person) />
          </extractPlan>
        </filterPlan>
  </multiVarPlan>
  <multiVarPlan 32.0022 8.65/9.45 c,v$1>
        <filterPlan 15.6015 4.70/6.30 (c.retired=false)>
          <extractPlan 15.5803 4.68/6.30 c(Client)>
            <btreePlan 15.5803 4.68/6.30 c(Client) type(Client[all]) />
          </extractPlan>
        </filterPlan>
        <filterPlan 8.2004 2.34/3.15 or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))>
          <extractPlan 7.9673 2.11/3.15 v$1(Person)>
            <btreePlan 7.9673 2.11/3.15 v$1(Person) type(Person[set]) />
          </extractPlan>
        </filterPlan>
  </multiVarPlan>
</finalPlans>

[2023-09-23 22:46:58 #284 query.manager]
<newQueryProgram>
  <multiVarPlan complexity="20.0677" eval="7.01/6.30" var="c,v$1">
        <filterPlan complexity="15.6015" eval="4.70/6.30" filter="(c.retired=false)">
          <extractPlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)">
            <btreePlan complexity="15.5803" eval="4.68/6.30" variable="c(Client)" scope="type(Client[all])" />
          </extractPlan>
        </filterPlan>
        <filterPlan complexity="2.2331" eval="0.71/0.00" filter="or((toLower(v$1.nic)=:q),(v$1.phone2=:q),(v$1.phone1=:q),(toLower(v$1.ssNumber)=:q),(toLower(v$1.localReferanceNo)=:q),(toLower(c.phn)=:q))">
          <extractPlan complexity="2.0" eval="0.48/0.00" variable="v$1(Person)">
            <boundPlan complexity="0.0" eval="0.00/0.00" variable="v$1(Person)" binding="bound(c.person)" />
          </extractPlan>
        </filterPlan>
  </multiVarPlan>
</newQueryProgram>

[2023-09-23 22:47:01 #285 query.manager]
<newQueryResponse isUnique="false" resultSize="7" fetchSize="0">
  <ObjectResultBuilder name="new lk.gov.health.phsp.pojcs.ClientBasicData(c.id, c.phn, c.person.name, c.person.nic, c.person.phone1, c.person.address )" type="java.lang.Object">
        <SimpleResultBuilder name="c.id" type="java.lang.Long" />
        <SimpleResultBuilder name="c.phn" type="java.lang.String" />
        <SimpleResultBuilder name="c.person.name" type="java.lang.String" />
        <SimpleResultBuilder name="c.person.nic" type="java.lang.String" />
        <SimpleResultBuilder name="c.person.phone1" type="java.lang.String" />
        <SimpleResultBuilder name="c.person.address" type="java.lang.String" />
  </ObjectResultBuilder>
</newQueryResponse>