You may want to break into 2 sets of work the first one will do all of the
inserts. Then do the updates. If you do at the same time it will be slow.
You could also create a temporary table and insert all of the data into
Oracle using SQLLOADER that comes with Oracle. Then you could use an insert
with a subselect.
INSERT INTO [table](columns)
VALUES()
WHERE not exists(SELECT * FROM TEMPTABLE, INSERTTABLE WHERE ...)
Then do an update by the same method.
Thank You,
Peter
Peter J. MacDonald II
Creative Computing, Inc.
100 Middle Street
Lincoln, RI 02865
Phone: 401.727.0183 x123
Fax: 401.727.4998
Portable: 401.965.3661
E-MAIL: [EMAIL PROTECTED]
Web Page: www.creatcomp.com
-----Original Message-----
From: Art Broussard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 13, 2001 10:57
To: CF-Talk
Subject: What else can I say but HELP!!!!
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
