#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    58
    Rep Power
    9

    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?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,295
    Rep Power
    968
    cfqueryparam should be faster, since it uses bind variables. So I'm not sure what the problem might be...which database are you using?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    58
    Rep Power
    9

    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.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,295
    Rep Power
    968
    Hmm, yeah then this doesn't make any sense. Does using cfqueryparam slow down all queries? Or is it just this one?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    58
    Rep Power
    9
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    58
    Rep Power
    9

    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.
  12. #7
  13. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,295
    Rep Power
    968
    If you run the query from inside the SQL Server Manager, how long does it take from there?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    58
    Rep Power
    9

    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.
  16. #9
  17. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,295
    Rep Power
    968
    Even if you use bind variables in the SQL within SQL Server Manager?

IMN logo majestic logo threadwatch logo seochat tools logo