CFML & SQL -性能提高？

Question

简要概述

好的，我已经编写了一个查询，它将过滤一些产品，但是，我需要进一步调整这个查询，以便允许更多的过滤器。我已经做了一些，但是，它需要两倍的时间，我的意思是，加载和呈现页面需要大约5+秒的时间，这在我看来还不够好。我的意思是它很有效，但对商业发行来说太慢了。

--我假设--我在下面复制的函数要慢得多，但遗憾的是，，我不知道还能怎么做呢？--我想不出有什么方法可以编写一个查询，这样基本上就不必使用我编写的函数了。

(我猜是)，最好的方法是将这两个查询合并在一起，再加上另一个对当前的过滤器不起作用的过滤器？-我不完全确定。

，所以，这里有一些代码：-

初始查询

<cfset row = 0>

...

<cfquery name="query" datasource="ds">
    DECLARE @st TABLE (ID int, z varchar(50))
    DECLARE @tc int

    <cfloop array="#refineArr#" index="x">
    <cfset row ++>
        <cfoutput>
            INSERT INTO @st VALUES ('#IDArr[row]#', '#x#')
        </cfoutput>
    </cfloop>

    SELECT @tc = COUNT(DISTINCT ID) 
    FROM @st
    SELECT tbl.code
    FROM Table1 tbl

    INNER JOIN @st T 
    ON T.ID = tbl.ID 
    AND tbl.V = T.z

    INNER JOIN Table2 tbl2
    ON tbl.ID = tbl2.ID

    WHERE tbl.code IN (<cfqueryparam list="yes" value="#valuelist(getallcodes.code)#">)
    GROUP BY tbl.code
    HAVING COUNT(tbl.ID) = @tc
</cfquery>

澄清一下，这个查询工作得很好，一点问题都没有。这两个数组是在这个查询之前生成的，IDarr数组只是一个数组--所有ID都在某个类别内，然后根据用户输入的细化程度生成refineArr数组。--它们都必须是相同的长度，所以当一个数组不存在索引时，它就不会尝试获取该数组的索引'x‘。

正如您可能已经猜到的那样，和行“getallcodes.code”，它只获取应该在某个猫/子猫下面显示的所有产品的所有代码，等等。

下半部分

好的，在接下来的部分中，由于不同的类型的过滤器，我需要允许范围，日期和测量。但是，现在忽略度量值部分，结果是存储在数据库中的数据都被错误地用于度量值。

我最初尝试创建一个函数，它可以工作，但是它没有那么快，为了运行这个函数，我最初写了以下内容：

<cfset ranges = ['Months','Period','SMonths']>
<cfset tc = 0>
<cfset tempQ = query>

...

<cfloop array="#ranges#" index="i">
    <cfset tc ++>
    <cfif i contains 'month' or i contains 'period'>
        <cfset tempQ = rangesFnc(tempQ, Int(tc), ToString(i))>
    </cfif>
</cfloop>

功能本身

请记住，我还没有完成这个函数的编写，我知道它仍然有点混乱，而对于数组来说，它们只是一些变量的集合，这些变量对于进行额外的细化是至关重要的。

<cffunction name="rangesFnc">
    <cfargument name="q" type="query" required="true">
    <cfargument name="i" type="numeric" required="true">
    <cfargument name="s" type="string" required="true">

    <cfset minArr = '#minf#,
                    #minh#,
                    #minh2#,
                    #minm#,
                    #mins#'>

    <cfset maxArr = '#maxf#,
                    #maxh#,
                    #maxh2#,
                    #maxm#,
                    #maxs#'>

    <cfset min = listGetAt(minArr, i)>
    <cfset max = listGetAt(maxArr, i)>

    <cfquery name="tempq" datasource="ds">
        WITH q AS (
            SELECT DISTINCT tbl.code,

            CASE
                WHEN tbl.V = 'January' THEN 1
                WHEN tbl.V = 'February' THEN 2
                WHEN tbl.V = 'March' THEN 3
                WHEN tbl.V = 'April' THEN 4
                WHEN tbl.V = 'May' THEN 5
                WHEN tbl.V = 'June' THEN 6
                WHEN tbl.V = 'July' THEN 7
                WHEN tbl.V = 'August' THEN 8
                WHEN tbl.V = 'September' THEN 9
                WHEN tbl.V = 'October' THEN 10
                WHEN tbl.V = 'November' THEN 11
                WHEN tbl.V = 'December' THEN 12
                ELSE 0
            END AS xdate

            FROM Table1 tbl
            INNER JOIN Table2 tbl2
            ON tbl.ID = tbl2.ID

            WHERE tbl2.name LIKE <cfqueryparam value="%#s#%">
            AND tbl.code IN (<cfqueryparam value="#valueList(q.code)#" list="yes">)
        )

        SELECT code 
        FROM q

        WHERE xdate <= <cfqueryparam value="#Int(max)#">
        AND xdate >= <cfqueryparam value="#Int(min)#"> 
    </cfquery>  

    <cfreturn tempq>
