Database writing problem

[bboggessswcg]

I am now reading a .DBF file into a recordset with about 1.2 million records in it. I am then writing the entire file back out into another .DBF file. This process is taking forever, and I need to speed it up. I am just using a SELECT statement to get the information, and a UPDATE statement on each row of the recordset to write it. Is there a faster way to write it out? Thanks in a advance for your help.

 

[bboggessswcg]

Below is the code that I am using to execute this update statement. It is taking probably 10 minutes to do a few thousand records. Anyone know of a better way to dump a .DBF file into another .DBF file containing all the same fields?

Do While rs.EOF = False
strSqlUp = "INSERT INTO ACTNEW ( ACTCNO, ACTLNO, ACTDAT, ACTRES, ACECAT, ACECOD, ACEUSR, ACFILR, ACORIG, ACADM ) VALUES ('" & rs!ACTCNO & "', '" & rs!ACTLNO & "', '" & rs!ACTDAT & "', '" & rs!ACTRES & "', '" & rs!ACECAT & "', '" & rs!ACECOD & "', '" & rs!ACEUSR & "', '" & rs!ACFILR & "', '" & rs!ACORIG & "', '" & rs!ACADM & "')"

Conn.Execute (strSqlUp)
rs.MoveNext
Loop

 

[johnwm]

Are you modifying the data in any way or is this a backup or copy?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[bboggessswcg]

Basically I am making a backup. The new .DBF file will have 1 extra field in it, but I am not populating it. I am moving all the data from 1 DBF file to another. Keep in mind that there are 1.2 million records in the file. The recordset reads plenty fast enough, it is just the writing that is taking forever.

 

[johnwm]

Can you use a stored procedure depending on what db engine you're using? Or can you just use FileCopy, then insert the extra field separately?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[bboggessswcg]

Not sure about the stored procedure, never done that before. I need to read and write the file to get rid of some corruption within it, that is why I can't just use the FileCopy function. Maybe if you could give me some help with the stored procedure, that could work, but I am not sure how or what to do with that.

 

[johnwm]

You'll need to look in your own database manual for Stored Procedures. Setting them up is different for different dbs

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[billybobk]

Sorry, no stored procedures in dBase that I've ever seen. What program is it?

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[Lunch]

SQL DTS

 

[ca8msm]

Do you have to step through the recordset and do something to the data or could you try just executing a select into query. i.e.

strSqlUp = "SELECT ACTCNO, ACTLNO, ACTDAT, ACTRES, ACECAT, ACECOD, ACEUSR, ACFILR, ACORIG, ACADM INTO ACTNEW FROM ACTOLD"

Conn.Execute (strSqlUp)

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[billybobk]

Oh, bboggessswcg, what program is this dBase database?

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[johnwm]

billybobk,

You could re-phrase that question.

Which one from the list Clipper, dBase, FoxBase, Foxpro, Visual Foxpro, Visual DBase .... etc etc is using this dbf file

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[vbrocks]

You could try writing the new db file into a RAM DISK first then copy it over to Hard disk after it is finished.

 

[billybobk]

Thanks John: yes, please choose from the list, and there is a reason for my asking. I do a lot of work with some dBase applications (unfortunately; times are rough and it's thin pickens in the job market!) But I can tell you this; that's a lot of records for a dBase app, and my first guess for the slowness that you are experiencing is: way too many t-log records. If you cannot migrate to SQL server or other RDBMS, the least you should do is regularly truncate the t-logs, pack/rebuild/re-index the database to try and improve the performance. If you already do these things, then I'm not sure why the slowness (hardware issue?) Good luck!

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran