Looking for the optimization of the below code

Question

I have found below code buggy as it degrades the performance of extjs3 grid, i am looking for possibilities of optimization at query or code level, as per my analysis, if we extract out the query there are two nested inner queries which are responding slow, in addition, the code inside while loop trying to find the unique id, can't we have distinct in query, or joins rather than inner queries.

Please suggest me the best practice to follow in order to achieve optimization.

public boolean isSCACreditOverviewGridVisible(String sessionId) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        boolean result = false;
        try {
            CommonUtility commUtil = new CommonUtility();

            List<String> hmIds = new ArrayList<String>();
            Map<String, String> tmStockMap = new TreeMap<String, String>();
            Set<String> setRecentCertificate = new HashSet<String>();

            String managerAccountId = sessionInfo.getMembershipAccount();

            String stockQuery = " select memberId , RootCertficateId from stockposition sp  where sp.stocktype = 'TR' and sp.memberId "
                + " IN  ( select hm2.accountId from "
                DATALINK
                + ".holdingmembers  hm2 "
                + " where hm2.holdingId = ( select holdingId from "
                DATALINK
                + ".holdingmembers  hm1 where hm1.accountId =  ? )) "
                + " order by sp.createdDate desc ";

            conn = getChildDBConnection();
            if (null != conn) {
                ps = conn.prepareStatement(stockQuery);
                ps.setString(1, managerAccountId);
                rs = ps.executeQuery();
                if (null != rs) {
                    while (rs.next()) {
                        String memberId = rs.getString("memberId");
                        String rootCertficateId = rs
                        .getString("RootCertficateId");
                        if (tmStockMap.containsKey(rootCertficateId)) {
                            continue;
                        }
                        hmIds.add(memberId);
                        tmStockMap.put(rootCertficateId, memberId);
                    }
                }
                rs.close();
                ps.close();

                if (null != hmIds && !hmIds.isEmpty()) {
                    String inIds = commUtil.getInStateParam(hmIds);
                    String mostRecentLicense = "Select RootCertificateId , memberaccountid  from "
                        + OctopusSchema.octopusSchema
                        + ".certificate c where  c.memberaccountid IN ("
                        + inIds
                        + ") and c.isrootcertificate=0 and c.certificationstatusid > 1 order by c.modifieddate desc";
                    ps = conn.prepareStatement(mostRecentLicense);
                    rs = ps.executeQuery();
                    if (null != rs) {
                        while (rs.next()) {
                            String rootCertficateId = rs
                            .getString("RootCertificateId");
                            String memberaccountid = rs
                            .getString("memberaccountid");
                            if (setRecentCertificate.contains(memberaccountid)) {
                                continue;
                            }
                            setRecentCertificate.add(memberaccountid);
                            if (tmStockMap.containsKey(rootCertficateId)) {
                                result = true;
                                break;
                            }
                        }
                    }
                    rs.close();
                    ps.close();
                } else {
                    result = false;
                }
            }
        } catch (Exception e) {
            LOGGER.error(e);
        } finally {
            closeDBReferences(conn, ps, null, rs);
        }
        return result;
    }

QUERY:

 select RootCertficateId,memberId from stockposition sp  where sp.stocktype = 'TR' and sp.memberId 
                  IN  ( select hm2.accountId from 
                DATALINK.holdingmembers  hm2 
                    where hm2.holdingId = ( select holdingId from 
                DATALINK.holdingmembers  hm1 where hm1.accountId =  '4937' )) 
                    order by sp.createdDate DESC;

I seriously wonder why people write raw SQL queries instead of using ORM (object relational mapping) frameworks such as Hibernate. Code complexity, maintenance costs and duplication is what leads to these aforementioned bugs/issues that you're facing right now. — Adithya Upadhya, Apr 06 '20 at 07:32
its a legacy code aditya, wrote way back 8 years ago, hence we need to dealing it as it is. — user9634982, Apr 06 '20 at 07:33
it wasn't me who downvoted your question. You've neither provided the schema of your tables, nor specified what this method is supposed to achieve. If you provide more information, folks might be able to help. — Adithya Upadhya, Apr 06 '20 at 07:36
give me some sample example, so I can frame my post in that way — user9634982, Apr 06 '20 at 07:49
@AdithyaUpadhya: because obfuscation layers like Hibernate generate crappy SQL and usually won't let you exploit the full capabilities of your database. — , Apr 06 '20 at 08:01
I agree with obfuscation however disagree with the "crappy" part. The queries generated by ORM engines are more than sufficient for >90% of the cases unless your use case is very specific. We have some modest servers handling high volume webservices powered by Spring-hibernate and Django. I would never write shit ton of raw queries just to exploit some micro-optimization. OP's use case looks more like a couple of joins and IN and ORMs are more than equipped to handle that. — Adithya Upadhya, Apr 06 '20 at 08:10

score 0 · Answer 1 · answered Apr 06 '20 at 07:26

0

One quick approach would be a substition of your IN by EXISTS. If your inner queryes return a lot of rows, it would be a lot more efficient. It depends if your subquery returns a lot of results.

SQL Server IN vs. EXISTS Performance

answered Apr 06 '20 at 07:26

Aitor

3,309
2
27
32

Kindly propose the solution by implement the same in the my exisiting snippet – user9634982 Apr 06 '20 at 07:27
I am looking for joins rather than exists – user9634982 Apr 06 '20 at 07:34
@user9634982 can you share the schema of your database and the exact use case here? We might be able to provide an optimized SQL query for this. – Adithya Upadhya Apr 06 '20 at 08:13

Looking for the optimization of the below code

1 Answers1