|
|
Performance issue in Compact Framework
-
 04-21-2008, 12:23 PM |
-
roquevera
-
-
-
Joined on 07-09-2007
-
Spain
-
Posts 4
-
-
|
Performance issue in Compact Framework
Hello,
I am porting an old application to use Entityspaces but I have encountered a problem with the performance.
First step in my application is to bring data from some web services and populate tables in a local .sdf.
When we use Entityspaces, the time is 2x the old process took.
I have prepared two code snippets to see if you can help me in any way.
First, the old function.
Code: Private Sub testcarga()
Dim SQL As String = ""
Dim m_Conexion As SqlServerCe.SqlCeConnection = Nothing
m_Conexion = New SqlServerCe.SqlCeConnection("Data Source=test.sdf")
m_Conexion.Open()
For i As Integer = 1 To 1000
SQL = " INSERT INTO USOS_OFF "
SQL = SQL & " ( "
SQL = SQL & " ID_ACT , "
SQL = SQL & " ID_ST_USAGE , "
SQL = SQL & " ID_ST_OVERRIDE , "
SQL = SQL & " ID_TIPOLECTURA , "
SQL = SQL & " UseTime , "
SQL = SQL & " VisualID , "
SQL = SQL & " AccessCode , "
SQL = SQL & " IDNo , "
SQL = SQL & " Code , "
SQL = SQL & " Qty , "
SQL = SQL & " UseNo , "
SQL = SQL & " Operator , "
SQL = SQL & " SerialNo , "
SQL = SQL & " UsageCondition , "
SQL = SQL & " OriginalStatus , "
SQL = SQL & " BankNo , "
SQL = SQL & " RecordVersion , "
SQL = SQL & " LastUpdate , "
SQL = SQL & " LastUpdatedBy , "
SQL = SQL & " xmlTicket "
SQL = SQL & " ) "
SQL = SQL & "VALUES "
SQL = SQL & " ( "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? "
SQL = SQL & " ) "
Dim insertCommand As SqlServerCe.SqlCeCommand = m_Conexion.CreateCommand()
insertCommand.CommandText = SQL
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_ACT", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_ST_USAGE", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_ST_OVERRIDE", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_TIPOLECTURA", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("UseTime", Now))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("VisualID", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("AccessCode", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("IDNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("Code", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("Qty", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("UseNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("Operator", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("SerialNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("UsageCondition", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("OriginalStatus", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("BankNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("RecordVersion", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("LastUpdate", Now))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("LastUpdatedBy", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("xmlTicket", i))
insertCommand.ExecuteNonQuery()
Next
m_Conexion.Close()
m_Conexion = Nothing
End Sub
And now, the same function using entityspaces: Code: Private Sub testcarga_es()
For i As Integer = 1 To 1000
Dim esUsosOff As New UsosOff
esUsosOff.IdAct = i
esUsosOff.IdStUsage = i
esUsosOff.IdStOverride = i
esUsosOff.IdTipolectura = i
esUsosOff.UseTime = Now
esUsosOff.VisualID = i
esUsosOff.AccessCode = i
esUsosOff.IDNo = i
esUsosOff.Code = i
esUsosOff.Qty = i
esUsosOff.UseNo = i
esUsosOff.Operator = i
esUsosOff.SerialNo = i
esUsosOff.UsageCondition = i
esUsosOff.OriginalStatus = i
esUsosOff.BankNo = i
esUsosOff.RecordVersion = i
esUsosOff.LastUpdate = Now
esUsosOff.LastUpdatedBy = i
esUsosOff.Save()
Next
End Sub
I hope I am doing something wrong.
Kind regards,
Roque Vera.
Ps: sorry for my poor english
|
|
-
| 04-21-2008, 12:42 PM |
-
Mike.Griffin
-
-
-
Joined on 01-14-2007
-
Indianapolis
-
Posts 2,468
-
-
|
Re: Performance issue in Compact Framework
Well, first you are opening and holding a connection open in your test, ES of course opens and closes (connection pooling of course helps) on each save. However, try this: Post your new times, I'm curious ...
EntitySpaces | Twitter | BLOG
|
|
-
| 04-21-2008, 12:54 PM |
-
Mike.Griffin
-
-
-
Joined on 01-14-2007
-
Indianapolis
-
Posts 2,468
-
-
|
Re: Performance issue in Compact Framework
Your English is fine by the way ;) I'm sure your new times using the approach I stated above will be much better. However, don't overlook the fact that if you had to write your entire application in your old style you would spend a tremendous amount of time doing grunt work and then tracking down mistakes. You have no strongly typed concept, no intellisense, a hierarchical model, serialization, or any of the other features that ES provides. You in essence, have no architecture based on the old style of code. You can change your database and regenerate EntitySpaces and you're off and running, with the approach you have shown you have no hope of absorbing change without some real pain and tracking down runtime issues.
I know you probably know all of that but it's somewhat unfair to take low level code like that and wonder why ES might be slower, however, I have great hopes in your next report.
EntitySpaces | Twitter | BLOG
|
|
-
| 04-21-2008, 1:28 PM |
-
roquevera
-
-
-
Joined on 07-09-2007
-
Spain
-
Posts 4
-
-
|
Re: Performance issue in Compact Framework
Hello Mike, I have tried your observations and these are the times I get:
- With "native" sqlcecommand: 2 minutes and 5 seconds
- Using UsosOff (no collection):5 minutes and 24 seconds
- Using UsosOffcollection (yours):4 minutes and 36 seconds
As you can see, the time with your solution is better than is better than the previous one but it is still very slow considering that I have just reduced code a lot (real code has to make a LoadByPrimatyKey to decide if it's a new element or it has to update and old one).
Thanks for your help,
Roque Vera
Code: Private Sub testcarga_escol()
Dim escolUsosOff As New UsosOffCollection
Dim esUsosOff As UsosOff
For i As Integer = 1 To 1000
esUsosOff = escolUsosOff.AddNew()
esUsosOff.IdAct = i
esUsosOff.IdStUsage = i
esUsosOff.IdStOverride = i
esUsosOff.IdTipolectura = i
esUsosOff.UseTime = Now
esUsosOff.VisualID = i
esUsosOff.AccessCode = i
esUsosOff.IDNo = i
esUsosOff.Code = i
esUsosOff.Qty = i
esUsosOff.UseNo = i
esUsosOff.Operator = i
esUsosOff.SerialNo = i
esUsosOff.UsageCondition = i
esUsosOff.OriginalStatus = i
esUsosOff.BankNo = i
esUsosOff.RecordVersion = i
esUsosOff.LastUpdate = Now
esUsosOff.LastUpdatedBy = i
Next
escolUsosOff.SaveAndDiscard()
End Sub
|
|
-
| 04-21-2008, 1:33 PM |
-
Mike.Griffin
-
-
-
Joined on 01-14-2007
-
Indianapolis
-
Posts 2,468
-
-
|
Re: Performance issue in Compact Framework
When you do you timings you should always run them once without timing and then time the 2nd run (without restarting the app) to eliminate any first time loading issues (such as assemblies). I will try this on SQL Server (not CE as I don't have that setup right now) but would be very surprised to see us be twice as slow as raw sql. EntitySpaces is also bringing back the identity column which is not in your test (if you have identity columns).
EntitySpaces | Twitter | BLOG
|
|
-
| 04-21-2008, 11:41 PM |
-
roquevera
-
-
-
Joined on 07-09-2007
-
Spain
-
Posts 4
-
-
|
Re: Performance issue in Compact Framework
Hello Mike, I have done the next test:
Code:
Private Sub testcarga()
Dim SQL As String = ""
Dim m_Conexion As SqlServerCe.SqlCeConnection = Nothing
For i As Integer = 1 To 1000
m_Conexion = New SqlServerCe.SqlCeConnection("Data Source=test.sdf")
m_Conexion.Open()
SQL = " INSERT INTO USOS_OFF "
SQL = SQL & " ( "
SQL = SQL & " ID_ACT , "
SQL = SQL & " ID_ST_USAGE , "
SQL = SQL & " ID_ST_OVERRIDE , "
SQL = SQL & " ID_TIPOLECTURA , "
SQL = SQL & " UseTime , "
SQL = SQL & " VisualID , "
SQL = SQL & " AccessCode , "
SQL = SQL & " IDNo , "
SQL = SQL & " Code , "
SQL = SQL & " Qty , "
SQL = SQL & " UseNo , "
SQL = SQL & " Operator , "
SQL = SQL & " SerialNo , "
SQL = SQL & " UsageCondition , "
SQL = SQL & " OriginalStatus , "
SQL = SQL & " BankNo , "
SQL = SQL & " RecordVersion , "
SQL = SQL & " LastUpdate , "
SQL = SQL & " LastUpdatedBy , "
SQL = SQL & " xmlTicket "
SQL = SQL & " ) "
SQL = SQL & "VALUES "
SQL = SQL & " ( "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? , "
SQL = SQL & " ? "
SQL = SQL & " ) "
Dim insertCommand As SqlServerCe.SqlCeCommand = m_Conexion.CreateCommand()
insertCommand.CommandText = SQL
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_ACT", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_ST_USAGE", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_ST_OVERRIDE", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("ID_TIPOLECTURA", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("UseTime", Now))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("VisualID", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("AccessCode", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("IDNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("Code", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("Qty", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("UseNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("Operator", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("SerialNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("UsageCondition", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("OriginalStatus", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("BankNo", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("RecordVersion", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("LastUpdate", Now))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("LastUpdatedBy", i))
insertCommand.Parameters.Add(New SqlServerCe.SqlCeParameter("xmlTicket", i))
insertCommand.ExecuteNonQuery()
m_Conexion.Close()
m_Conexion = Nothing
Next
End Sub
It's first test using sqlcecommand but creating and disposing connection in every loop. Now time has gone to 4 minutes so it´s equivalent to ES time. I can conclude ES could get a better performance with Compact SQL if objects could use an open connection instead of opening and closing everytime. Is there a way to do it? I am using Compact SQL 3.0 (not 3.5) with latest version of ES. Thanks for your help and kind regards, Roque Vera.
|
|
-
| 04-22-2008, 2:54 AM |
-
Mike.Griffin
-
-
-
Joined on 01-14-2007
-
Indianapolis
-
Posts 2,468
-
-
|
Re: Performance issue in Compact Framework
Ahhh, you know, there must be no connection pooling on CE devices, that makes sense. One way to hold a connection open in ES is to use an esTransactionScope, but not sure transactions are supported on CE devices, try this?
using(esTransactionScope scope = new esTransactionScope())
{
the code ....
scope.Complete();
} We have been asked for the ability to hold connections open before, but we are database independent, however, I have been thinking of such an API that would be something like this:
using(esConnection conn = new esConnection())
{
the code ....
}
I sure hope you can use the esTransactionScope, give it a try.
EntitySpaces | Twitter | BLOG
|
|
-
| 04-22-2008, 4:07 AM |
-
roquevera
-
-
-
Joined on 07-09-2007
-
Spain
-
Posts 4
-
-
|
Re: Performance issue in Compact Framework
Hello again Mike,
It seems CE devices support transactions. I changed my code to make all inserts in one transaction and time went down to 3 minutes and 10 seconds.
It only worked when no collections are used (my original code). If I use collections, the elapsed time is the same.
Well, time is now 50% higher than using sqlcecommand so I'll change real code to use transactions and let's hope my boss accepts it.
Anyway, I hope you'll keep this issue as something to improve in new versions. It seems that it's more a fault on SQL Server Compact (no support for connection pooling) but I hope you can improve performance of ES in CE devices.
Again, thank you very much for your help.
Roque Vera
|
|
-
| 04-22-2008, 4:24 AM |
-
Mike.Griffin
-
-
-
Joined on 01-14-2007
-
Indianapolis
-
Posts 2,468
-
-
|
Re: Performance issue in Compact Framework
I'm going to take a look at allowing the new syntax: using(esConnection conn = new esConnection())
{
the code ....
}
EntitySpaces | Twitter | BLOG
|
|
|
|
|