Well, by using a list like that, you are kind of defeating the indexing mechanism of the SQL server. You could try to simplify the list by grouping it in large sequential chunks and using the BETWEEN operator.
You also want to make sure that TicketID is indexed (if not the primary key). HTH -- Ryan Emerle -----Original Message----- From: Cedric Villat [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 9:35 PM To: CF-Talk Subject: QUERY IN performance hit I'm doing a query like this: <cfquery name="updateTicket" datasource="#request.dsn#" username="#request.dbuser#" password="#request.dbpass#"> UPDATE Tickets SET folder = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getEscalationInfo.eFolder#">, dateUpdated = <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#CreateODBCDateTime(Now())#"> WHERE ticketID IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#tickList#" list="Yes">) </cfquery> The tickList can contain 20-30,000+ items, and performing this query takes forever. What can I do to improve this a bit more? Perhaps doing multiple updates within the <cfquery> or doing ticketID = #List[1]# OR ticketIS = #List[2]# etc? Any ideas? Cedric ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
