Solved

Writing data to database in thread

Posted on 2008-06-24
7
233 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

12 Experts available now in Live!

Get 1:1 Help Now