Performance

Jun 14, 2012 at 10:27 PM

Any tricks on setting up a store for maximum performance?  I've set:

_store.ConnectionBehavior = ConnectionBehavior.Persistent;
_store.UseCommandCache = true;

When comparing the time required to load 150K records using TableDirect using a SqlCeResultSet and ORM I get:

800ms for TableDirect, 5076ms for ORM.

I must have missed something as I thought ORM was using TableDirect also.

Any thoughts?

Coordinator
Jun 14, 2012 at 11:48 PM

Yes, ORM uses TableDirect whenever possible.  Fundamentally you should be seeing the same behavior as with yoru direct SqlCeResultSet.  Can you post a little bt of the code you're using for both the entity definition and the insert?

Jun 18, 2012 at 2:09 PM
Edited Jun 18, 2012 at 2:10 PM

Actually I'm seeing this behavior when doing a SELECT.  I've yet to look at the INSERT in details.  Here's some of the code - I've changed some names, but it should still be readable.

 I do expect a bit of a hit, but not as large as what I'm seeing.

Thanks!

 The entity definition:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:2.0.50727.5456
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

{
    using System;
    using OpenNETCF.ORM;


    [Entity(KeyScheme.Identity)]
    public class UsbEvent
    {

        private int _eventid;

        private int _detid;

        private int _usbfileid;

        private System.DateTime _timestamp;

        private int _type;

        private string _infostr10;

        private string _infostr25;

        private string _infostr50;

        private System.Nullable<int> _infoint1;

        private System.Nullable<int> _infoint2;

        private System.Nullable<int> _infoint3;

        [Field(IsPrimaryKey = true, SearchOrder = FieldSearchOrder.Ascending)]
        public int eventId
        {
            get
            {
                return this._eventid;
            }
            set
            {
                this._eventid = value;
            }
        }

        [Field()]
        public int detId
        {
            get
            {
                return this._detid;
            }
            set
            {
                this._detectorid = value;
            }
        }

        [Field()]
        public int usbFileId
        {
            get
            {
                return this._usbfileid;
            }
            set
            {
                this._usbfileid = value;
            }
        }

        [Field()]
        public System.DateTime timestamp
        {
            get
            {
                return this._timestamp;
            }
            set
            {
                this._timestamp = value;
            }
        }

        [Field()]
        public int type
        {
            get
            {
                return this._type;
            }
            set
            {
                this._type = value;
            }
        }

        [Field()]
        public string infoStr10
        {
            get
            {
                return this._infostr10;
            }
            set
            {
                this._infostr10 = value;
            }
        }

        [Field()]
        public string infoStr25
        {
            get
            {
                return this._infostr25;
            }
            set
            {
                this._infostr25 = value;
            }
        }

        [Field()]
        public string infoStr50
        {
            get
            {
                return this._infostr50;
            }
            set
            {
                this._infostr50 = value;
            }
        }

        [Field()]
        public System.Nullable<int> infoInt1
        {
            get
            {
                return this._infoint1;
            }
            set
            {
                this._infoint1 = value;
            }
        }

        [Field()]
        public System.Nullable<int> infoInt2
        {
            get
            {
                return this._infoint2;
            }
            set
            {
                this._infoint2 = value;
            }
        }

        [Field()]
        public System.Nullable<int> infoInt3
        {
            get
            {
                return this._infoint3;
            }
            set
            {
                this._infoint3 = value;
            }
        }
    }
}

SELECT, TableDirect and ORM
 
 
var sw = new Stopwatch();

var sEvents = new List<UsbEvent>();

sw.Start();

