简要概述
好的,因此我编写了一个查询,该查询将过滤某些产品,但是,我需要尝试进一步调整该查询以允许更多过滤器。我已经做到了,但是,它花费了两倍的时间,我的意思是页面加载和完全呈现大约需要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的数组,然后仅根据用户输入的细化来生成fineArr数组。 -它们都被迫具有相同的长度,因此当该索引不存在时,它永远不会尝试为一个数组获取索引“ 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()'之类的函数以确保其按预期工作,仅此而已,仅此而已,实际上可能甚至不需要。
最佳答案
这更多的是评论,但是太长了。
我真的不喜欢<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