System.Data.SQLite

Hello,

I precise that i read Robert's post speaking about SQLite Provider in multi-thread environnement. I am programming with  the compact framework under WinCE 5.0.

What i do is very classic. I have an DialogBox (A) to search a user in the database (about 67 000 records in database). After the user select/enter some criteria he send request. A new Dialog box (B) open printing how many records are found and the elapse time.

Because of limited power the execution time can reach 30 minutes. So user can cancel using a button DialogBox B. The elapse time is update with a timer in dialogBox B. I test timer separately : it is ok;

The SQLiteDataReader loop is executed in a separated thread an in a cloned SQLIteConnection to allow DialogBox refresh. The result is writen (using invoke) in a ListView of the DialogBox A while the record count is refresh (still with invoke) in the dialogBox B.

At the begining of the research, numerous records are found an the DialogBox B is well refresh, but later the number of record fall under than 3 per minutes and the DialogBox B is no more refresh. It seems that SQLiteDataReader doesn't release the hand until it find the next record.  

 Is there any Workaround to my problem ?

Thanks by advance for your help.

ps : a so long explain for a so sort question ;)

Searching 67.000 records should complete in at most 10 seconds, so there is no need for threading. Test your program in the MS Device Emulator. If it takes as long as on your PDA there is probably something very wrong with your program & you will have to provide code details.

Hello,

You are right, my code was wrong.

I cut it in two parts 1) searching 2) filling MS Form ListView Control.

The first part takes about 16 secondes what ever records are returns. The second reach 15 minutes when the query retutns 2800 records. Of course it will be longer with more records !!! crazy.

When i fill the list, all cpu time is grab by the filling thread  and forms are not refreshed. I added a line "Application.DoEvent()" to force windows messages dispatching. Times are the same (15 minutes) but forms are alive. User can now cancel query that is a correct solution for me.

I Thanks a lot for your help.

 Marc

Are you filling the ListView correctly? When adding multiple records one-at-a-time to ListView, you should instruct the ListView to redraw itself only after you have added the last record, rather than each time a record is added. For instance:

ListView list_view = ...;
IEnumerable<myrecord> records = ...; // from database...

list_view.BeginUpdate();
foreach (MyRecord r in records)
  list_view.Items.Add(...new item from 'r'...);
list_view.EndUpdate();

Alternately, if you have already retrieved all the data from the database, you can prepare the ListViewItem instances ahead of time, and then add them to the ListView in a single fast operation via ListView.AddRange().

Hello Sunshine,

My code looks like yours. I just added "Application.DoEvent()" in the foreach loop. I use this because the AddRange doesn't exit in the Compact Framework (unfortunatly). .Remeber this program works on a PDA.

I wrote a simple test program which adds 3000 two-column rows to a ListView. In the device emulator, the 3000 rows are added in about 7 seconds. On a real device, filling the ListView will be faster. Note that I did not use a separate thread.

I can think of at least a couple reasons for the slowness you experience.

First, if many temporary objects are being created as you read the data and/or fill the ListView, then this could cause slow down. Object allocation is expensive, and so is garbage collection -- which may occur frequently if a lot of temporary objects are created. Temporary objects may get created in many different ways, so it is important to review your code to see if it is doing wasteful things.

Second, thread synchronization via Control.Invoke() can be very slow, especially if you are doing this for every single returned row. Unless there is a good reason to be filling the ListView as each row arrives from the database query, then you would be better off taking a different approach. For instance, you could fill the ListView in batches, say, use a single Invoke() to add a batch each time 100 records is retrieved from the database. Even better, would be to wait for the entire query to complete and then use Invoke() just once to fill the ListView in one step.

Probably best, however, would be to eliminate the second thread altogether, and do the query and ListView population in the main thread. If, on average, your query takes only several seconds (5-20) and filling the ListView, on average, takes only several seconds (1-10), then it would be sufficient (and much simpler) to do all the work in the main thread.

Hello, Sunshine,

Thanks for you interest for my project.

I tried your sugestions but without success.I verifyed my code looking for temporary objects and I puted all the code in the main thead.