using (var conn = new SqlCeConnection(GetLocalDBConnString2()))
{
    using (var cmd = new SqlCeCommand("UsbEvent", conn))
    {
        conn.Open();

        cmd.CommandType = CommandType.TableDirect;
        var r = cmd.ExecuteResultSet(ResultSetOptions.None);

        // Get the ordinals
        var oEventId = r.GetOrdinal("eventId");
        var oDetId = r.GetOrdinal("detId");
        var oUsbFileId = r.GetOrdinal("usbFileId");
        var oTimeStamp = r.GetOrdinal("timeStamp");
        var oType = r.GetOrdinal("type");
        var oInfoStr10 = r.GetOrdinal("infoStr10");
        var oInfoStr25 = r.GetOrdinal("infoStr25");
        var oInfoStr50 = r.GetOrdinal("infoStr50");
        var oInfoInt1 = r.GetOrdinal("infoInt1");
        var oInfoInt2 = r.GetOrdinal("infoInt2");
        var oInfoInt3 = r.GetOrdinal("infoInt3");

        while (r.Read())
        {

            sEvents.Add(new UsbEvent
                            {
                                eventId = r.GetInt32(oEventId),
                                detId = r.GetInt32(oDetId),
                                usbFileId = r.GetInt32(oUsbFileId),
                                timestamp = r.GetDateTime(oTimeStamp),
                                type = r.GetInt32(oType),
                                infoStr10 = r.IsDBNull(oInfoStr10) ? null : r.GetString(oInfoStr10),
                                infoStr25 = r.IsDBNull(oInfoStr25) ? null : r.GetString(oInfoStr25),
                                infoStr50 = r.IsDBNull(oInfoStr50) ? null : r.GetString(oInfoStr50),
                                infoInt1 = r.IsDBNull(oInfoInt1) ? (int?)null : r.GetInt32(oInfoInt1),
                                infoInt2 = r.IsDBNull(oInfoInt2) ? (int?)null : r.GetInt32(oInfoInt2),
                                infoInt3 = r.IsDBNull(oInfoInt3) ? (int?)null : r.GetInt32(oInfoInt3),

                            });

        }
    }
}

// Conver to list of different type...
var events = sEvents.Where(e => e.detId == d.detId).Select(e => new NetPollEventEx(e)).ToList();

sw.Stop();

Debug.WriteLine(string.Format(" TableDirect = {0}", sw.ElapsedMilliseconds));

// - ORM -

sw.Restart();

var dbEvents = Db.GetEvents();
events.AddRange(dbEvents.Select(e => new NetPollEventEx(e)));

sw.Stop();

Debug.WriteLine(string.Format(" ORM = {0}", sw.ElapsedMilliseconds));
GetEvents method:
   public IEnumerable<UsbEvent> GetEvents()
        {
            return SqlCeStore.Select<UsbEvent>(false);
        }
Coordinator
Jun 18, 2012 at 4:01 PM

Can you attach (or send to me directly) a populated database so I don't have to generate enough data to test this?

Coordinator
Jun 19, 2012 at 4:27 PM

This is challenging, to say the least.  The “slowness” appears to be in the fact that I have to iterate the fields of the target object instance and fill them.  Just the iteration itself (as opposed to the call to set the property) appears to be taking the largest percentage of the time.  I did find some other places to do some caching and optimizations, so I’ve improved things by roughly 20% in the ORM case, but it’s still nowhere near the statically-bound speed.  I’ll keep digging, but I’m not certain we’ll be able to squeeze much more out of it simply because the code has to handle object types that are unknown at compile time.

So using your DB, with 100k records the non-ORM select takes me about 500ms.  With the original ORM code (what you’re using) it took rough 5200ms.  The new code trims that down to around 4000ms. This code is checked in as a change set (though not yet as a release). Let me know if it at least improves things on your end.

Jun 19, 2012 at 7:18 PM
Thanks Chris,
I'll check it out now and let you know.
Dom

On Tue, Jun 19, 2012 at 11:27 AM, ctacke <notifications@codeplex.com> wrote:

From: ctacke

