Too Slow to Load the JSP page

Question

im newbie in jsp jstl tag. i have a question, recently i build web application that can calculate outlet's total amount by revenue code. i make the query inside the jsp using jstl tag. the problem is my page take too long to load. i got about 60 outlet, 26 revenue code. 1 outlet can have at least 4 revenue code.

here is my code

in dashboard.jsp

 <sql:query var="outletView" dataSource="${datasource}">
                                    SELECT 
                                        outletid
                                    FROM 
                                        user_payment
                                   group by 
                                        outletid
                    </sql:query>
                                        <table class="table table-bordered table-striped table-hover">
                                            <thead>
                                                <th> Outlet ID </th>
                                                <th> Revenue Code </th>
                                                <th> Total Transaction </th>
                                                <th> Total Amount </th>
                                            </thead>

                                        <c:forEach items="${outletView.rows}" var="rowOutlet">
                                            <sql:query var="outletView" dataSource="${datasource}">
                                                SELECT 
                                                    revcode,
                                                    count(receiptnumbe) as Transactions,
                                                    sum(amount) as total
                                                FROM 
                                                    user_payment
                                                Where 
                                                    outletid='${rowOutlet.outletid}'
                                                Group by 
                                                    revcode

                                            </sql:query>




                                                    <c:forEach items="${outletView.rows}" var="displayrevcode">
                                                        <tr>
                                                            <td>${rowOutlet.outletid}</td>
                                                            <td>${displayrevcode.revcode}</td>
                                                            <td>${displayrevcode.Transactions}</td>
                                                            <td>${displayrevcode.total}</td>
                                                        </tr>

                                                    </c:forEach>


                                        </c:forEach>

can make it faster? i have to fetch and calculate each outlet's revenue code amount...

Answer 1

Create a single select statement instead of n+1 (where n is the number of distinct outletid):

SELECT 
    outletid, 
    revcode,
    count(receiptnumbe) as Transactions,
    sum(amount) as total
FROM 
    user_payment
GROUP BY 
    outletid, revcode
ORDER BY 1

Answer 2

Multiple issues:

1. Running query from JSP (directly/indirectly using a tag) is a bad practice. Query only from service layer.
2. Looping through one query result and running another query depending on the outer loop is a very bad practice. It typically leads to very slow running queries in production environment with large data sets. Use joins to avoid such queries.
3. This is a report functionality. Reports are rarely live. Usually a report is run on previous day's data. As far as possible pre-process the data for reports. In data warehouse terminology create the cubes for the data. These queries run offline and keep the report data ready for using when the user accesses the report.