coldfusion中的mysql查询优化

tvmytwxo 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(378)

我在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）：特殊比特= 25+17 = 42 1正常借方（记录3）：TotalDebt= 50 1正常信用（记录4）：总信用= 12 2特别信贷（记录5和6）：特别信贷= 7+18 = 25

sql mysql coldfusion

来源：https://stackoverflow.com/questions/48099130/query-optimization-in-coldfusion

2条答案

按热度按时间

5w9g7ksd1#

我会使用这种程序流。它代表了总体思路。细节由你决定。
第1步-运行一个查询以获取所有数据。按用户id排序，因为您将使用cf的group属性。

<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标记的group属性的更多信息。

赞(0）回复(0）举报 2021-06-21

3vpjnl9f2#

我上面的建议实际上相当于获取查询中所有用户所需的数据，然后输出这些结果，而不是循环为用户重新运行查询。我添加了一个使用查询的方法。
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 |

这将为用户标识1、3、4和5输出必要的字段，这些字段被过滤为test=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仍将这些结果显示为空白，但数据可能是错误的。

赞(0）回复(0）举报 2021-06-21

我来回答

coldfusion中的mysql查询优化

2条答案

相关问题

热门标签

最新问答