Optimizing File Insertions Into An Access Database

[PaidtheUmpire]

I have a Delphi program which inserts records from text files into an Access database. The system has become very slow as of late and i am wondering if there is a simple way to speed it all up again.

The program currently does the following:
[ol]
[li]Setup Variables[/li]
[li]Collects new record details[/li]
[li]Finds out which type of record it is[/li]
[li]Does appropriate procedure[/li]
[li]Inserts into database[/li]
[li]Updates program screen[/li]
[li]Goes on to next file (ie goto 2.)[/li]
[/ol]

Since the database has got more than 50,000 records in the system the insertion of records has slowed up dramatically. I have a couple of ideas but I have have no idea if they'll work or how to actually do them.

Idea 1. Insert all the new records into the dummy database and after all records are inserted into the dummy the program will transfer all the new records to the real database.
Idea 2. Insert all the new records into a textfile and then insert the completed textfile into the database.
Idea 3. Redo the insertion of records part of the program to speed it up.

Anyways here is the way i insert the records into the database (one at a time):

  SystemData.Tbl_SBD_Rec.Insert;
  SystemData.Tbl_SBD_Rec.Edit;
  SystemData.Tbl_SBD_Rec.FieldByName('Client_Reference_Number').AsString:= CRN;
  SystemData.Tbl_SBD_Rec.FieldByName('IMEI_Number').AsString:= IMEI;
  SystemData.Tbl_SBD_Rec.FieldByName('MSISDN_Number').AsString:= MSISDN;
  SystemData.Tbl_SBD_Rec.FieldByName('Start_Date_And_Time').AsString:= FullTime;
  SystemData.Tbl_SBD_Rec.FieldByName('Number_Of_Bytes_Sent').AsString:= NoBytes;
  SystemData.Tbl_SBD_Rec.FieldByName('Cost_To_Fastwave').AsString:= CostD + '.' + CostC;
  SystemData.Tbl_SBD_Rec.FieldByName('Cost_To_Client').AsCurrency:= CostClient;
  SystemData.Tbl_SBD_Rec.FieldByName('Number_Called').AsString:= PhoneNo;
  SystemData.Tbl_SBD_Rec.FieldByName('CTC_Currency').AsString:= CTC;
  SystemData.Tbl_SBD_Rec.FieldByName('InvoiceType').AsString := InvoiceWords;
  SystemData.Tbl_SBD_Rec.FieldByName('FileName').AsString := Part2;
  SystemData.Tbl_SBD_Rec.FieldByName('LineNo').AsString := IntToStr(LineNo);
  SystemData.Tbl_SBD_Rec.Post;

There is a large amount of code for the whole program and i can't really put it up here. Any other ideas or ways of how to do my ideas would be great.

Delphi I can't get enough of you.

 

[donvittorio]

It seems likely that the slow part is actually inserting the data into the database. Before you start trying to work around the problem, try and ascertain what it is about inserting data into this table that is so slow. Indexes are a common cause of this, as each index needs to be rebuilt each time a record is inserted. Try removing each index in turn and seeing what effect it has on the time taken to insert a record, with indexes there is always a trade off between the time it saves for select statements and the extra time it takes for insert and update statements. The same goes for primary and foreign keys of course.
If you are using a transaction then this can also slow things down if it gets very large, i.e. if you perform lots of operations before you commit the transaction.

HTH

Steve

 

[lespaul]

I'm not sure how you are getting your information from the text file to the variables, but here's some pieces of a routine that I use to do the same thing. The text file is fixed width and I have to go to specific spots to get the information I need.

procedure Upload();
var
  Save_Cursor : TCursor;
  F: TextFile;
  S: string;
  i: integer;
begin
  //lets the user select the file to be uploaded
  if frmMain.OpenDialog1.Execute then
  begin
    Save_Cursor := Screen.Cursor;
    Screen.Cursor := crHourGlass;
    try
      AssignFile(F, frmMain.OpenDialog1.FileName);
      Reset(F);
      While not EOF(F) do
      begin
        with dmJMS.qryVenireUpload do
        begin
          Readln(F, S); { Read first line of file }
          FullName := Copy(S, 160,30);  // extract name
          i := Pos( ',', FullName);
          SQL.Clear;
          SQL.Add('INSERT INTO JMPMAIN (JURNUM, LASTNAME, FIRSTNAME, STREET1, ' +
            'CITY, STATE, ZIPCODE, SSN, TERMDATE, ORGPOST, STATUSCD, DNPT, DNPM) VALUES (' +
            IntToStr(JurNum) + ', ' +  QuotedStr(Trim(Copy(FullName,0,i-1))) + ', ' + //JURNUM & LASTNAME
            QuotedStr(Trim(Copy(FullName, (i+2), (30 - Length(LastName))))) + ', ' + //FIRSTNAME
            QuotedSTr(Trim(copy(S,190,25))) + ', ' +  // extract address
            QuotedStr(Trim(copy(S,215,15))) + ', ' + //extract city
            QuotedStr(Trim(copy(S,230,2))) + ', ' +  //state
            QuotedStr(Trim(copy(S,232,9))) + ', ' + //zipcode
            QuotedStr(Trim(copy(S,151,9))) + ', ' + //ssn
            QuotedStr(VenireDate) + ', ' + //termdate
            QuotedStr(VenireDate) + ', ' + //orgpost date
            QuotedStr('NR') + ', ''F'', ''F'')');
          ExecSQL;
          inc(JurNum);
        end;
      end;
    finally
       Screen.Cursor := Save_Cursor;  { Always restore to normal }
    end;
  end;
end;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[svanels]

Big tables will slow down transactions, especially when you are looping through them. Using queries as showed by Leslie will speed-up things.

Steven van Els
SAvanEls@cq-link.sr