0

Our company has used some flat files as a database for many years, it has a fix structure snd unfortunately with no extra space. now they wanted to add some new fields and even make it more flexible to add new filed easily. It is a while I am working here and they gave this task to me.

I have two questions:

  1. Firstly, I decided to convert it to a SQL based database like SQLite, I wrote a small program and used APIs sqlite3_prepare_v2 and sqlite_bind_* (for 18 fileds) to convert our legacy database to SQLite but it take forever. It’s four days and it just convert about 37,000 record from 500,000 records.

How can I improve this process, is there any better way? Because typically there are about 30 to 90 separated flat files with about 500,000 to 10,000,000 records in each one. So by considering the maximum, we should deal with sbout 90 * 10,000,000 records

  1. My second question is, whether it is reasonable to keep this fixed flat files with just some modification(a smart sulotion)? What you will do if you where in my situation? (I hope this question not be inappropriate to ask here, because I really need some advice in this case, but if it is not OK to ask these questions I am verry sorry about that)

And it may worth to say that the information in these database are very sensitive and our approach should be immune to unexpected incidents like power failure and anything like that (being able to recover from that). And also, Losing to save even one record is very critical for our company and their customers.

UPDATE 1: (some snipped code included)

const char sql = "INSERT INTO MetaData ("
 "Field_1,"
 "Field_2,"
 /* 16 other fields */
 ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

 for (unsigned record = 0; record <= header.endpos; record++) {
   rc = sqlite3_precpare_v2(db, sql, strlen(sql), &stmt, &pzTail);
   if (rc) {
     fprintf(stderr, "cannot prepare database,\n");
     goto failed1;
   }

   dwReadCount = 0;
   ReadFile(hIndex, &index, sizeof(index), &dwReadCount, NULL);

   sqlite3_bind_int(stmt, 1, index.field1);
   sqlite3_bind_int(stmt, 2, index.field2);  
   /* 16 same other binding
   */
   ..
   ..
   ...

   sqlite3_step(stmt);
   sqlite3_finalize(stmt);
 }

I appreciate any help or hint.

Thanks

Arash
  • 383
  • 4
  • 16
  • 1
    Four days? You better show some code since that amount I would assume to be handled in seconds. – Sami Kuhmonen Dec 09 '17 at 07:59
  • @Sami Kuhnonen I add that part if code – Arash Dec 09 '17 at 08:21
  • At least don’t prepare for every record. That needs to be done only once. And don’t finalize either other than after the loop. – Sami Kuhmonen Dec 09 '17 at 08:48
  • Ok I put prepare and finalize outside the loop, tested for 10 minuets it converted about 5766 records. It still lots of time to convert all. But thanks for the help. – Arash Dec 09 '17 at 09:38

0 Answers0