Optimizing a query using Stored Procedure

Question

I am optimizing a code written a long time ago by a developer that I never met. I came across a method that requires modification. The first thing that came to mind is to use stored procedure. Maybe there are better ways of achieving this hence this question. The code goes this way:

public void execute()
{
    String query = "select a, b, e from table1";
    ....
    ResultSet rs = stmt.executeQuery(query);
    String query2 = null;
    List<Integer> list1 = ....
    List<Integer> list2 = ....
    while(rs.next)
    {
        query2 = "select count(*) as rowcount from vw_view1 where f='" + rs.getString("a") + "' and d='" + rs.getString("b") + "'";
       .....
       ResultSet rs2 = stmt2.executeQuery(query2);
       list1.add(rs2.getInt(rowcount));

        query3 = "select count(*) as rowcount from vw_view1 where c='" + rs.getString("a") + "' and e='" + rs.getString("e") + "'";
       .....
       ResultSet rs3 = stmt3.executeQuery(query3);
       list2.add(rs3.getInt(rowcount));
    }
}

Apart from using a stored procedure, is there a better way of avoiding unnecessary trips to the database in this method.

SeraphimFoA · Accepted Answer · 2014-08-13 14:03:11Z

1

Try this query, check if it gives you the same result. With this you should have in one query directly all the value you wanted

SELECT c, 
SUM(CASE WHEN vw1.d = tb1.b THEN 1 ELSE 0  END) as rowcountListOne,
SUM(CASE WHEN vw1.e = tb1.e THEN 1 ELSE 0  END) as rowcountListTwo
from vw_view1 vw1
left join table1 tb1 on vw1.c=tb1.a
GROUP BY c

In the new case you posted you should actually join by c and f:

SELECT c, f,
SUM(CASE WHEN (vw1.d = tb1.b) AND (vw1.f=tb1.a) THEN 1 ELSE 0  END) as rowcountListOne,
SUM(CASE WHEN (vw1.e = tb1.e) AND (vw1.c=tb1.a) THEN 1 ELSE 0  END) as rowcountListTwo
FROM vw_view1 vw1
LEFT JOIN table1 tb1 on (vw1.c=tb1.a OR vw1.f=tb1.a)
GROUP BY c, f

You should check also what Lennart said about COUNT, I think I should add on those case some NULL management, but I can't test it right now.

Maybe is enough if you check vw1.f not null in the case (and vw1.c in the other):

CASE WHEN (vw1.f IS NOT NULL) AND (vw1.d = tb1.b) AND (vw1.f=tb1.a) THEN 1 ELSE 0  END

edited Aug 13, 2014 at 14:03

answered Aug 13, 2014 at 10:06

SeraphimFoA

4764 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lennart - Slava Ukraini Over a year ago

Side-note: you can even make it a bit shorter by using count instead of sum: COUNT(CASE WHEN vw1.d = tb1.b THEN 1 END). COUNT don't count null (not matched by case), for SUM you need an else clause to avoid null in case nothing matches.

Uchenna Nwanyanwu Over a year ago

I think this will be more efficient. I believe that the grouping on is is because the two queries in the while loop have a criteria dependent on c. What if the c in the first query in the loop is replaced by another parameter f as I have modified and the c in the second query in the loop remains the same, what will I group on to achieve the same result.

GarethD · Accepted Answer · 2014-08-13 10:04:04Z

0

You could just perform the counts at the same time as your initial select, removing the need to run two further queries for every row in rs:

SELECT  t.a, t.b, t.e, t1.RowCount1, t2.RowCount2
FROM    table1 AS t
        LEFT JOIN
        (   SELECT  c, d, COUNT(*) AS RowCount1
            FROM    vw_view1
            GROUP BY c, d
        ) AS t2
            ON t2.c = t.a
            AND t2.d = t.b
        LEFT JOIN
        (   SELECT  c, e, COUNT(*) AS RowCount2
            FROM    vw_view1
            GROUP BY c, e
        ) AS t3
            ON t3.c = t.a
            AND t3.e = t.e;

The initial select will be slower, but the subsequent loop will be much more efficient.

answered Aug 13, 2014 at 10:04

GarethD

70k11 gold badges90 silver badges130 bronze badges

Collectives™ on Stack Overflow

Optimizing a query using Stored Procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related