sql - 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>加载临时表。您的 SQL 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