There where no temporary objects : i enum DataReader to string[ and i create a new object ListViewItem with that string[.

As you said, invoke may waspe time so i  hoped that remove them (i.e using one thread only) could help, but it is no the case !!! Times are exactly the same. I seems that Item creation / adding in the list is very (very) long and the time gain in changing loop technique is insignificant regarding it.

I noticed something strange : at the beginning, the number of records added increase by 40 per secondes but this number fall down to 2 per secondes when total records in the list is about 2500. The first thousand records are added in 2 min 45 secondes but it takes 10 minutes 10 s to reach 2000 !!

Any idea ?  

Here the code. I translated comments

formWait is a dialog box which print elapse time (timer in formWait class) and records count (set by formWait.SetLabel)

public void SetDataLayout<T>(T dataLayout) where T : IListViewLayout

if (this.dataLayout != null)

this.dataLayout.Dispose();

this.dataLayout = dataLayout;

// Setup windows title

this.Text = dataLayout.GetCaption();

tbChoixUtilNom.Focus();

// Begin Data Feeding

lvChoixUtilListe.BeginUpdate();

// Add Columns headers

lvChoixUtilListe.Columns.Clear();

foreach (ColumnHeader ch in dataLayout.Headers)

lvChoixUtilListe.Columns.Add(ch);

lvChoixUtilListe.EndUpdate();

// Clear actual datas

lvChoixUtilListe.Items.Clear();

// Show Wait windows 

formWait = new FormChoixUtilAttente();

formWait.Show();

DateTime startTime = DateTime.Now; int timeToUpdate = Environment.TickCount;

// Suspend List Layout

lvChoixUtilListe.BeginUpdate();

int i = 0;

foreach (string[ stringRow in dataLayout.StringRows)

{

ListViewItem lvi = lvChoixUtilListe.Items.Add(new ListViewItem(stringRow));

lvi.Tag = i;

if (i == selectedIdx)

{

lvChoixUtilListe.Items[i].Checked = true;

}

i++;

if (timeToUpdate < Environment.TickCount)

{

timeToUpdate = Environment.TickCount + 1000;if (formWait != null)

{

if (formWait.IsCancelAsked)

break;

formWait.SetLabel(i.ToString());

}

}

}

if (formWait != null)

{

if (formWait.IsCancelAsked)

{

lvChoixUtilListe.Items.Clear();

}

// Ferme la boite d'attente

formWait.Close();

}

EnableCheckItemList(true);

lvChoixUtilListe.EndUpdate();

I pasted your SetDataLayout() method directly into a test program, and it worked fine. With the device emulator, SetDataLayout() consistently filled a four-column ListView with 3000 rows in seven or eight seconds, so the problem does not seem to be with this code.

There is one particular bit of slowness in SetDataLayout() where it re-creates the columns before clearing the rows. If the ListView already contains data, ListView will access every row each time a column is added or removed. In my tests, re-creating the columns with 3000 rows already in the ListView (from a previous query) added several seconds to the execution time. It is faster to clear the rows before re-creating the columns. For instance:

• BeginUpdate()
• Items.Clear()
• Columns.Clear()
• foreach (ColumnHeader ...) Columns.Add(...) 
• foreach (string[ ...) Items.Add(...)
• EndUpdate()

A couple other minor comments: Rather than 'lvChoixUtilListe.Items[i].Checked', you can use 'lvi.Checked'. Also, you should invoke formWait.Dispose() when you are done with the form and also set the variable to null or make the variable local so that it goes out of scope at the end of SetDataLayout().

I can send my test program to you if you would like to run it for yourself.

I correct my code with your indications. I tested the same code without the lvChoixUtilListe.Items.Add() but with new ListViewItem(stringRow)

and the running time fall down to 25 secondes. The Add method is expensive in time probably because of memory limitation ( 20 Mb usable).

sunshine:

I can send my test program to you if you would like to run it for yourself.

Yes it should be interesting.

m3z:

I correct my code with your indications. I tested the same code without the lvChoixUtilListe.Items.Add() but with new ListViewItem(stringRow) and the running time fall down to 25 secondes. The Add method is expensive in time probably because of memory limitation ( 20 Mb usable).

If it was somehow writing to a memory card, then that could be quite slow, though it is not obvious how that would be happening. Typically, in a low-memory situation, the program would simply abort due to insufficient resources.

 m3z,

what device are you using ?

Hello, Sunshine

Sorry to be late to respond, i was working in my other office in La Defense / France since the two last days. Actualy i work in Saint Quentin en Yvelines (~ 30 km from Paris)   I thanks for all what you do :)

I am working on 2  Datalogic devices : the Skorpio and memor which are code bar scanners working under Windows CE 5. The processor is an intel ARM.

It looks like that  : http://memor.datalogic.com/, and http://skorpio.datalogic.com/

Bye

m3z:

sunshine:

I can send my test program to you if you would like to run it for yourself.

Yes it should be interesting.

To where shall I send the test project?

 Hi m3z,

the Memor has a 200MHz processor which ist quite weak for a device running WM5. You can try the following things:

1. Make a coldstart of the memor and look whether this speeds things up.

2. Download http://www.dotfred.net/TaskMgr.htm (an indispensible tool for every mobile developer) and run it on the device. Look for processes consuming memory and/or cpu.

3. Redesign your program. What sense does it make, to display thousands of records on a display so small ? I limit display to 50 records, if that's not enough the user has to narrow his selection criteria.

4. Programming "tricks" as asynchronous threads tend to make things even worse, refrain from it. I display the waitcursor if the operation takes 2-10 seconds. If it takes longer, show the processed records every second or so.

Regards
Mark