February 18th, 2015, 12:45 PM
-
cfqueryparam issues
Code:
SELECT DISTINCT Table3.ID
FROM Table1
INNER JOIN Table2 ON Table1.thisID = Table2.thisID
INNER JOIN Table3 ON Table2.ID = Table3.ID
WHERE (Table1.ID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#idlist#" list="yes">))
AND Table2.ID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#idlist2#" list="yes">)
AND Table3.active=1
ORDER BY Table3.ID
When I run the above code it takes 11 to 15 seconds. If I remove the queryparam and just use the idlist2 variable. The query only take 32 miliseconds. Is this a coming issue with cfquery param, or am I doing something incorrect?
February 19th, 2015, 09:04 AM
-
cfqueryparam should be faster, since it uses bind variables. So I'm not sure what the problem might be...which database are you using?
February 19th, 2015, 02:44 PM
-
Sql
Originally Posted by kiteless
cfqueryparam should be faster, since it uses bind variables. So I'm not sure what the problem might be...which database are you using?
SQL Server 2008.
February 20th, 2015, 01:01 AM
-
Hmm, yeah then this doesn't make any sense. Does using cfqueryparam slow down all queries? Or is it just this one?
February 20th, 2015, 07:52 AM
-
Originally Posted by kiteless
Hmm, yeah then this doesn't make any sense. Does using cfqueryparam slow down all queries? Or is it just this one?
Not all, but it happens to a couple of queries.
February 20th, 2015, 04:12 PM
-
Stored Procedure.
Originally Posted by danarashad
Not all, but it happens to a couple of queries.
I have moved the code to a stored procedure and rebuilt indexes and that have seemed to cut the time down to 2-4 seconds.
February 21st, 2015, 01:09 PM
-
If you run the query from inside the SQL Server Manager, how long does it take from there?
February 23rd, 2015, 07:41 AM
-
Query
Originally Posted by kiteless
If you run the query from inside the SQL Server Manager, how long does it take from there?
It only takes a couple of milliseconds.
February 24th, 2015, 05:08 PM
-
Even if you use bind variables in the SQL within SQL Server Manager?
October 17th, 2016, 11:44 AM
-