This is challenging, to say the least. The “slowness” appears to be in the fact that I have to iterate the fields of the target object instance and fill them. Just the iteration itself (as opposed to the call to set the property) appears to be taking the largest percentage of the time. I did find some other places to do some caching and optimizations, so I’ve improved things by roughly 20% in the ORM case, but it’s still nowhere near the statically-bound speed. I’ll keep digging, but I’m not certain we’ll be able to squeeze much more out of it simply because the code has to handle object types that are unknown at compile time.

So using your DB, with 100k records the non-ORM select takes me about 500ms. With the original ORM code (what you’re using) it took rough 5200ms. The new code trims that down to around 4000ms. This code is checked in as a change set (though not yet as a release). Let me know if it at least improves things on your end.

Read the full discussion online.

To add a post to this discussion, reply to this email (orm@discussions.codeplex.com)

To start a new discussion for this project, email orm@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Jun 20, 2012 at 1:53 PM

There's an issue when passing "true" for fillReferences parameter.  These lines went missing in SqlCeDataStore_Select.cs:

 

// autofill references if desired
if (referenceFields == null)
{
     referenceFields = Entities[entityName].References.ToArray();
}

 

---

I get about 8-10% improvement with the new version.  (fillReference set to False).  Doing a test of about 2M rows 

Results:

TableDirect = 6786ms

ORM = 58,451ms of which  50,057ms are spent in PopulateFields and 34,872ms of the time in PopulateFields in spent calling field.PropertyInfo.SetValue.

Unless there's a faster way of setting the property value.  Seems like the limit.  

 

Jun 20, 2012 at 3:20 PM

One possibility to speed it up is to move some of the work into a constructor for the entity definition.  Assuming one uses the EntityGenerator, that no extra work on the part of the user.

I just tested it quickly and the same 2M rows now loads in 17,500ms!  There's probably a bit more speed to had from my little test.  I've set up so the constructor takes the field collection and the result.  It casts and sets the property value based on the field name.  

The big change would be modifying the EntityGenerator to generate the constructor.  (I just wrote the constructor by hand for my test.)

 

Coordinator
Jun 20, 2012 at 3:50 PM

You've definitely got my interest!  Shoot me the code example and I'll make some updates.

Coordinator
Jun 20, 2012 at 5:38 PM

So I took your idea and ran with it.  I've got a preliminary (meaning it's not been tested against a lot of types or null fields, etc) but it trims the time for my earlier test to about 1400ms, which is something like a 75% improvement.

Once I've tested it further, I'll update the EntityGenerator but for now you have to manually add a method into your entity class.  Here's an example (it's the one for your test class):

        private static UsbStealthEvent ORM_CreateProxy(FieldAttributeCollection fields, SqlCeResultSet results)
        {
            UsbStealthEvent item = new UsbStealthEvent();

            foreach (var field in fields)
            {
                var value = results[field.Ordinal];

                switch (field.FieldName)
                {
                    case "eventId":
                        item.eventId = (int)value;
                        break;
                    case "detectorId":
                        item.detectorId = (int)value;
                        break;
                    case "usbStealthFileId":
                        item.usbStealthFileId = (int)value;
                        break;
                    case "timestamp":
                        item.timestamp = (DateTime)value;
                        break;
                    case "type":
                        item.type = (int)value;
                        break;
                    case "infoStr10":
                        item.infoStr10 = (string)value;
                        break;
                    case "infoStr25":
                        item.infoStr25 = (string)value;
                        break;
                    case "infoStr50":
                        item.infoStr50 = (string)value;
                        break;
                    case "infoInt1":
                        item.infoInt1 = (int?)value;
                        break;
                    case "infoInt2":
                        item.infoInt2 = (int?)value;
                        break;
                    case "infoInt3":
                        item.infoInt3 = (int?)value;
                        break;
                }
            }

            return item;
        }

Jun 20, 2012 at 6:19 PM

Sorry for the delay, I'll zip and send.  But looking at the code, that's the idea. 

