Query optimization in ColdFusion
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:
25+17 = 42 50 12 7+18 = 25 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 JOIN s 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.
I would use this sort of program flow. It represents the general idea. The details are up to you.
Step 1 - Run a single query to get all your data. Sort it by user ID because you are going to use CF's group attribute.
<cfquery name="data">
select userID, field1, field2, etc
from users u join UsersTransactions ut on u.UserId = ut.userID
etc
where whatever
order by userID
</cfquery>
Step 2 - Start outputting data at the user level. Then for each user, do your calculations and output the result.
<cfoutput query="data" group="userID">
#username# etc
<cfset totalCredit = totalDebit = specialCredit = specialDebit = 0>
<!--- now do the math for each user --->
<cfoutput>
<cfscript>
if (whatever) totalCredit += something;
etc
</cfscript>
</cfoutput>
<!--- display these variables --->
#totalCredit#
</cfoutput>
You can read more about the group attribute of the cfoutput tag in the documentation.
链接地址: http://www.djcxy.com/p/62462.html下一篇: ColdFusion中的查询优化