ColdFusion中的查询优化
我在ColdFusion中创建了一个代码，用于从Users和UsersTransactions表中加载数据并将其显示在表中。 但是，我的查询花费很长时间才能运行。
<!--- 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>
需要很长时间的是“getUsersTransactions”查询的循环。 有没有办法让这些查询运行得更快？
更新：
一位用户的数据样本：
User.UserID = 10
它将在UsersTransactions中有6个记录
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
在这种情况下，我有：
25+17 = 42 50 12 7+18 = 25 我上面的建议基本上等同于抓取查询中所有用户所需的数据，然后输出这些结果，而不是循环为用户重新运行查询。 我已经添加了使用查询的刺戳。
SQL小提琴
MySQL 5.6架构设置 ：
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' ) ;
初始查询示例 ：注意：这可以进一步优化。
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
结果 ：
| userid | name | SpecialBalance | TotalBalance |
|--------|-----------|----------------|--------------|
| 1 | Bill | -3 | -3 |
| 3 | Rufus | 10 | 20 |
| 4 | SilentBob | 0 | 0 |
| 5 | Jay | 0 | 500 |
这将输出用于测试= 5过滤的UserID 1,3,4和5的必要字段。 由于似乎没有来自表TransactionTypes和ChargeTypes任何数据，因此可以删除这些INNER JOIN以显着加快查询速度。
所以当你到达CF时，你可以简化查询。 尝试：
<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>
注：将ELSE 0添加到CASE语句将过滤掉查询中的NULL值，该值将打破求和。 CF仍然会将这些结果显示为空白，但数据可能是错误的。
我会使用这种程序流程。 它代表了总体思路。 细节取决于你。
第1步 - 运行一个查询来获取所有数据。 按用户标识排序，因为您要使用CF的组属性。
<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>
第2步 - 开始在用户级输出数据。 然后为每个用户做好计算并输出结果。
<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>
您可以在文档中阅读有关cfoutput标记的组属性的更多信息。
链接地址: http://www.djcxy.com/p/62461.html