Link to home
Start Free TrialLog in
Avatar of satmanuk
satmanukFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Writing data to database in thread

Hi all,
Problem, Possible anyway.

A Database for my music.....

I have a list of a lot of bits of data i want to store in a Access / MSSQL server database from Delphi.
At the moment, I read the data from each file and write it to a database, To start with, its doing 50 per second, Then as the database grows, it gets to the case were one 1 or 2 per second is happening.

I am having to open the database, Try to write the data, If its there alread the write fails, so i go into edit mode to update it.
I use the following thread to keep the form alive.

Procedure TRunProcess.Execute;
Var
 i: Integer;
Begin
 Form1.ProgressBar1.max := sFileList.Count - 1;
 For i := 0 To sFileList.Count - 1 Do
  Begin
   sPos := I;
   If STOPRunning Then
    Begin
      LongProcess.Terminate;
      BREAK;
      Application.terminate;
    End;
    Form1.AddToDB(sFileList.strings[I]);
    Synchronize(UpdatePosition);
    Form1.label1.Caption := Inttostr(I) + '/' + Inttostr(sFileList.Count - 1);
    application.ProcessMessages;
  End;
 If Terminated Then Exit;
STOPRunning:=true;
End;

and the following to write the code.

Procedure TForm1.AddToDB(Filename: String);
Var
 UpdateStr: String;
Begin
 ID3V2.ReadFromFile(Filename);
 If ID3V2.Exists Then
  With ID3V2 Do
   Begin
    Try
     ADOTable1.close;
     ADOTable1.open;
     ADOTable1.Insert;
     ADOTable1.FieldByName('FullFileName').AsVariant := Filename;
     ADOTable1.FieldByName('Title').AsVariant := Title;
     ADOTable1.FieldByName('Artist').AsVariant := Artist;
     ADOTable1.FieldByName('Album').AsVariant := Album;
     ADOTable1.FieldByName('Track').AsVariant := TrackString;
     ADOTable1.FieldByName('TrackAmount').AsVariant := TrackAmount;
     ADOTable1.FieldByName('sYear').AsVariant := Year;
     ADOTable1.FieldByName('Genre').AsVariant := Genre;
     ADOTable1.FieldByName('Band').AsVariant := Band;
     ADOTable1.FieldByName('Remixed').AsVariant := Remixed;
     ADOTable1.FieldByName('Publisher').AsVariant := Publisher;
     ADOTable1.FieldByName('BPM').AsVariant := BPM;
     ADOTable1.FieldByName('Comment').AsVariant := Comment;
     ADOTable1.FieldByName('sKey').AsVariant := key;
     ADOTable1.Post; // Post the info to the database
     ADOTable1.close;
    Except //If there is a error
     On E: Exception Do
      Begin
       Try // Try to update the record if its there?
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add('select * from mp3s where FullFileName = '+ #39 + Filename + #39);
        AdoQuery1.open;
        If Not (AdoQuery1.RecordCount <= 0) Then // If less than 1 result
         Begin
          UpdateStr := ' With ';
          AdoQuery1.Edit;
          ADOQuery1.FieldByName('Title').AsVariant := Title;
          ADOQuery1.FieldByName('Artist').AsVariant := Artist;
          ADOQuery1.FieldByName('Album').AsVariant := Album;
          ADOQuery1.FieldByName('Track').AsVariant := TrackString;
          ADOQuery1.FieldByName('TrackAmount').AsVariant := TrackAmount;
          ADOQuery1.FieldByName('sYear').AsVariant := Year;
          ADOQuery1.FieldByName('Genre').AsVariant := Genre;
          ADOQuery1.FieldByName('Band').AsVariant := Band;
          ADOQuery1.FieldByName('Remixed').AsVariant := Remixed;
          ADOQuery1.FieldByName('Publisher').AsVariant := Publisher;
          ADOQuery1.FieldByName('BPM').AsVariant := BPM;
          ADOQuery1.FieldByName('Comment').AsVariant := Comment;
          ADOQuery1.FieldByName('sKey').AsVariant := key;
          ADOQuery1.Post; // Post the info to the database
          ADOQuery1.close;
        End;
       Except //If there is a error
        On E: Exception Do
         Begin
         End;
       End; //End Try
      End;
    End; //End Try
   End;
End;

Is this normal for writes to a DB to slow down so much as the data grows in the database?
I am really new to using threads in delphi so excuse me if the code is wrong.... It works though :)