</cffunction>

最后

对于任何突出显示语法的问题，以及它有点混乱的事实，我都表示歉意。除了我还得留下很多信息。源代码本身看起来与完全不同，但这是故意的，我只是在这里复制一个虚拟示例，因为我的职责之一是确保我不公开太多关于我正在处理的网页结构的信息，我知道这很愚蠢，但规则是规则。

我也只添加了像'Int()‘或'ToString()’这样的函数来确保按预期工作，也就是说，没有更多，也没有更少，从现实的角度来说，它可能是不需要的。

Answer 1

这是一个更多的评论，但它太长了。

我真的不喜欢<cfloop>加载临时表。您的Server将无法缓存该查询，因此每次都必须对其进行解析。我和#valuelist(getallcodes.code)#也有类似的问题

我宁愿多值参数通过一些永不改变的东西来提取数据。

<!--- Generate XML of data --->


<cfquery>
DECLARE @st TABLE (ID int, z varchar(50))
DECLARE @tc int
DECLARE @xmlIDArr xml = <cfqueryparam value="#xmlIDarr#">

INSERT INTO @st
SELECT tbl.Col.value('id', 'int'), tbl.Col.value('z', 'varchar(50)')
FROM    @xmlIDArr.nodes('/data') tbl(Col)S


SELECT @tc = COUNT(DISTINCT ID) 
FROM @st
  SELECT tbl.code
  FROM Table1 tbl

  INNER JOIN @st T 
  ON T.ID = tbl.ID 
  AND tbl.V = T.z
</cfquery>

关于另一个例子：Select IN on more than 2100 values

Answer 2

这里有一些代码评审点(这是一个答案，因为它的格式比注释更漂亮)：

初始查询

1)将行变量移动到查询中并对其进行范围调整。查询前的<cfset row = 0>只是自找麻烦。

2)将INSERT移出循环之外。SQL 2008可以插入多个值。不需要多次访问数据库。

INSERT INTO @st 
VALUES
<cfloop array="#refineArr#" index="x">
    <cfset row++>
    <cfoutput>('#IDArr[row]#','#x#')</cfoutput>
    <cfif row LT ArrayLen(refineArr)>,</cfif>
</cfloop>

3)我不知道cf页面的其余部分是什么样子，但我的直觉是cfquery可以重构为一个单独的选择，而不需要temp @st表变量。

下一部分

如果没有看到更多的代码，我就不知道如何对其进行优化。同时可以有多个过滤器(即用Months and Periods进行过滤)

1)重新界定范围。这里有很多松散的变量。有些可能是不必要的。

( 2)这里是否需要循环？同样，如果没有更多的代码，我不知道它的目的是什么。

3)在您的循环中，i contains month or i contains period对i中的值进行了大量额外的解析，它可能并不总是返回预期的值。如前所述，它还将返回SMonths。

4)我假设tempQ=query只是第一部分查询的一个副本，对吗？需要这个吗？

函数本身

1) minArr和maxArr >>是否会将数组传递到函数中？或者minf, minh....变量会被传入吗？不应该从函数外部访问它们。您应该能够完全独立地运行这个函数(而且它可能是它自己的CFC)。

2)它们被命名为数组，但它们包含列表。名字应该与他们的意图一致。

3)将变量重命名为tempq，并将其设置为前面的查询，然后在函数内部创建另一个名为tempq的查询。如果在页面下进一步使用，这可能会使您想要访问的tempq变得混乱。

4) Table1.V的数据类型是什么？您可以只使用SQL函数来返回日期名称的数字吗？

5)在WHERE子句中，使用tbl2.name LIKE....。应该将其移到INNER JOIN for Table2中。‘tbl.ID Table2 tbl2 ON tbl.ID= tbl2.ID和tbl2.name ()

6)我认为您在这个查询中不需要CTE。您正在构建xdate，只是通过它来过滤CTE。将整个块添加到WHERE子句中。

终于

我花了近20年的时间在专有或更严格的环境中工作，所以我完全理解保护应用程序源代码的必要性。这可能是令人沮丧的，但它有时是必要的邪恶。我在这里说的比我想的要多一点，但我希望这能帮上忙。正如我前面所说的，在不了解您的应用程序意图的情况下，我只能希望我已经引导您走上了正确的道路，我希望我的建议在性能上至少有了一点改进。

祝好运。