SQL Mobile slower than SQL CE 2.0

[mattklassen]

Based on what I've read about performance improvements in SQL Mobile, I
have started down the road to migrate an application from VS 2003/SQL
CE 2 to VS 2005/SQL Mobile. So far, however, I am sorely disappointed
with the performance of the new platform. In fact, filling a datatable
with one of my queries has proved to take over 10 times as long on the
new platform.

This is such a big difference that I'm sure I must be doing something
wrong. I'll post my code and I would appreciate if anyone has any
ideas for me.

Dim sSQL$
Dim oCmd As New SqlCeCommand
Dim oDA As New SqlCeDataAdapter
Dim oDT As New Data.DataTable

sSQL = "SELECT Questions.ID AS QID..."

Try
Dim Conn As Data.SqlServerCe.SqlCeConnection = New
Data.SqlServerCe.SqlCeConnection("Data Source=\CF Card\DB\DB1.sdf")
oCmd.Connection = Conn
oCmd.CommandText = sSQL
oDA.SelectCommand = oCmd
oDA.Fill(oDT)
'(I've also tried the new SqlCeResultSet without much better
success: Dim rs As SqlCeResultSet =
oCmd.ExecuteResultSet(ResultSetOptions.None) )
Catch ex As Exception
Throw New Exception("Error" & vbCrLf & vbCrLf & ex.Message)
End Try

Thanks

 

[mohitchawla@mindfiresolutions]

Hi,
While viewing your code one thing that comes to my mind is that you are
using a dataadapter to be able to fill the datatable. Considering you
have large amount of data, it would be better if u use a datareader.

I 'll explain:

By filling a DataTable you are calling the dataAdapter, The dataadapter
internally calls a datareader only, so it will fill the data row by row
in the datareader first and then using the dataadapter the full chunk
will be taken to the datatable.

Therefore it is recomended that you use a datareader directly incase of
only selecting
records from the database.

Now if you judge the performance, you may find a considerable
improvement in the speed and time.

Incase you are still in doubt, Please reply back to me.

mohit chawla
mindfire solutions, india

 

[Coder]

Thank you very much for your response. I agree that the DataReader
would most likely be faster than the DataAdapter (I'll change it now and
verify that), but my real question is why the SAME code works 10 times
slower on the new VS 2005/SQL Mobile platform than it did on the old (VS
2003/SQL CE 2.0) platform. From the postings that I've read, SQL Mobile
is supposed to be significantly faster than SQL CE 2.0.

Something that I should also mention that my experimenting has brought
to light - in my query I've got INNER JOINs, and that seems to slow
things down exponentially. I just thought I'd mention it, but I can't
at this point denormalize my database.

Matt

 

[Guest]

Are there index differences between the two? If you're joining, an index
will likely improve the speed ten fold.

-Chris

 

[Coder]

Are there index differences between the two? If you're joining, an
index will likely improve the speed ten fold.

The databases are exactly the same - I used the same schema and insert
files to create both SQL CE 2.0 and SQL Mobile databases.

 

[Joseph Byrns]

It seems to me that SQL Mobile is REALLY slow on the first open/connection
within the application (regardless of how you get data from the database),
after that it seems to be OK. It is quite irritating.

 

[Coder]

Yeah, I've noticed that too. However, all of my tests are done after
conn.Open() has been called once already, and STILL the performance is
brutal.

 

[Simon Hart]

I must admit I find it considerably faster than v2. I am using a simple
database layout though with no more than 10k records. Most SQL queries are
simple one table SELECT statements.