SQL Compact performance issue

Posts   
 
    
JoshK
User
Posts: 2
Joined: 12-Jul-2011
# Posted on: 12-Jul-2011 18:06:20   

I'm using LLBLGen for a windows mobile application that reads/writes data to a local sql compact 3.5 database and occasionally syncs that data to a server.

The data access portion seems slow and I had attributed that to sql compact. But i did a metrics test using a thousand insert statements between llblgen (self-servicing as well as adapter) and simple sql commands (with parameters) and found vast differences.

1000 inserts LLBLGen: 70 - 80 seconds SQL command: 10 - 15 seconds

I don't want to abandon llblgen, is there something i'm missing that will speed up data read/writes? I have tried units of work, saving each record individually, and using transactions.

The project is C#, Compact Framework 3.5, SQL Compact 3.5, LLBLGen 2.6. Thanks Josh

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 12-Jul-2011 22:10:07   

How did you run your direct SQL statements, was it directly in Query analyser, or in some C# code ?

LLBLGen generally adds very little (or no) overhead so I'm surprised at the differences that you see. Can you try and narrow down where the slow down is coming from a bit for us please ? i.e. are the generated SQL statements executing slower against the database, or is the slow down in the generated code ?

Matt

JoshK
User
Posts: 2
Joined: 12-Jul-2011
# Posted on: 12-Jul-2011 22:16:20   

I used ado.net. Here is the code i used for one of the metrics tests. Keep in mind i've also tried adapter, with and without unit of work and transactions, etc. getting the same results each time

Removed some code for brevity

ADO: using (SqlCeConnection lconn = new SqlCeConnection(Utilities.gsConn)) { SqlCeCommand cmd = new SqlCeCommand("INSERT INTO EventAudit([EventAuditId],[DeviceId],[EventId],[UserName],[FacilityName],[LaneName],[EventAuditDate],[Message],[Synced]) VALUES(@EventAuditId,@DeviceId,@EventId,@UserName,@FacilityName,@LaneName,@EventAuditDate,@Message,@Synced)", lconn); cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlCeParameter("@EventAuditId", SqlDbType.Int, 4, "EventAuditId")); ... cmd.Prepare();

                lconn.Open();
                for (int i = 1; i <= liIterations; i++) {
                    cmd.Parameters[0].Value = i;

... cmd.ExecuteNonQuery(); } lconn.Close(); }

LLBLGen UnitOfWork uow = new UnitOfWork();

            for (int i = 1; i <= liIterations; i++) {
                EventAuditEntity lentEventAudit = new EventAuditEntity();
                lentEventAudit.EventAuditId = i;

... uow.AddForSave(lentEventAudit); }

            uow.Commit(new Transaction(IsolationLevel.ReadCommitted, "Save EventAudit test"), true);

Thanks, Josh

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 12-Jul-2011 22:27:39   

Hmm. Seems a bit odd..! Please could you enable verbose tracing as described here http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm#verboselevelDQE

and have a look at the generated SQL and the timings - it may shed some more light on what is going on under the hood.

Thanks

Matt

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 13-Jul-2011 12:09:34   

It's not an equal test. The code you run using only ADO.NET is just a part of what's done in the llblgen pro code. Also you don't start a transaction in your ado.net code.

What I think is 'slow' is the .net code in our framework which is a lot more than just some ado.net statements: additionally it has to run on the mobile CLR, which isn't as performing as one might hope for. Our framework does: the entities have to be placed in a queue, all entities are examined one at a time and for each entity a new query is generated. This query is then executed. After that the transaction is committed. Although this code is heavily optimized, it could still be slower compared to a tight loop as more IL code is executed in our case, and on a relatively slow CLR, this isn't going to help.

So the overhead inside our library can be substantially higher than low-level ado.net code, which normally isn't a problem (and also not really comparable) but on a resource constrained platform like a mobile platform, it's different because the CLR isn't as fast as on normal platforms like x86.

That said, the fastest way I think is by simply saving an entity collection, but that too is not going to do miracles. Also, for your ADO.NET test, it's best if you create the query in each iteration as well as use a transaction. I think that will show different results (but still won't be equal to our code, no matter what we do).

If you need a lot of bulk inserts on CE, look into the direct table access provided by SqlCe. it's much faster as it circumvents the whole SQL pipeline.

Frans Bouma | Lead developer LLBLGen Pro