satmanuk
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.st rings[I]);
Synchronize(UpdatePosition );
Form1.label1.Caption := Inttostr(I) + '/' + Inttostr(sFileList.Count - 1);
application.ProcessMessage s;
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(Filenam e);
If ID3V2.Exists Then
With ID3V2 Do
Begin
Try
ADOTable1.close;
ADOTable1.open;
ADOTable1.Insert;
ADOTable1.FieldByName('Ful lFileName' ).AsVarian t := Filename;
ADOTable1.FieldByName('Tit le').AsVar iant := Title;
ADOTable1.FieldByName('Art ist').AsVa riant := Artist;
ADOTable1.FieldByName('Alb um').AsVar iant := Album;
ADOTable1.FieldByName('Tra ck').AsVar iant := TrackString;
ADOTable1.FieldByName('Tra ckAmount') .AsVariant := TrackAmount;
ADOTable1.FieldByName('sYe ar').AsVar iant := Year;
ADOTable1.FieldByName('Gen re').AsVar iant := Genre;
ADOTable1.FieldByName('Ban d').AsVari ant := Band;
ADOTable1.FieldByName('Rem ixed').AsV ariant := Remixed;
ADOTable1.FieldByName('Pub lisher').A sVariant := Publisher;
ADOTable1.FieldByName('BPM ').AsVaria nt := BPM;
ADOTable1.FieldByName('Com ment').AsV ariant := Comment;
ADOTable1.FieldByName('sKe y').AsVari ant := 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('Tit le').AsVar iant := Title;
ADOQuery1.FieldByName('Art ist').AsVa riant := Artist;
ADOQuery1.FieldByName('Alb um').AsVar iant := Album;
ADOQuery1.FieldByName('Tra ck').AsVar iant := TrackString;
ADOQuery1.FieldByName('Tra ckAmount') .AsVariant := TrackAmount;
ADOQuery1.FieldByName('sYe ar').AsVar iant := Year;
ADOQuery1.FieldByName('Gen re').AsVar iant := Genre;
ADOQuery1.FieldByName('Ban d').AsVari ant := Band;
ADOQuery1.FieldByName('Rem ixed').AsV ariant := Remixed;
ADOQuery1.FieldByName('Pub lisher').A sVariant := Publisher;
ADOQuery1.FieldByName('BPM ').AsVaria nt := BPM;
ADOQuery1.FieldByName('Com ment').AsV ariant := Comment;
ADOQuery1.FieldByName('sKe y').AsVari ant := 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.
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.st
Synchronize(UpdatePosition
Form1.label1.Caption := Inttostr(I) + '/' + Inttostr(sFileList.Count - 1);
application.ProcessMessage
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(Filenam
If ID3V2.Exists Then
With ID3V2 Do
Begin
Try
ADOTable1.close;
ADOTable1.open;
ADOTable1.Insert;
ADOTable1.FieldByName('Ful
ADOTable1.FieldByName('Tit
ADOTable1.FieldByName('Art
ADOTable1.FieldByName('Alb
ADOTable1.FieldByName('Tra
ADOTable1.FieldByName('Tra
ADOTable1.FieldByName('sYe
ADOTable1.FieldByName('Gen
ADOTable1.FieldByName('Ban
ADOTable1.FieldByName('Rem
ADOTable1.FieldByName('Pub
ADOTable1.FieldByName('BPM
ADOTable1.FieldByName('Com
ADOTable1.FieldByName('sKe
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('Tit
ADOQuery1.FieldByName('Art
ADOQuery1.FieldByName('Alb
ADOQuery1.FieldByName('Tra
ADOQuery1.FieldByName('Tra
ADOQuery1.FieldByName('sYe
ADOQuery1.FieldByName('Gen
ADOQuery1.FieldByName('Ban
ADOQuery1.FieldByName('Rem
ADOQuery1.FieldByName('Pub
ADOQuery1.FieldByName('BPM
ADOQuery1.FieldByName('Com
ADOQuery1.FieldByName('sKe
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.
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,
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Its the full version of MS SQL 2005, Works nice at times ;)
I have changed the function to
Procedure TForm1.AddSongToDB(Filenam e: String);
Begin
ID3V2.ReadFromFile(Filenam e);
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('Ful lFileName' ).AsVarian t := filename;
ADOQuery1.FieldByName('Tit le').AsVar iant := Title;
ADOQuery1.FieldByName('Art ist').AsVa riant := Artist;
ADOQuery1.FieldByName('Alb um').AsVar iant := Album;
ADOQuery1.FieldByName('Tra ck').AsVar iant := TrackString;
ADOQuery1.FieldByName('Tra ckAmount') .AsVariant := TrackAmount;
ADOQuery1.FieldByName('sYe ar').AsVar iant := Year;
ADOQuery1.FieldByName('Gen re').AsVar iant := Genre;
ADOQuery1.FieldByName('Ban d').AsVari ant := Band;
ADOQuery1.FieldByName('Rem ixed').AsV ariant := Remixed;
ADOQuery1.FieldByName('Pub lisher').A sVariant := Publisher;
ADOQuery1.FieldByName('BPM ').AsVaria nt := BPM;
ADOQuery1.FieldByName('Com ment').AsV ariant := Comment;
ADOQuery1.FieldByName('sKe y').AsVari ant := 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('Ful lFileName' ).AsVarian t := StringReplace(filename,#39 ,#39#39,[r fReplaceAl l, rfIgnoreCase]);
tried that because this works.
test :=' captian''s heart';
I have changed the function to
Procedure TForm1.AddSongToDB(Filenam
Begin
ID3V2.ReadFromFile(Filenam
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('Ful
ADOQuery1.FieldByName('Tit
ADOQuery1.FieldByName('Art
ADOQuery1.FieldByName('Alb
ADOQuery1.FieldByName('Tra
ADOQuery1.FieldByName('Tra
ADOQuery1.FieldByName('sYe
ADOQuery1.FieldByName('Gen
ADOQuery1.FieldByName('Ban
ADOQuery1.FieldByName('Rem
ADOQuery1.FieldByName('Pub
ADOQuery1.FieldByName('BPM
ADOQuery1.FieldByName('Com
ADOQuery1.FieldByName('sKe
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('Ful
tried that because this works.
test :=' captian''s heart';
ASKER
my bad....sorted it..
Thanks for your help
Thanks for your help
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