ColdFusion中的查询优化

100

马克斯·男孩

我在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

在这种情况下，我有：

2个“特殊借方”（记录1和2）：SpecialDebit = 25+17 = 42
1普通借项（记录3）：TotalDebit = 50
1个普通信用（记录4）：TotalCredit = 12
2个特殊信用（记录5和6）：SpecialCredit = 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 |

这将输出经过test = 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仍将这些结果显示为空白，但数据可能是错误的。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。