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?
Phillip
Here is my code.
<cfquery name="get" datasource="POUTDOORS_DBF">
select * from poutdoors_dbf
</cfquery>
<cfoutput>Total records from dbase4: <b>#get.RecordCount#</b></cfoutput><P>
<ol start="1">
<cfoutput>
<cfloop query="get" startrow="1" endrow="#get.RecordCount#">
<cfquery name="check" datasource="#application.datasource#">
select text_id, proddate from prodheader
where itemno2 = '#itemno2#' and
itemno3 = '#itemno3#' and
itemno4 = '#itemno4#'
</cfquery>
<cfif check.RecordCount is "0">
<cfquery name="put" datasource="#application.datasource#">
insert into prodheader
values(prodheaderID.NextVal,
'Off',
'#DateFormat(Now(), "dd-mmm-yyyy")#',
0,
'#itemno2#',
'#itemno3#',
'#itemno4#',
#dept#,
#subdept#,
#class#,
#subclass#,
#text_id#,
'#RTrim(header)#',
'#RTrim(status)#')
</cfquery>
</cfif>
<cfif check.RecordCount gt "0" and check.proddate is not "#DateFormat(Now(),
"dd-mmm-yyyy")#">
<cfquery name="add" datasource="#application.datasource#">
update prodheader
set proddate = '#DateFormat(Now(), "dd-mmm-yyyy")#',
text_id = #text_id#,
header = '#RTrim(header)#',
bpstatus = '#RTrim(status)#'
where itemno2 = '#itemno2#' and
itemno3 = '#itemno3#' and
itemno4 = '#itemno4#'
</cfquery>
</cfif>
<li>RECORD<br>
</cfloop>
</cfoutput>
</ol>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
