Solved

Writing data to database in thread

Posted on 2008-06-24
7
238 Views
Last Modified: 2010-04-05
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.




0
Comment
Question by:satmanuk
7 Comments
 
LVL 18

Expert Comment

by:Johnjces
ID: 21858771
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
0
 
LVL 1

Author Comment

by:satmanuk
ID: 21858878
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,


0
 
LVL 18

Expert Comment

by:Johnjces
ID: 21858956
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 14

Accepted Solution

by:
SteveBay earned 250 total points
ID: 21859053
Two things come to mind.
1.) ADOTable is frequently less efficient than using ADOQuery.
2.) I would not use an exception to determine if it's an existing record.
Try something like this: (typed in editor there may be few syntax issues)
Procedure TForm1.AddToDB(Filename: String);

Var

 UpdateStr: String;

 DoInsert : Boolean;

Begin

 ID3V2.ReadFromFile(Filename);

 If ID3V2.Exists Then

  With ID3V2 Do

   Begin

     ADOQuery1.Close;

     ADOQuery1.SQL.Clear;

     ADOQuery1.SQL.Add('SELECT COUNT(*) As Cnt FROM mp3s WHERE FullFileName = ' + QuotedStr(Filename));

      try

      ADOQuery1.Active := True;

      except

       on e : exception do begin

        showmessage(e.Message)

        end;

      end;

     DoInsert := (ADOQuery1.FieldByName('Cnt').AsInteger > 0);

     ADOQuery1.Close;

     ADOQuery1.SQL.Clear;

     if DoInsert then

      Begin

      ADOQuery1.SQL.Add('INSERT INTO mp3s (' );

      ADOQuery1.SQL.Add('FullFileName');

      ADOQuery1.SQL.Add(',Title');

      ADOQuery1.SQL.Add(',Artist');

      ADOQuery1.SQL.Add(',Album');

      ADOQuery1.SQL.Add(',Track');

      ADOQuery1.SQL.Add(',TrackAmount');

      ADOQuery1.SQL.Add(',sYear');

      ADOQuery1.SQL.Add(',Genre');

      ADOQuery1.SQL.Add(',Band');

      ADOQuery1.SQL.Add(',Remixed');

      ADOQuery1.SQL.Add(',Publisher');

      ADOQuery1.SQL.Add(',BPM');

      ADOQuery1.SQL.Add(',Comment');

      ADOQuery1.SQL.Add(',sKey');

      ADOQuery1.SQL.Add(') VALUES (');

      ADOQuery1.SQL.Add(' ' + QuotedStr(Filename));

      ADOQuery1.SQL.Add(',' + QuotedStr(Artist));

      ADOQuery1.SQL.Add(',' + QuotedStr(Album));

      ADOQuery1.SQL.Add(',' + QuotedStr(TrackString));

      ADOQuery1.SQL.Add(',' + QuotedStr(TrackAmount));

      ADOQuery1.SQL.Add(',' + QuotedStr(Year));

      ADOQuery1.SQL.Add(',' + QuotedStr(Genre));

      ADOQuery1.SQL.Add(',' + IfThen(Remixed,'1','0'));

      ADOQuery1.SQL.Add(',' + QuotedStr(Publisher));

      ADOQuery1.SQL.Add(',' + IntToStr(BPM));

      ADOQuery1.SQL.Add(',' + QuotedStr(Comment));

      ADOQuery1.SQL.Add(',' + QuotedStr(key));

      ADOQuery1.SQL.Add(')');

      end // do Insert

     else

      begin

      ADOQuery1.SQL.Add('UPDATE mp3s SET');

      ADOQuery1.SQL.Add('Title = ' + QuotedStr(Title));

      ADOQuery1.SQL.Add(',Artist = ' + QuotedStr(Artist));

      ADOQuery1.SQL.Add(',Album = ' + QuotedStr(Album));

      ADOQuery1.SQL.Add(',Track = ' + QuotedStr(TrackString));

      ADOQuery1.SQL.Add(',TrackAmount = ' + QuotedStr(TrackAmount));

      ADOQuery1.SQL.Add(',sYear = ' + QuotedStr(Year));

      ADOQuery1.SQL.Add(',Genre = ' + QuotedStr(Genre));

      ADOQuery1.SQL.Add(',Band = ' + QuotedStr(Band));

      ADOQuery1.SQL.Add(',Remixed = ' + QuotedStr(Remixed));

      ADOQuery1.SQL.Add(',Publisher = ' + QuotedStr(Publisher));

      ADOQuery1.SQL.Add(',BPM = ' + QuotedStr(BPM));

      ADOQuery1.SQL.Add(',Comment = ' + QuotedStr(Comment));

      ADOQuery1.SQL.Add(',sKey = ' + QuotedStr(key));

      ADOQuery1.SQL.Add('WHERE FullFileName = ' + QuotedStr(Filename));

      end; // do Update

     try

     ADOQuery1.ExecSQL;

     except //If there is a error

      on e : exception do begin

       showmessage(e.Message)

       end;

     end; //End Try

   End; // with Id3v2

End;

Open in new window

0
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 21859456
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
0
 
LVL 1

Author Comment

by:satmanuk
ID: 21859597
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';



0
 
LVL 1

Author Comment

by:satmanuk
ID: 21859943
my bad....sorted it..

Thanks for your help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi XE2 application frozen on Windows 10 10 277
select query - oracle 16 95
Create a path if not exists 7 69
how to change, disabled button color FMX ? 1 16
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now