Query optimization in ColdFusion

Question

I created a code in ColdFusion to load data from Users and UsersTransactions tables and display it in a table. However, my queries is taking long time to run.

<!--- This return about 250 records --->
<cfquery name="getUsers" datasource="db">
    Select * From Users Where test = 5
</cfquery>

<!--- Loop to display the Users info --->
<cfloop query="getUsers">
    <cfset SpecialDebit = 0 />
    <cfset TotalDebit = 0 />
    <cfset SpecialCredit = 0 />
    <cfset TotalCredit = 0 />

    <!--- Loop to get Users Balance --->
    <cfquery name="getUsersTransactions" datasource="db">
        SELECT * FROM UsersTransactions , TransactionTypes, ChargeTypes 
        Where UsersTransactions.TransactionTypeID=TransactionTypes.TransactionTypeID 
        AND ChargeTypes.ChargeTypeID=UsersTransactions.ChargeTypeID  
        AND UsersTransactions.UserID=#getUsers.UserID#
    </cfquery>
    <cfloop query="getUsersTransactions">
        <cfif TransactionTypeID EQ "1"> <!--- This means it's a debit --->
            <cfif ChargeTypeID EQ "6"> <!-- This means its a special debit --->
                <cfset SpecialDebit += TransactionAmount />
            <cfelse>
                <cfset TotalDebit += TransactionAmount />
            </cfif>     
        </cfif>
        <cfif TransactionTypeID EQ "2"> <!--- This means it's a credit --->
            <cfif ChargeTypeID EQ "6"> <!-- This means its a special credit --->
                <cfset SpecialCredit += TransactionAmount />
            <cfelse>
                <cfset TotalCredit += TransactionAmount />
            </cfif>
        </cfif>
    </cfloop>
    <cfset UserSpecialBalance = SpecialDebit - SpecialCredit />
    <cfset UserBalance = TotalDebit - TotalCredit />

    <!--- Display User's data in a table with Column Special Balance and User Balance --->
    .
    .
    .

</cfloop>

What is taking long time is the loop for the "getUsersTransactions" query. Is there a way to make these queries run faster?

Update:

Data sample for one User:

User.UserID = 10

It will have, for example, 6 records in UsersTransactions

             Transaction
#   UserID   TypeID      ChargeTypeID    TransactionAmount
1   10       1           6               25
2   10       1           6               17
3   10       1           1               50
4   10       2           1               12
5   10       2           6               7
6   10       2           6               18

In this case I have:

• 2 "Special Debit" (records 1 and 2): SpecialDebit = 25+17 = 42
• 1 Normal Debit (record 3): TotalDebit = 50
• 1 Normal Credit (record 4): TotalCredit = 12
• 2 Special Credit (records 5 and 6): SpecialCredit = 7+18 = 25

Answer 1

My suggestions above essentially amounted to grabbing the data you need for all users in the query and then outputting those results instead of looping to rerun a query for a user. I've added a stab at using the query.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE users ( userid int, name varchar(10), test int ) ;
INSERT INTO users (userid, name, test)
VALUES 
    ( 1, 'Bill', 5 ) 
  , ( 2, 'Tex', 3 )
  , ( 3, 'Rufus', 5 )
  , ( 4, 'SilentBob', 5 )
  , ( 5, 'Jay', 5 )
;

CREATE TABLE UsersTransactions ( UserID int, TransactionTypeID int, ChargeTypeID int, TransactionAmount int ) ;
INSERT INTO UsersTransactions ( UserID, TransactionTypeID, ChargeTypeID, TransactionAmount )
VALUES 
    ( 1, 1, 6, 2 )  /* Special Debit */
  , ( 1, 1, 1, 5 )  /* Normal Debit */
  , ( 2, 1, 6, 20 )
  , ( 2, 1, 1, 20 )
  , ( 3, 1, 6, 30 )
  , ( 3, 1, 1, 30 )
  , ( 1, 2, 6, 5 )  /* Special Credit */
  , ( 1, 2, 1, 5 )  /* Special Credit */
  , ( 2, 2, 6, 20 )
  , ( 2, 2, 1, 20 )
  , ( 3, 2, 6, 20 )
  , ( 3, 2, 1, 20 )
  , ( 5, 1, 1, 500 )  /* Normal Debit */
;

CREATE TABLE TransactionTypes ( TransactionTypeID int, Description varchar(10) ) ;
INSERT INTO TransactionTypes VALUES ( 1, 'Debit' ), ( 2, 'Credit' ) ;

