Recarding the speed of the orm

Aug 1, 2013 at 12:51 PM
HI, I am finding the orm a bit slow when inserting or selecting from larger amounts of data. So here I go with an example. I use 3 enitities

Client entity:

[Entity(KeyScheme.Identity , NameInStore = "Clienti")]
public class Client
{

    [Field(IsPrimaryKey = true, FieldName = "idClient", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 idClient { get; set; }

    [Field(FieldName = "idFirma", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 idFirma { get; set; }

    [Field(FieldName = "idPartener", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 idPartener { get; set; }

    [Field(FieldName = "numePartener", SearchOrder = FieldSearchOrder.Ascending)]
    public string numePartener { get; set; }

    [Field(FieldName = "numePctLucru", SearchOrder = FieldSearchOrder.Ascending)]
    public string numePctLucru { get; set; }

    [Field(FieldName = "oras", SearchOrder = FieldSearchOrder.Ascending)]
    public string oras { get; set; }

    [Field(FieldName = "adresa", SearchOrder = FieldSearchOrder.Ascending)]
    public string adresa { get; set; }

    [Field(FieldName = "telefon", SearchOrder = FieldSearchOrder.Ascending)]
    public string telefon { get; set; }

    [Field(FieldName = "fax", SearchOrder = FieldSearchOrder.Ascending)]
    public string fax { get; set; }

    [Field(FieldName = "email", SearchOrder = FieldSearchOrder.Ascending)]
    public string email { get; set; }

    [Field(FieldName = "agentID", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 agentID { get; set; }

    [Field(FieldName = "idPctLucru", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 idPctLucru { get; set; }

    [Field(FieldName = "codFiscal", SearchOrder = FieldSearchOrder.Ascending)]
    public string codFiscal { get; set; }

    [Field(FieldName = "nrComert", SearchOrder = FieldSearchOrder.Ascending)]
    public string nrComert { get; set; }

    [Field(FieldName = "inventarDeschis", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 inventarDeschis { get; set; }

    [Field(FieldName = "tipClient", SearchOrder = FieldSearchOrder.Ascending)]
    public string tipClient { get; set; }

    [Reference(typeof(PretClient), "idClient", ReferenceType = ReferenceType.OneToMany)]
    public PretClient[] listaPretClient { get; set; }

    private static Client ORM_CreateProxy(FieldAttributeCollection fields, IDataReader results)
    {
        var item = new Client();
        foreach (var field in fields)
        {
            var value = results[field.Ordinal];

            switch (field.FieldName)
            {
                case "idClient": item.idClient = (long)value; break;
                case "idFirma": item.idFirma = (long)value; break;
                case "agentID": item.agentID = (long)value; break;
                case "idPartener": item.idPartener = (long)value; break;
                case "idPctLucru": item.idPctLucru = (long)value; break;
                case "numePartener": item.numePartener = DBNull.Value == value ? null : (string)value; break;
                case "numePctLucru": item.numePctLucru = DBNull.Value == value ? null : (string)value; break;
                case "oras": item.oras = DBNull.Value == value ? null : (string)value; break;
                case "adresa": item.adresa = DBNull.Value == value ? null : (string)value; break;
                case "telefon": item.telefon = DBNull.Value == value ? null : (string)value; break;
                case "fax": item.fax = DBNull.Value == value ? null : (string)value; break;
                case "email": item.email = DBNull.Value == value ? null : (string)value; break;
                case "codFiscal": item.codFiscal = DBNull.Value == value ? null : (string)value; break;
                case "nrComert": item.nrComert = DBNull.Value == value ? null : (string)value; break;
                case "inventarDeschis": item.inventarDeschis = (long)value; break;
                case "tipClient": item.tipClient = DBNull.Value == value ? null : (string)value; break;
               //case "listaPretClient": item.listaPretClient = (PretClient[])value; break;
            }
        }

        return item;
    }
}
Produs entity

public class Produs
{
    [Field(IsPrimaryKey = true, FieldName = "idProdus", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 idProdus { get; set; }

    [Field(FieldName = "idFirma", SearchOrder = FieldSearchOrder.Ascending)]
    public long? idFirma { get; set; }

    [Field(FieldName = "groupID", SearchOrder = FieldSearchOrder.Ascending)]
    public string groupID { get; set; }

    [Field(FieldName = "agentID", SearchOrder = FieldSearchOrder.Ascending)]
    public long? agentID { get; set; }

    [Field(FieldName = "codArticol", SearchOrder = FieldSearchOrder.Ascending)]
    public string codArticol { get; set; }

    [Field(FieldName = "numeArticol", SearchOrder = FieldSearchOrder.Ascending)]
    public String numeArticol { get; set; }

    [Field(FieldName = "unitateMasura", SearchOrder = FieldSearchOrder.Ascending)]
    public String unitateMasura { get; set; }

    [Field(FieldName = "codEAN", SearchOrder = FieldSearchOrder.Ascending)]
    public String codEAN { get; set; }

    [Field(FieldName = "inactiv", SearchOrder = FieldSearchOrder.Ascending)]
    public Byte? inactiv { get; set; }

    [Field(FieldName = "stoc", SearchOrder = FieldSearchOrder.Ascending)]
    public double? stoc { get; set; }

    [Field(FieldName = "pretVanzare", SearchOrder = FieldSearchOrder.Ascending)]
    public double? pretVanzare { get; set; }

    [Field(FieldName = "pretTVA", SearchOrder = FieldSearchOrder.Ascending)]
    public double? pretTVA { get; set; }

    [Field(FieldName = "faraDiscount", SearchOrder = FieldSearchOrder.Ascending)]
    public Byte? faraDiscount { get; set; }

    [Field(FieldName = "cantitate", SearchOrder = FieldSearchOrder.Ascending)]
    public String cantitate { get; set; }

    [Field(FieldName = "target", SearchOrder = FieldSearchOrder.Ascending)]
    public Boolean? target { get; set; }

    private static Produs ORM_CreateProxy(FieldAttributeCollection fields, IDataReader results)
    {
        var item = new Produs();

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

            switch (field.FieldName)
            {
                case "idProdus": item.idProdus = (long)value; break;
                case "idFirma": item.idFirma = DBNull.Value == value ? null : (long?)value; break;
                case "groupID": item.groupID = DBNull.Value == value ? null : (string)value; break;
                case "agentID": item.agentID = DBNull.Value == value ? null : (long?)value; break;
                case "codArticol": item.codArticol = DBNull.Value == value ? null : (string)value; break;
                case "numeArticol": item.numeArticol = DBNull.Value == value ? null : (string)value; break;
                case "unitateMasura": item.unitateMasura = DBNull.Value == value ? null : (string)value; break;
                case "codEAN": item.codEAN = DBNull.Value == value ? null : (string)value; break;
                case "inactiv": item.inactiv = DBNull.Value == value ? null : (byte?)value; break;
                case "stoc": item.stoc = DBNull.Value == value ? null : (double?)value; break;
                case "pretVanzare": item.pretVanzare = DBNull.Value == value ? null : (double?)value; break;
                case "pretTVA": item.pretTVA = DBNull.Value == value ? null : (double?)value; break;
                case "faraDiscount": item.faraDiscount = DBNull.Value == value ? null : (byte?)value; break;
                case "cantitate": item.cantitate = DBNull.Value == value ? null : (string)value; break;
                case "target": item.target = DBNull.Value == value ? null : (bool?)value; break;
            }
        }

        return item;
    }
}
Aug 1, 2013 at 1:12 PM
PretClient entity

[Entity(KeyScheme.Identity, NameInStore = "PretClient")]
public class PretClient
{
    [Field(IsPrimaryKey = true, FieldName = "idPretClient", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 idPretClient { get; set; }

    [Field(FieldName = "codArticol", SearchOrder = FieldSearchOrder.Ascending)]
    public string codArticol { get; set; }

    [Field(FieldName = "idPctLucru", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64? idPctLucru { get; set; }

    [Field(FieldName = "idFirma", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64? idFirma { get; set; }

    [Field(FieldName = "pret", SearchOrder = FieldSearchOrder.Ascending)]
    public double? pret { get; set; }

    [Field(FieldName = "idAgent", SearchOrder = FieldSearchOrder.Ascending)]
    public long? idAgent { get; set; }

    [Field(FieldName = "idClient", SearchOrder = FieldSearchOrder.Ascending)]
    public Int64 idClient { get; set; }

    public PretClient()
    {
        idPretClient = -1;
    }

    private static PretClient ORM_CreateProxy(FieldAttributeCollection fields, IDataReader results)
    {
        var item = new PretClient();
        foreach (var field in fields)
        {
            var value = results[field.Ordinal];

            switch (field.FieldName)
            {
                case "idPretClient": item.idPretClient = (long)value; break;
                case "codArticol": item.codArticol = DBNull.Value == value ? null : (string)value; break;
                case "idPctLucru": item.idPctLucru = DBNull.Value == value ? null : (long?)value; break;
                case "idClient": item.idClient = (long)value; break;
                case "idFirma": item.idFirma = DBNull.Value == value ? null : (long?)value; break;
                case "pret": item.pret = DBNull.Value == value ? null : (double?)value; break;
                case "idAgent": item.idAgent = DBNull.Value == value ? null : (long?)value; break;
            }
        }

        return item;
    }
}
I need to get the products(from Produs) that apear as having a specific price(PretClient) for a client and for that I use this function:


var sw = new Stopwatch();
        sw.Start();
        List<Produs> listaProduse = new List<Produs>();
        List<Produs> listaTotala = m_store.Select<Produs>(false);
        Debug.WriteLine("listaProduse  " + sw.ElapsedMilliseconds);
        Client clientGasit = m_store.Select<Client>(true).FirstOrDefault<Client>(c => c.idClient == client.idClient);
        List<PretClient> listaPret = clientGasit.listaPretClient.ToList<PretClient>();// m_store.Select<PretClient>().Where(pr => pr.idPctLucru == clientGasit.idPctLucru && pr.idFirma == agent.idFirma).ToList<PretClient>();
        Debug.WriteLine("listaPret  " + sw.ElapsedMilliseconds);
        //List<PretClient> listaPret = m_store.Select<PretClient>().Where(pr => pr.idPctLucru == client.idPctLucru && pr.idFirma == agent.idFirma).ToList<PretClient>();  
        List<Produs> listaProduseIstoric = (from produs in listaTotala join pret in listaPret on produs.codArticol equals pret.codArticol select produs).ToList<Produs>();
        listaProduse = listaProduseIstoric;
        Debug.WriteLine("join produse  " + sw.ElapsedMilliseconds);
        sw.Stop();
First debug:
Produs has about 3k entries and it take 2307 ms to return
Second debug:
PretClient has about 17k entries and it takes about 7400 ms to return
3rd debug:
the join is ok 80 ms;

also when inserting all the data in the DB about 25k entries through a wcf server it takes around 6mins to complete from wich around 5 mins are the inserts
I have this datastore setings:
m_store.ConnectionBehavior = ConnectionBehavior.Persistent;
m_store.UseCommandCache = true;

My question is if there is a way to improve performence because i need it to be at around 1/5 of the time it takes now. Thank you for your time.
Coordinator
Aug 1, 2013 at 10:52 PM
Can you provide an SDF with some sample data for me to debug against? Why are you setting a SearchOrder on every field (doing so creates an index on every field, which will affect insert performance)?
Aug 2, 2013 at 6:48 AM
From my tests indexing every field doesn't affect performance. Here is a download link for my db(couldn't find your email to send it) http://www.speedyshare.com/a5UZG/mobileDB.sdf
Coordinator
Aug 2, 2013 at 2:37 PM
The database requires a password. feel free to email it to me. Just click my name to the left and there will be a link to Contact me in the lower left of the new page.
Coordinator
Aug 2, 2013 at 4:31 PM
Alright, so one root of your perf problems is the effective table scan for the Client, when you know the PK value. I'm testing on the desktop to make things simpler, but the relative speed between tasks is still valid.

Let's assume I want a specific Client (with references), that I know has an ID of 100. Your code then does this:
        sw.Reset();
        sw.Start();

        var clientGasit = store.Select<Clienti>(true).FirstOrDefault<Clienti>(c => c.idClient == 100);
        Debug.WriteLine("Get client LINQ filter " + sw.ElapsedMilliseconds);
On the desktop that takes ~800ms, which is a pretty long time.

Now if we use the Select overload that takes in the primary key directly, and change the code to this (note I'm using 100L since your PK is actually a long):
        sw.Reset();
        sw.Start();

        clientGasit = store.Select<Clienti>(100L, true);
        Debug.WriteLine("Get client by PK " + sw.ElapsedMilliseconds);
This returns the same Client record in about 13ms, which is about a 60x improvement.

For the first case where it is selecting out all Products, verify that the ORM_CreateProxy is indeed getting called. If not, it will be way slower to generate instances.