CFML & SQL - increasing productivity?

Question

Short review

Ok, so I wrote a query that will filter some products, however I need to try setting this query again to have more filters. I performed kinda , however, it takes twice as much, I mean that loading the page and its full processing takes about 5 seconds, which in my opinion is not enough. I mean, it works, but it's too slow for a commercial release.

I assume it is much slower with the function that I copied below , but sadly . I'm not sure how else could I do this? “I can't think of a way that I could write a query that would essentially eliminate the need to use the function I wrote.”

I guess the best way would be to somehow combine the two queries together, plus one more for other filters that don't work for the current one that I have? “I'm not quite sure.”

Anyway, here is some code : -

---

Original request

<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 - all , , ARR , , - , "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>

: IN 2100

Answer 2

( , , ):

1) . <cfset row = 0>, .

2) INSERT . SQL 2008 INSERT . .

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 SELECT temp @st table.

, , . (.. Months and Periods?

1) . . , .

2) ? , , .

3) i contains month or i contains period i, . , SMonths.

4) Im, , tempQ=query , ? ?

1) minArr maxArr → ? minf, minh....? - . (, , CFC).

2) , . .

3) tempq , tempq. , tempq , .

4) Table1.V? SQL ?

5) WHERE tbl2.name LIKE..... INNER JOIN Table2. `INNER JOIN Table2 tbl2 ON tbl.ID = tbl2.ID tbl2.name LIKE()

6) , CTE . xdate CTE. WHERE.

2 , , . , . , , , . , , , , , , .

.