CREATE TABLE ChargeTypes ( ChargeTypeID int, Description varchar(10) ) ;
INSERT INTO ChargeTypes VALUES ( 1, 'Regular' ), ( 6, 'Special' ) ;

Initial Query Example: NOTE: This can be further optimized.

SELECT u.userid
  , u.name
  , SUM(CASE WHEN ut.TransactionTypeID = 1  AND ut.ChargeTypeID = 1 THEN ut.TransactionAmount END) AS NormalDebit
  , SUM(CASE WHEN ut.TransactionTypeID = 1  AND ut.ChargeTypeID = 6 THEN ut.TransactionAmount END) AS SpecialDebit
  , SUM(CASE WHEN ut.TransactionTypeID = 2  AND ut.ChargeTypeID = 1 THEN ut.TransactionAmount END) AS NormalCredit
  , SUM(CASE WHEN ut.TransactionTypeID = 2  AND ut.ChargeTypeID = 6 THEN ut.TransactionAmount END) AS SpecialCredit
  , SUM(CASE WHEN ut.TransactionTypeID = 1  THEN ut.TransactionAmount WHEN ut.TransactionTypeID = 2  THEN ut.TransactionAmount * -1 END) AS TotalAmount
FROM users u
LEFT OUTER JOIN UsersTransactions ut ON u.userid = ut.UserID 
INNER JOIN TransactionTypes tt ON ut.TransactionTypeID = tt.TransactionTypeID  
INNER JOIN ChargeTypes ct ON ut.ChargeTypeID = ct.ChargeTypeID
WHERE u.test = 5
GROUP BY u.userID
ORDER BY u.userID

Results:

| userid |      name | SpecialBalance | TotalBalance |
|--------|-----------|----------------|--------------|
|      1 |      Bill |             -3 |           -3 |
|      3 |     Rufus |             10 |           20 |
|      4 | SilentBob |              0 |            0 |
|      5 |       Jay |              0 |          500 |

This outputs the necessary fields for UserIDs 1, 3, 4 and 5 that are test=5 filtered. Since there doesn't appear to be any data coming from the tables TransactionTypes and ChargeTypes, those INNER JOINs can be removed to significantly speed up the query.

So when you get to CF, you can simplify the query. Try:

<cfquery name="getUsersTransactions" datasource="db">
    SELECT s1.userid
      , s1.name
      , ( s1.SpecialDebit - s1.SpecialCredit ) AS SpecialBalance
      , ( (s1.NormalDebit + s1.SpecialDebit) - (s1.NormalCredit + s1.SpecialCredit) ) AS TotalBalance
    FROM (
      SELECT u.userid
        , u.name
        , SUM(CASE WHEN ut.TransactionTypeID = 1  AND ut.ChargeTypeID = 1 THEN ut.TransactionAmount ELSE 0 END) AS NormalDebit
        , SUM(CASE WHEN ut.TransactionTypeID = 1  AND ut.ChargeTypeID = 6 THEN ut.TransactionAmount ELSE 0 END) AS SpecialDebit
        , SUM(CASE WHEN ut.TransactionTypeID = 2  AND ut.ChargeTypeID = 1 THEN ut.TransactionAmount ELSE 0 END) AS NormalCredit
        , SUM(CASE WHEN ut.TransactionTypeID = 2  AND ut.ChargeTypeID = 6 THEN ut.TransactionAmount ELSE 0 END) AS SpecialCredit
      FROM users u
      LEFT OUTER JOIN UsersTransactions ut ON u.userid = ut.UserID 
      WHERE u.test = 5
      GROUP BY u.userID
    ) s1
</cfquery>

<table>
<th>UserID</th>
<th>User Name</th>
<th>UserSpecialBalance</th>
<th>UserBalance</th>
<cfoutput query="getUsersTransactions">
    <!--- Build out the table rows in here--->
    <tr>
        <td>#getUsersTransactions.userID#</td>
        <td>#getUsersTransactions.name#</td>
        <td>#getUsersTransactions.SpecialBalance#</td>
        <td>#getUsersTransactions.TotalBalance#</td>
    </tr>
</cfoutput>
</table>

NOTE: Adding the ELSE 0 to the CASE statements will filter out the NULL values in the query that will break the summing. CF will still display those results as a blank, but the data could be wrong.