Known Participant

Question

how to speed insert my 1000000 records into the database?

Forum|Forum|18 years ago
August 23, 2007
8 replies
1017 views

my code like:

<cfloop from="1" to="#inserteddb.getrecordcount()#" index="x">



<cfquery datasource="#cfdsn#" name="insertdata">
insert into inputtest (#InsertFieldList#)
values (
<cfqueryparam value="#InsertValueList#" cfsqltype="cf_sql_varchar" list="yes">
)
</cfquery>
</cfloop>

The test inserts 100,000 records, has spend I 30 minutes time,but I have 1,000,000 record to insert , is there any way to enhance the insertion speed?

Thanks a lot.

Database access

This topic has been closed for replies.

F

FlashcqxgAuthor

Known Participant

Thanks a lot.

M

Michael8

Participating Frequently

Whatever method you use to load the records, make sure you disable any indexes during the inserts. Then, renable the indexes after the batch is complete.

A

Anonymous

> Sybase anywhere have import/export tools,but i need import data from web.

Are you or your company the owner of the data on the Web? Do you have a license to suck it all down for your own purposes?

F

FlashcqxgAuthor

Known Participant

thank you cf_dev2 :

Sybase anywhere have import/export tools,but i need import data from web.

D

Dan_Bracuk

Inspiring

quote:

Originally posted by: Flashcqxg
thank you cf_dev2 :

Sybase anywhere have import/export tools,but i need import data from web.

I have a similar situation. My solution was to use Cold Fusion to get data from the first db, write it to text files, and ftp to another server, the one with the 2nd db. I then wrote a script to check the ftp directory and if I found any files, do something with them. This script is scheduled to run every day.

The Cold Fusion job also sends me mail whenever it runs, so, if I'm there at the time, I can run the 2nd job manually.

C

cf_dev2

Inspiring

Sybase anywhere doesn't have import/export tools?

F

FlashcqxgAuthor

Known Participant

Thank you Ian Skinner and Dan Bracuk :

I must import the dbf file into my database(sybase anywhere). I have got dbf data through javadbf。but javadbf can only read into array,so,I can only do like this.

D

Dan_Bracuk

Inspiring

If you absolutely have to use Cold Fusion, you might be able to speed things up by putting the loop inside the query instead of the the way you have it now. You have to test it, I've seen it speed things up and slow things down.

The syntax is

insert into yourtable
(field1, field2, etc)
<cfloop>
select distinct value1, value2, etc
from some_small_table
<cfif loop not done>
union
closing tags

N

Newsgroup_User

Inspiring

By removing ColdFusion from the process as much as possible.

Where is the 'insertedDB' data coming from? It looks to be a record set?

Are you moving data from one data source to another? If so, some DBMS
have the ability to insert an entire record set in one step. I do not
have the exact syntax at my finger tips, but I have done something like
this in the past with Oracle. INSERT INTO aTable SELECT FROM bTable.

Are you building a record set from a text file such as CSV? If so, many
DBMS have the ability to do 'bulk' inserts from such text files and CF
does not even need to be involved.

As you can see, knowing exactly what are you working with will help us
provide suggestions an how to improve your process.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded