GUI lockup while inserting to wxSqlite DB... Topic is solved

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
Post Reply
baka_bai
Experienced Solver
Experienced Solver
Posts: 99
Joined: Fri Sep 30, 2005 10:20 pm
Contact:

GUI lockup while inserting to wxSqlite DB...

Post by baka_bai »

I was wasn't sure if I should put this in the C++ cat. or wxCode...
so if its in the wrong place please move it.

Basically what seems to happen is the GUI locks-up while inserting new rows to the database though it should be updating a counter in the status bar...
I believe it seems to lockup when ever there is an event passed through the event table... But I also noticed that it sometimes locks up when minimizing say the task manager or another window. Or if its left alone with no mouse movement after x number of inserts (usually 15 or 200) it locks up. Though it still is inserting new rows.
If code will help here is:
EVT Table

Code: Select all

BEGIN_EVENT_TABLE(MyFrame, wxFrame)
	EVT_MENU(CLIENT_EXIT,				MyFrame::OnExit)
	EVT_MENU(CLIENT_ABOUT,				MyFrame::OnAbout)
	EVT_MENU(CLIENT_OPEN,				MyFrame::OnOpenConnection)
	EVT_MENU(CLIENT_CLOSE,				MyFrame::OnCloseConnection)
	EVT_MENU(CLIENT_SEND_CMD,			MyFrame::OnSendCMD)
	EVT_MENU(CLIENT_DB,					MyFrame::LoadListToDB)
	EVT_TEXT_MAXLEN(CLIENT_TXT_UPDATE, 	MyFrame::OnTextResize)
	EVT_SOCKET(SOCKET_ID,				MyFrame::OnSocketEvent)
END_EVENT_TABLE()

Code: Select all

void MyFrame::LoadListToDB(wxCommandEvent& event) {
	std::vector <wxString> Failures(2); //Stores Failed Entries
	
	NewsGroup tmpGroup;
	wxString gfile = "groupslist.dat";
	unsigned int lineCtr = 0;
	unsigned int fCount = 0;
	unsigned int success = 0;
	
	wxTextFile file(gfile);
	if (!file.Exists()) {
		m_text->AppendText(_("Group cache list file doesn't exist!\n"));
		return;
	}
	file.Open();
	//lineCtr = file.GetLineCount();
	wxString s;
	s = file.GetFirstLine();
	tmpGroup = m_split.SplitString(s);
	tmpGroup.id << lineCtr + 1;
	if (m_group_db->InsertNewGroup(tmpGroup.id, tmpGroup.GroupName, tmpGroup.lastPost,
					tmpGroup.firstPost, tmpGroup.postable)) {
		success++;
	} else {
		if (fCount >= Failures.size()) { Failures.resize(Failures.size()+15); }
		Failures[fCount] = s;
		fCount++;
	}
	//While Line != the end of list terminator
	m_caching = true;
	while (s != "." || s != '\n') {
		lineCtr++;
		s = file.GetNextLine();
		tmpGroup = m_split.SplitString(s);
		tmpGroup.id << lineCtr;
		
		if (m_group_db->InsertNewGroup(tmpGroup.id, tmpGroup.GroupName, tmpGroup.lastPost,
					tmpGroup.firstPost, tmpGroup.postable)) {
			success++;
		} else {
			//Enlarge Vector elements count 
			if (fCount >= Failures.size()) { Failures.resize(Failures.size()+15); }
			Failures[fCount] = s;
			fCount++;
		}
		
		UpdateGroupBar(lineCtr); //this is suppose to update part of the status bar in a lazy attempt to prevent the GUI from locking
	}
	m_caching = false;
	unsigned int tally = lineCtr - success;
	s << tally << " groups failed to be added to db.\n"
		<< success << " groups have been added to db.\n"
		<< "Group Failures follow: \n";
	for (unsigned int i = 0; i < Failures.size(); i++ ) {
		s << Failures[i];
	}
	
	m_text->AppendText(_(s));
	Failures.resize(1);
}
Also insertion into the db seems kind of slow. Around one row a second, which is not particularly very good when there are approximately 105,000 rows to be inserted. If anyone has any ideas on how I can speed this up it would be most appreciated, and if more code is needed to understand something just ask.
leio
Can't get richer than this
Can't get richer than this
Posts: 802
Joined: Mon Dec 27, 2004 10:46 am
Location: Estonia, Tallinn
Contact:

Re: GUI lockup while inserting to wxSqlite DB...

Post by leio »

baka_bai wrote:I was wasn't sure if I should put this in the C++ cat. or wxCode...
so if its in the wrong place please move it.

Basically what seems to happen is the GUI locks-up while inserting new rows to the database though it should be updating a counter in the status bar...
I believe it seems to lockup when ever there is an event passed through the event table... But I also noticed that it sometimes locks up when minimizing say the task manager or another window. Or if its left alone with no mouse movement after x number of inserts (usually 15 or 200) it locks up. Though it still is inserting new rows.
To update the GUI, you could wxYieldIfNeeded() once in a while. Lets say after every 20 inserts, or whatever works good on most computers. You don't want to yield every time, that will make the whole process too slow, while the perceived responsiveness is too good.
baka_bai wrote:Also insertion into the db seems kind of slow. Around one row a second, which is not particularly very good when there are approximately 105,000 rows to be inserted. If anyone has any ideas on how I can speed this up it would be most appreciated, and if more code is needed to understand something just ask.
Do you use a BEGIN ... COMMIT block for the insertions? For SQLite transactions are very important. I brought an insertion block in a for loop down to 0.1 secs from 10 seconds just by making it a proper transaction - BEGIN (or whatever it was) before the loop, and COMMIT only after I'm done with all. Without that, it has to do disk I/O all the time to autocommit after every insertion, and you can imagine what that does to performance.
Compilers: gcc-3.3.6, gcc-3.4.5, gcc-4.0.2, gcc-4.1.0 and MSVC6
OS's: Gentoo Linux, WinXP; WX: CVS HEAD

Project Manager of wxMUD - http://wxmud.sf.net/
Developer of wxGTK;
gtk+ port maintainer of OMGUI - http://www.omgui.org/
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: GUI lockup while inserting to wxSqlite DB...

Post by utelle »

baka_bai wrote:I was wasn't sure if I should put this in the C++ cat. or wxCode... so if its in the wrong place please move it.
In my opinion your question is not wxCode specific, but since I'm not a forum administrator I can't move the topic.
baka_bai wrote:Basically what seems to happen is the GUI locks-up while inserting new rows to the database though it should be updating a counter in the status bar...
Usually event handling functions should not perform long running tasks. In such cases creating a worker thread might be the better approach.
baka_bai wrote:Also insertion into the db seems kind of slow. Around one row a second, which is not particularly very good when there are approximately 105,000 rows to be inserted.
Without knowing the structure of your database model it's hard to say why your inserts are slow. This could also depend on your event function.
I don't believe it's a matter of the wxSQLite3 wrapper. For example the wxSQLite3 sample application inserts 50,000 records within a few seconds.

Maybe the SQLite documentation on the SQLite web site can give you hints regarding database performance issues. There is also a SQLite mailing list where you can ask SQLite database questions.

Regards,

Ulrich
baka_bai
Experienced Solver
Experienced Solver
Posts: 99
Joined: Fri Sep 30, 2005 10:20 pm
Contact:

Post by baka_bai »

SQLite Structure:

Code: Select all

CREATE TABLE nl_group (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nlr_group TEXT NOT NULL,
last varchar(10) NOT NULL,
first varchar(10) NOT NULL,
post varchar(2) NOT NULL);

CREATE INDEX id_idx ON nl_group(id);
nlr_group was originally varchar(255) but I wasn't sure of how large NewsGroup names can get, so I used a TEXT type to be safe.
Well I used the begin & commit and when it goes to commit the application freezes & crashes.
It doesn't seem to freeze though unless I try to move the window or minimize i now.
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

baka_bai wrote:SQLite Structure:

Code: Select all

CREATE TABLE nl_group (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nlr_group TEXT NOT NULL,
last varchar(10) NOT NULL,
first varchar(10) NOT NULL,
post varchar(2) NOT NULL);

CREATE INDEX id_idx ON nl_group(id);
nlr_group was originally varchar(255) but I wasn't sure of how large NewsGroup names can get, so I used a TEXT type to be safe.
SQLite does not enforce the column types you define when creating the table, that is, you can store numeric values in varchar columns and vice versa. Therefore SQLite3 - and wxSQLite3 - provide methods to find out the actual column type and the declared column type of a column in a result set (methods GetColumnType resp. GetDeclaredColumnType). The declared length restriction isn't enforced, too. For more information see SQLite documentation on datatypes.
baka_bai wrote:Well I used the begin & commit and when it goes to commit the application freezes & crashes.
Since you use indices to get acceptable performance you should definitely use transactions (begin ... commit/rollback). In your above SQL statements it is unnecessary to declare the extra index id_idx since the column id is a primary key and an index is created by SQLite automatically.

Does the commit in your program succeed? Or does an exception occur?
baka_bai wrote:It doesn't seem to freeze though unless I try to move the window or minimize i now.
In regard of wxWidgets event handling unfortunately I can't give advice.

Regards,

Ulrich
baka_bai
Experienced Solver
Experienced Solver
Posts: 99
Joined: Fri Sep 30, 2005 10:20 pm
Contact:

Post by baka_bai »

utelle wrote:Does the commit in your program succeed? Or does an exception occur?
It crashes before it can send or process the exception on the commit.
But it does throw a couple of syntax errors well inserting four or five of the lines.

Heres the updated code:

Code: Select all

try {
	std::vector <wxString> Failures(2); //Stores Failed Entries
	NewsGroup tmpGroup;
	wxString gfile = "groupslist.dat";
	unsigned int lineCtr = 0;
	unsigned int fCount = 0;
	unsigned int success = 0;
	
	wxTextFile file(gfile);
	if (!file.Exists()) {
		m_text->AppendText(_("Group cache list file doesn't exist!\n"));
		return;
	}
	file.Open();
	//lineCtr = file.GetLineCount();
	wxString s;
	s = file.GetFirstLine();
	tmpGroup = m_split.SplitString(s);
	tmpGroup.id << lineCtr + 1;
	m_group_db->StartTrans(); //calls begin
	if (m_group_db->InsertNewGroup(tmpGroup.id, tmpGroup.GroupName, tmpGroup.lastPost,

					tmpGroup.firstPost, tmpGroup.postable)) {
		success++;
	} else {
		if (fCount >= Failures.size()) { Failures.resize(Failures.size()+15); }
		Failures[fCount] = s;
		fCount++;
	}
	//While Line != the end of list terminator
	m_caching = true;
	while (s != "." || s != '\n') {
		lineCtr++;
		s = file.GetNextLine();
		tmpGroup = m_split.SplitString(s);
		tmpGroup.id << lineCtr;
	
		if (m_group_db->InsertNewGroup(tmpGroup.id, tmpGroup.GroupName, tmpGroup.lastPost,
					tmpGroup.firstPost, tmpGroup.postable)) {
			success++;
		} else {
			//Enlarge Vector elements count 
			if (fCount >= Failures.size()) { Failures.resize(Failures.size()+15); }
			Failures[fCount] = s;
			fCount++;
		}
		if ((lineCtr % 100) == 0) {
			UpdateGroupBar(lineCtr);
		}
	}
	UpdateGroupBar(lineCtr);
	m_group_db->EndTrans(); //call commit
	m_caching = false;
	unsigned int tally = lineCtr - success;
	s = "";
	s << tally << " groups failed to be added to db.\n"
		<< success << " groups have been added to db.\n"
		<< "Group Failures follow: \n";
	
	for (unsigned int i = 0; i < Failures.size(); i++ ) {
		s << Failures[i];
	}
	m_text->AppendText(_(s));
	Failures.resize(1);
} catch (wxSQLite3Exception& e) {
	wxString q;
	q << _T("DB Error: ") << e.GetErrorCode() << _T(" : ") << e.GetMessage().mb_str() << _T("\n");
	m_text->AppendText(_T(q));
	q.Clear();
}
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

baka_bai wrote:It crashes before it can send or process the exception on the commit.
But it does throw a couple of syntax errors well inserting four or five of the lines.
You should analyze these syntax errors and eliminate them.

Additionally you must commit or rollback a transaction, since SQLite does not allow nested transactions. This means, you must call rollback in your catch block in case of errors.

Regards,

Ulrich
baka_bai
Experienced Solver
Experienced Solver
Posts: 99
Joined: Fri Sep 30, 2005 10:20 pm
Contact:

Post by baka_bai »

Well I found the source of my syntax errors... and why it was crashing.
Lines that are read in with ' in them like the one below
alt.www.sites.that.don't.suck 0000000003 0000000002 y
cause sqlite to think that this is the end of the content for the column.
INSERT INTO nl_group VALUES (NULL, 'alt.www.sites.that.don't.suck', '0000000003', '0000000002', 'y');
so this will produce this error:
DB Error: 1 : SQLITE_ERROR[1]: near "t": syntax error

I tried to use

Code: Select all

s.Replace("'", "\'", true)
to replace ' with \' but it refuses to recognize and replace it.
Any suggestions on how I can replace that since I will need to maintain the entire group name?

I also didn't have any nested transactions...
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg »

You are using the wrong specifyer. Use %Q instead of %s. sqlite will automatically replace ' with \' for you...

I had a similar problem and could not believe someone had thought about that ;-)

- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

Jorg wrote:You are using the wrong specifyer. Use %Q instead of %s. sqlite will automatically replace ' with \' for you...
As far as I know the standard C/C++ formatting functions don't support the %Q format code, but SQLite provides a specialized version of sprintf. wxSQLite3 makes this function accessible via the method Format of the class wxSQLite3StatementBuffer. The use of this method is described in the wxSQLite3 API documentation.

Regards,

Ulrich
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg »

That is why I said sqlite will transform it for you ;-) the printf was specialized ;-)

- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Post Reply