I started to set it up such that it would fall back to the "normal" way (PropertyInfo.SetValue) method, if a constructor with parameters for the entity wasn't found.  But the caching was getting me a little mess up and I had to move on.  That would allow backwards compatibility with previously generated entity definitions.

Here's a part of the code I had...  But the caching of the constructor messes it up: if the entity doesn't have a parameterized constructor the GetConstructorForType return the non-parameterized one - which blows up on the invoke.  Either we need 2 caches...  try the param cache first, then the non-param one.  Or we need to be able to identify if the constructorInfo object is parameterized or not and invoke it properly.

                            // Try to get a parameterized constructor
                            var paramCtor = GetConstructorForType(objectType, new[] { typeof(FieldAttributeCollection), typeof(SqlCeResultSet) });

                            if (paramCtor != null)
                            {
                                item = paramCtor.Invoke(new object[] { m_fields, results });
                            }
                            else
                            {
                                var ctor = GetConstructorForType(objectType);
                                item = ctor.Invoke(null);

                                object rowPK = null;


                                swPopField.Start();

                                // fill in the entity field values
                                PopulateFields(entityName, m_fields, results, item, fillReferences);

                                swPopField.Stop();

                                // autofill references if desired
                                if (referenceFields == null)
                                {
                                    referenceFields = Entities[entityName].References.ToArray();
                                }

                                if ((fillReferences) && (referenceFields.Length > 0))
                                {
                                    //FillReferences(item, rowPK, referenceFields, true);
                                    FillReferences(item, rowPK, referenceFields, false);
                                }
                            }
protected internal ConstructorInfo GetConstructorForType(Type objectType, Type[] paramTypes)
        {
            if (m_ctorCache.ContainsKey(objectType))
            {
                return m_ctorCache[objectType];
            }

            var ctor = objectType.GetConstructor(paramTypes);
            if (ctor != null)
            {
                m_ctorCache.Add(objectType, ctor);
            }
            return ctor;
        }
Coordinator
Jun 20, 2012 at 6:57 PM

Mine was similar, though I moved stuff out to another method.  I also didn't use a constructor, but opted for a private static method so as to not pollute either the constructor or the exposed object model (helping prevent developers from doing things they shouldn't).  Take a look at the changes I checked in and see if it does what you're after.

Jun 22, 2012 at 10:00 PM

18,000ms from 50-60,000ms.  Nice work! 

I did have to change the private static method to deal with NULL values...

case "eventId": item.eventId = (int)value; break;
case "detectorId": item.detectorId = (int)value; break;
case "usbStealthFileId": item.usbStealthFileId = (int)value; break;
case "timestamp": item.timestamp = (DateTime)value; break;
case "type": item.type = (int)value; break;
case "infoStr10": item.infoStr10 = DBNull.Value == value ? null : (string)value; break;
case "infoStr25": item.infoStr25 = DBNull.Value == value? null : (string)value; break;
case "infoStr50": item.infoStr50 = DBNull.Value == value ? null : (string)value; break;
case "infoInt1": item.infoInt1 = DBNull.Value == value ? null : (int?)value; break;
case "infoInt2": item.infoInt2 = DBNull.Value == value ? null : (int?)value; break;
case "infoInt3": item.infoInt3 = DBNull.Value == value ? null : (int?)value; break; 
Any thoughts on whether something could be gained by using PLINQ? For example when doing a select with a filtercondition.  (Would need a .Net 4 assembly, but could be interesting)  
Coordinator
Jun 22, 2012 at 10:31 PM
Edited Sep 28, 2012 at 2:45 PM

The desktop could probably benefit even further by emitting typesafe property setters, but the issue here is that I'm trying to keep the code base CF-compatible.  I could conditionally deal with Emit as well as PLINQ, but it adds complexity which I don't have a lot of time for testing.  If you want to try it and submit a patch, I'm all for seeing what it gives us.