Can someone show me a better way to do this?
It i do all the files, read the data from each file but NOT writing to the database, it takes a few minutes to complete.
With writing to the db enabled, it takes over a hour...... I left it running over night so dont know exact time.

So my collection of info from the files is nice and fast, But the database writes are my bottle neck.

Is there a better way (proberly) of doing this? And Is there a better free stand alone database file i could use to store the data?

Thanks in advance.




Avatar of Johnjces
Johnjces
Flag of United States of America image

Access databases will slow down terribly as they grow, as this has been my observation and experience.

What is the total number of records you are trying to add to your Access database tables? When/where are you storing data into MS SQL? ANd are you experienceing this slowness with MS SQL Server?

I have never seen a MS SQL server slow down appreciably at all!

What are your index or key fields?

I would check a key first to see if a record exists before trying to insert data and if exists update it then.

Just a couple of thoughts?

John
Avatar of satmanuk

ASKER

hi there, thanks for fast reply....

In the access database, i have the filename indexed with no dups.
I also have a standard id field as the key.

I will change the write to check for presents in db as you recomend and see if it helps.

But, I have emptied the DB fully, and started again, but same results.
Thought access would be a bottle neck, but makes it portable. But my MS SQL 2005 server is giving simaller results,


Sorry, more questions....

MS SQL 2005 full version or the desktop free version?

How large is the data or what size is the database when it starts to slow?

Let me know about checking the key/index first before inserting and updating as I am unsure if you tried that yet.

John
ASKER CERTIFIED SOLUTION
Avatar of SteveBay
SteveBay
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
definitely your slowdown is the use of the table
use an insert query as SteveBay has done
except perhaps when checking for duplicates, if "sKey" is the actual table key, then check that instead of "FullFileName"
the shorter the key the better, and string keys make pretty lousy indexes.
You might try an algorithm that turns your string into an integer based on the ascii codes or similar...

if you want a good free Database management system (DBMS) then use Firebird
Otherwise, MSSQL is a very good choice
Its the full version of MS SQL 2005, Works nice at times ;)

I have changed the function to

Procedure TForm1.AddSongToDB(Filename: String);
Begin
 ID3V2.ReadFromFile(Filename);
 If ID3V2.Exists Then
  With ID3V2 Do
   Begin
    Try // Try to update the record if its there?
     AdoQuery1.Close;
     AdoQuery1.SQL.Clear;
     AdoQuery1.SQL.Add('select * from mp3s where FullFileName = ' + #39 + Filename + #39);
     AdoQuery1.open;
     If Not (AdoQuery1.RecordCount <= 0) Then // If less than 1 result
      AdoQuery1.Edit
     Else
      AdoQuery1.Insert;
     ADOQuery1.FieldByName('FullFileName').AsVariant := filename;
     ADOQuery1.FieldByName('Title').AsVariant := Title;
     ADOQuery1.FieldByName('Artist').AsVariant := Artist;
     ADOQuery1.FieldByName('Album').AsVariant := Album;
     ADOQuery1.FieldByName('Track').AsVariant := TrackString;
     ADOQuery1.FieldByName('TrackAmount').AsVariant := TrackAmount;
     ADOQuery1.FieldByName('sYear').AsVariant := Year;
     ADOQuery1.FieldByName('Genre').AsVariant := Genre;
     ADOQuery1.FieldByName('Band').AsVariant := Band;
     ADOQuery1.FieldByName('Remixed').AsVariant := Remixed;
     ADOQuery1.FieldByName('Publisher').AsVariant := Publisher;
     ADOQuery1.FieldByName('BPM').AsVariant := BPM;
     ADOQuery1.FieldByName('Comment').AsVariant := Comment;
     ADOQuery1.FieldByName('sKey').AsVariant := key;
     ADOQuery1.Post; // Post the info to the database
     ADOQuery1.close;
    Except //If there is a error
     On E: Exception Do
      Begin
      // showmessage('error' + filename);
      End;
    End; //End Try
   End;
End;

And it seems to have speeded it up no end. 20000 files in 5 mins. :) Thanks

But, Another thing i am finding is if the filename has a ' in it, it errors.
it sees it as a end of the string early.
How would i get around this?

I have tried this.
     ADOQuery1.FieldByName('FullFileName').AsVariant := StringReplace(filename,#39,#39#39,[rfReplaceAll, rfIgnoreCase]);

tried that because this works.
test :=' captian''s heart';



my bad....sorted it..

Thanks for your help