Mobile benchmark (including DataAdapter and Dataset usage)

Last post 12-31-2010 3:35 AM by Miguel. 9 replies.

Page 1 of 1 (10 items)
	Sort Posts: Previous Next

03-12-2009 8:35 AM

blega
Joined on 03-12-2009
Posts 1

Mobile benchmark (including DataAdapter and Dataset usage)

MobileDB.Benchmark.2.zip

Reply Contact

Hello,

I updated the benchmark application written by Robert Simpson.

I add test scenarios to include Dataset and DataAdapter usage.

To my surprise, in some case, the execution time for SqliteCE is much higher than SqlServer CE's !! (see the .dataset cases in the overall performance chart)

What do you think ?

Please, find attached the code, the excel report (with queries) , and the overall performance chart.

Don't hesitate to post your suggestions /remarks.

Cheers,

Bertrand.

03-17-2009 2:16 PM In reply to

Mark2
Joined on 04-24-2008
Posts 70

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

I observed a similar problem on the following scenario:
HP iPAQ hx2490, 1MB textfile is read and results in 19.000 inserts into 2 tables with indices, CF.NET VB-program with sqlitephx-1.0.48. This took 18.5 minutes.
I then did some performance tests and experimenting:
- replaced sqlitephx with own thin wrapper and native sqlite
- used sqlite3_exec and sqlite3_get_table
- ripped out all datasets, tables and adapters

After all optimizations the program ran 133 seconds with unchanged program logic, a reduction of the runtime by nearly 90%. I cannot tell exactly where from the performance boost originated, but identified the following points:
- compiling the sqlite core with the maximize speed-options accelerates the program by 1/3
- the program compiled with VS2008 runs 10% faster than compiled with VS2005
- the dataset, table and adapter-stuff seems to suck a significant amount of time as well as the phx-wrapper
- the SQlite core is growing fatter each release, the functions added are not relevant for a mobile device, but seem to pose a small performance penalty in each new release.

Summary: The phx-wrapper is not well suited for mass data processing on mobile devices. For a typical dialog application performance is sufficient.

03-17-2009 2:34 PM In reply to

Robert Simpson
Joined on 08-28-2005
Posts 2,712

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

I'd like to see how you did the inserts using the .NET wrapper before commenting. I agree that datasets and adapters significantly increase the amount of time it takes to process bulk data.

However, I've bulk loaded a lot more than 19,000 rows of data on the CE emulator using the wrapper in a lot less time than you're saying a live device took.

03-18-2009 2:24 AM In reply to

Mark2
Joined on 04-24-2008
Posts 70

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

Unfortunately I do not have the original version, but an intermediate one with the System.data stuff already removed. The logic of the loop is unchanged through all versions.

      Const BEGTRANS As String = "BEGIN TRANSACTION;" & Microsoft.VisualBasic.ControlChars.NewLine
      Const ENDTRANS As String = "COMMIT TRANSACTION;"
      Dim sb As New System.Text.StringBuilder(BEGTRANS)
      Do Until EOF
         Call Read_Line(tr, str, line, EOF)
         a = str.Split(Sep)
         Records = db2.GetSingleValue("SELECT COUNT(*) FROM WEP WHERE WEKey=" & a(EWepFile.WEKey))
         If Records = 0 Then
            sb.Append("INSERT INTO WEP VALUES(" & str & ");")
            WEPCopied += 1
            Records = db2.GetSingleValue("SELECT COUNT(*) FROM LArt WHERE EAN=" & a(EWepFile.EAN) & " AND Seller=" & a(EWepFile.Seller))
            If Records = 0 Then
               sb.Append("INSERT INTO LArt VALUES(")
               ...
            End If
            'Commit every 500 records
            If (WEPCopied Mod 500) = 0 Then
               sb.Append(ENDTRANS)
               Call db2.Execute(sb.ToString)
               sb = New System.Text.StringBuilder(BEGTRANS)
            End If
         End If
         System.Windows.Forms.Application.DoEvents()
      Loop
      If sb.Length > BEGTRANS.Length Then
         sb.Append(ENDTRANS)
         Call db2.Execute(sb.ToString)
      End If

03-18-2009 8:02 AM In reply to

Robert Simpson
Joined on 08-28-2005
Posts 2,712

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

I don't suppose you'd be interested in sending me the text file and your insert logic (and the db schema of those two tables) so I can run some tests?

robert at blackcastlesoft dot com

03-19-2009 8:27 AM In reply to

Mark2
Joined on 04-24-2008
Posts 70

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

How would want it, by mail ?
BTW: You never commented the not functioning "pragma user_version=xy;". How about that ?

03-19-2009 9:07 AM In reply to

Robert Simpson
Joined on 08-28-2005
Posts 2,712

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

Yes e-mail: robert at blackcastlesoft dot com

As for user_version -- what's the expected usage and benefit of having it in the connection string parameters?

03-31-2009 11:40 AM In reply to

Mark2
Joined on 04-24-2008
Posts 70

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

Sorry, I'm quite busy these times.

The "pragma user_version" has nothing to do with the connection string, where indeed it would be quite useless.

10-27-2010 2:16 AM In reply to

debrac
Joined on 10-20-2010
Posts 5

Re: Mobile benchmark (including DataAdapter and Dataset usage)

Reply Contact

Thanks you. Excellent!

http://declutterdaytips.com/declutter-kitchen/

12-31-2010 3:35 AM In reply to

Miguel
Joined on 12-30-2010
Posts 1

Re: Mobile benchmark (including DataAdapter and Dataset usage)

benchlog.rar

Reply Contact

Hi, I ran the app and the csv contained nearly nothing (see attachment), what am I doing wrong? Kind Regards, Miguel

Page 1 of 1 (10 items)

System.Data.SQLite