locked
[UWP]Why sqlite core dataaccess is slower then sqlite EF in UWP applications ? RRS feed

  • Question

  • I am developing a small application in UWP with VS2015.

    I had created a model and used below code to insert data into SQLite DB.

    using (var db = new TSContext()) {

                        data.ForEach(c => db.Add(c));
                        db.SaveChanges();
    }

    Above piece of code successfully inserts data very quickly. Approx. 13129 records where inserted in 5 seconds.

    I might have to discard EF, as I feel EF support for SQLite on UWP is incomplete.   because simple linq query was not working for me eg.

                 var s = from r in db.TSContext
                            where r.Employee == "gg"
                            select r;

    So If I use SQLite core data access, it takes nearly 6 minutes  to insert same amount of records.  Below is the code

        using (SqliteConnection db = new SqliteConnection("Filename=TestDB.db"))
                {
                    db.Open();
                    string sqlquery = "Insert into DBTSE(EMPLOYEE, CONTRACT, DOWNLOAD_DATETIME ........) Values(@EMPLOYEE, @CONTRACT, @DOWNLOAD_DATETIME, .........)";

                    foreach (var row in pData)
                    {
                        SqliteCommand cmd = new SqliteCommand(sqlquery, db);
                        cmd.Parameters.AddWithValue("@EMPLOYEE", row.EMPLOYEE);
                        cmd.Parameters.AddWithValue("@CONTRACT", row.CONTRACT);

                         ...

                          ...

                        cmd.ExecuteNonQuery();
                    }

                    db.Close();
                }

    So my question is, Why it is slow ?   Is there some setup issue causing the performance problems  ?

    Regards



    Tuesday, June 6, 2017 7:51 AM

All replies

  • Hi Richard Freytag,

    Sorry for my late reply.

    >> Why sqlite core dataaccess is slower then sqlite EF in UWP applications?

    In my experience, I think the different ways to insert the records are the reason why sqlite core dataaccess is slower then sqlite EF. In the sqlite dataaccess way you need to create and execute the SqliteCommand so many times while the sqlite EF way need to insert for the same time but only need to save for once.

    >> simple linq query was not working for me?

    I have test about your code but this but it works well. The linq query returns a collection to you and if you debug at that line you can see it contains the model in the result view.

    Could you please check it out to see if you don’t know it before?

    I also find some example about using query in the SQLite EF in the Entity Framework Querying and Finding Entities and Entity Framework Raw SQL Queries

    Best Regards,

    Xavier Eoro


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 8, 2017 9:08 AM
  • @Richard Freytag

    Any update?

    Have you solved your issue?

    Best Regards,

    Xavier Eoro


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 12, 2017 1:51 AM
  • @Xavier Eoro   :

    I am so sorry, I did not checked forum earlier.

    Well The linq does not work for me .

      var s = from r in db.TSContext
                            where r.Employee == "gg"
                            select r;

    Its giving compile time error. Could not find implementation of the query pattern for the source type 'DbSet<>', where not found.

    Following are the references added into UWP project ( from nuget)

    Microsoft.Data.SQlite

    Microsoft.Entityframeworkcode.relational

    Microsoft.Entityframeworkcode.sqlite

    Microsoft.Entityframeworkcode.sqlite.design

    Microsoft.Entityframeworkcode.tools

    Microsoft.xaml.behaviorus.uwp.managed

    newtonsoft.json

    template10

    Universal Windows.

    Regards

    Monday, June 12, 2017 4:51 PM
  • Hi Richard Freytag,

    Since the linq query works well for me, could you please provide a sample for me? You can delete the sensitive information and just give me a simple project. It will be helpful for me to get a sample which giving compile time error when using the linq.

    Best Regards,

    Roy Li


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com .



    Tuesday, June 13, 2017 1:46 AM