[danarashad]

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?

[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?

[danarashad]

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.

[kiteless]

Hmm, yeah then this doesn't make any sense. Does using cfqueryparam slow down all queries? Or is it just this one?

[danarashad]

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.

[danarashad]

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.

[kiteless]

If you run the query from inside the SQL Server Manager, how long does it take from there?

[danarashad]

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.

[kiteless]

Even if you use bind variables in the SQL within SQL Server Manager?

[danarashad]

Closing the thread.