SQLite query performance

Hello!

I'm having some performance issues when loading my sqlite table. I'm sure I'm doing something wrong, but have hit the limit of my google skills to figure out what it is. I'm using the NuGet package "sqlite-net". That's what the ToDo app from the forms samples uses.

Here is the abbreviated class definition of my table record:

    public class Item
    {
        [PrimaryKey, AutoIncrement, Indexed]
        public int Id { get; set; }
        [Indexed]
        public string Source { get; set; }      // Indexes on Source and TrackId made no performance difference
        [Indexed]
        public string TrackId { get; set; }
        public string Product { get; set; } 
        public string ProdDescr { get; set; } 
        public double Qty { get; set; }
    }

... for which a table is created

        // create the tables
        CreateTable<Item> ();

... and I have the following access function defined:

    public int GetItemId (string itemname)  // Trackid
    {
        lock (ItemDatabase.locker) {
            Item oi = Table<Item>().FirstOrDefault(x => x.TrackId == itemname);
            if (oi == null)
                return -1;
            else
                return oi.Id;
        }
    }

Then I have a loop to load the data from stuff I read in from a web query:

    private void parseitems(CP_RESULT result, Group grp)
    {
        Item itm = null;
        _itemDB.BeginTransaction();

        foreach (DataRow row in result.FieldValues.Rows)
        {
            string trackid = (string)row[col_trackid];
            if (trackid.Length > 0)
            {
                int id = _itemDB.GetItemId(trackid);
                if (id < 0)
                {
                    itm = new Item();
                    itm.Source = grp.Sourced;
                }
                else
                {
                    itm = _itemDB.GetItem(id);
                }

                itm.TrackId = trackid;
                itm.Product = (string)row[col_prod];
                itm.ProdDescr = (string)row[col_desc];
                itm.Qty = (double)row[col_qty]; 
            }
            _itemDB.SaveElement<Item>(itm);
        }
        _itemDB.Commit();
    }

So... Adding the transaction made a small improvement. Adding the indexes made no improvement, whatsoever. Surprisingly, the SaveElement call is nearly free. It's the GetItemId(trackid) call that takes a long time. The first few queries are quite quick, but the more we put in, the slower it goes until it takes roughly half a second to do the query.

What blindingly stupid thing am I doing that's causing it to slow down so much?

Thank you so much for your help!
-Karen

Best Answers

Answers

  • KarenCateKarenCate USMember ✭✭

    @StefaanAvonds.3725 @DirkWilhelm Thank you both so much for your help!

    Stefaan - The reason it is architected this way is that I am loading data into the database here, from an external source. I'm checking for duplicates in the input data.

    Dirk - 1. I assumed that would be the case, but I could not find any documentation that said so. Thanks for the validation.
    2. I wondered if this was an issue, but...

    It is slightly unintuitive to me that processing everything into a list, then looping through the list to build a transaction would be faster, but it certainly is. My load time went from over twenty minutes to right around one minute. That is acceptable performance for this function. Most of my job is maintaining the desktop application that this app will augment. In that environment our database calls are fast enough that this method would (probably) be slower.

    Anyway... The core of the function now looks like this:

            List<Item> itmcache = new List<Item>();
            foreach (DataRow row in result.FieldValues.Rows)
            {
                string trackid = (string)row[col_trackid];
                if (trackid.Length > 0)
                {
                    itm = itmcache.FirstOrDefault(y => y.TrackId == trackid);
                    if (itm == null)
                    {
                        itm = new Item();
                        itm.Source = grp.Sourced;
                    }
    
                    itm.TrackId = trackid;
                    itm.Product = (string)row[col_prod];
                    itm.ProdDescr = (string)row[col_desc];
                    itm.Qty = (double)row[col_qty]; 
                }
                itmcache.Add(itm);
            }
    
            _itemDB.BeginTransaction();
            foreach (Item itm2 in itmcache)
            {
                _itemDB.SaveElement<Item>(itm2);
            }
            _itemDB.Commit();
    

    Thanks again!
    -Karen

Sign In or Register to comment.