Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Writing data to database in thread

Posted on 2008-06-24
7
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Accepted Solution

by:
SteveBay earned 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

721 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