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=subscribeforumid=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
