Okay, so I've written a query that will filter some products, however, I need to try and further tweak this query to allow for even more filters. I've kinda done that, however, it takes twice as long, I mean it takes about 5+ seconds for the page to load and be rendered completely, which isn't good enough in my opinion. I mean it works, but it's just too slow for commercial release.
I'm assuming it's so much slower with the function I've copied in below but sadly I'm not sure how else I could do this? - I can't think of a way where I could write a query that would essentially eliminate the need to having to use the function I've written.
I'm guessing, the best way would be to somehow merge the two queries together, plus another one for other filters that don't work for the current one I have in place? - I'm not totally sure.
So anyway, here's some code:-
<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>
Just to clarify, this query works just fine, no issues at all. The two arrays are just generated prior to this query, the IDarr array is just an array of all ID's that are within a certain category, then the refineArr array is just generated depending on what refinements the user has input. - They're both forced to be the same length so it never tries to get index 'x' for one array when that index doesn't exist.
And as you may have guessed, the line 'getallcodes.code', that just gets all codes for all products that should show under a certain cat/sub-cat, etc.
Okay, so with this next part, due to the different kinds of filters, I need to allow for ranges to occur, dates and measurements too. However, ignore the measurements part for now, turns out that the data stored in the database is all screwed up for the measurements.
I originally tried to create a function, it works, but it's not that fast, and to run call the function, I originally wrote this:
<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>
Just keep in mind, I haven't yet finished writing this function, I know it's still a little bit messy, and as for the arrays, they're honestly just a collection of some variables that are critical for working out additional refinements.
<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>
I'd like to apologies for any syntax highlighting issues, as well as the fact that it's a bit messy. In addition to the fact that I've got to leave a lot of info out. The source code itself looks very different, but that's deliberate, I'm just copying a dummy example here, as it's a part of my responsibility to ensure that I don't expose too much information about the structure of the web page I'm working on, silly I know, but rules are rules.
I've also only added functions like 'Int()' or 'ToString()' to ensure that it works as expected, that's it, nothing more, nothing less, it's probably not even needed realistically speaking.
This is more of a comment, but it is too long.
I really don't like the <cfloop> to load a temp table. Your SQL Server will not be able to cache the query and it will have to reparse it every time. I have similar issues with the #valuelist(getallcodes.code)#
I would rather that the multi value parameters have there data pulled via something that never changes.
<!--- 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>
For an additional example: Select IN on more than 2100 values