> I am doing a query to get all the records, 43224, from a dbase4
> database that was created from a tab delemeted file.
> I then check each record to see if it is in an Oracle table with
> 52,000 records and still growing.
> If it is then update that record.
> If not, insert that record.
>
> So far it takes about 10 min just to get through the first 500
> inserts/updates.
> Using my bad math skills, it should take over 12 hours to do this!
>
> Any one have any ideas on how to reduce the time it takes to do
> all of this?
<CFSNIP_OF_CODE>
Youch!
Firstly, can you run a JOIN on the "poutdoors_dbf" and "prodheader" tables?
That should cut down on the number of queries immeasurably
Secondly, can't you run the INSERT and UPDATE queries in one SQL statement?
I'm sure this should be a lot simpler than it is
It might take a little testing, but this shouldn't take more than a few
minutes to run on the server in a couple of SQL statements
Thirdly, if it's going to be rake-loads of queries, build a block of queries
into a string and separate them by ; and then run the queries once every 50
or so codes - this should speed it up a fair bit...
I'll see if I can build some SQL for you to get the JOIN and stuff, but I'm
a little pushed at the mo' to spend ages on my CF-Talking <g>
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
