search

ericsink / SQLitePCL.raw

A Portable Class Library (PCL) for low-level (raw) access to SQLite
Apache License 2.0
512 stars 106 forks source link

Performance with SQLite and Microsoft.EntityFrameworkCore.Sqlite.Core and SQLitePCLRaw.bundle_e_sqlcipher #504

Closed iplusMario closed 1 year ago

iplusMario commented 1 year ago

We are currently testing encrypted SQLite and we are experiencing slow performance (500 ms for commiting transaction) when inserting into database. Any idea what could be causing this slow performance on encrypted SQLite database?

dbug: 8/18/2022 08:20:39.382 CoreEventId.ValueGenerated[10808] (Microsoft.EntityFrameworkCore.ChangeTracking)
      'TestDBContext' generated temporary value '-9223372036854774805' for the property 'DrawCardId.DrawCard'.
dbug: 8/18/2022 08:20:39.382 CoreEventId.StartedTracking[10806] (Microsoft.EntityFrameworkCore.ChangeTracking)
      Context 'TestDBContext' started tracking 'DrawCard' entity with key '{DrawCardId: -9223372036854774805}'.
dbug: 8/18/2022 08:20:39.383 CoreEventId.SaveChangesStarting[10004] (Microsoft.EntityFrameworkCore.Update)
      SaveChanges starting for 'TestDBContext'.
dbug: 8/18/2022 08:20:39.383 CoreEventId.DetectChangesStarting[10800] (Microsoft.EntityFrameworkCore.ChangeTracking)
      DetectChanges starting for 'TestDBContext'.
dbug: 8/18/2022 08:20:39.383 CoreEventId.DetectChangesCompleted[10801] (Microsoft.EntityFrameworkCore.ChangeTracking)
      DetectChanges completed for 'TestDBContext'.
dbug: 8/18/2022 08:20:39.384 RelationalEventId.ConnectionOpening[20000] (Microsoft.EntityFrameworkCore.Database.Connection)
      Opening connection to database 'main' on server 'TestDatabase.db'.
dbug: 8/18/2022 08:20:39.384 RelationalEventId.ConnectionOpened[20001] (Microsoft.EntityFrameworkCore.Database.Connection)
      Opened connection to database 'main' on server 'TestDatabase.db'.
dbug: 8/18/2022 08:20:39.384 RelationalEventId.TransactionStarting[20209] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Beginning transaction with isolation level 'Unspecified'.
dbug: 8/18/2022 08:20:39.385 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Began transaction with isolation level 'Serializable'.
dbug: 8/18/2022 08:20:39.385 RelationalEventId.CommandCreating[20103] (Microsoft.EntityFrameworkCore.Database.Command)
      Creating DbCommand for 'ExecuteReader'.
dbug: 8/18/2022 08:20:39.385 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 8/18/2022 08:20:39.386 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0='1', @p1='52', @p2=NULL, @p3='2022-08-18T06:20:39.3822396Z' (DbType = DateTime), @p4='1', @p5='3', @p6='1'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "DrawCard" ("DrawId", "Card", "ChangeUser", "DrawDate", "Hand", "Index", "Status")
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
      SELECT "DrawCardId", "ChangeDate"
      FROM "DrawCard"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();
info: 8/18/2022 08:20:39.386 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p0='1', @p1='52', @p2=NULL, @p3='2022-08-18T06:20:39.3822396Z' (DbType = DateTime), @p4='1', @p5='3', @p6='1'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "DrawCard" ("DrawId", "Card", "ChangeUser", "DrawDate", "Hand", "Index", "Status")
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
      SELECT "DrawCardId", "ChangeDate"
      FROM "DrawCard"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();
dbug: 8/18/2022 08:20:39.387 CoreEventId.ForeignKeyChangeDetected[10803] (Microsoft.EntityFrameworkCore.ChangeTracking)
      The foreign key property 'DrawCard.DrawCardId' was detected as changed from '-9223372036854774805' to '3' for entity with key '{DrawCardId: 3}'.
dbug: 8/18/2022 08:20:39.388 RelationalEventId.DataReaderDisposing[20300] (Microsoft.EntityFrameworkCore.Database.Command)
      A data reader was disposed.
- dbug: 8/18/2022 08:20:39.388 RelationalEventId.TransactionCommitting[20210] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Committing transaction.
- dbug: 8/18/2022 08:20:39.856 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Committed transaction.
dbug: 8/18/2022 08:20:39.856 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection)
      Closing connection to database 'main' on server 'TestDatabase.db'.
dbug: 8/18/2022 08:20:39.857 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection)
      Closed connection to database 'main' on server 'TestDatabase.db'.
dbug: 8/18/2022 08:20:39.858 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Disposing transaction.
dbug: 8/18/2022 08:20:39.858 CoreEventId.StateChanged[10807] (Microsoft.EntityFrameworkCore.ChangeTracking)
      The 'DrawCard' entity with key '{DrawCardId: 3}' tracked by 'TestDBContext' changed state from 'Added' to 'Unchanged'.
dbug: 8/18/2022 08:20:39.858 CoreEventId.SaveChangesCompleted[10005] (Microsoft.EntityFrameworkCore.Update)
      SaveChanges completed for 'TestDBContext' with 1 entities written to the database.

Include provider and version information EF Core version: 6.0 and SQLitePCLRaw.bundle_e_sqlcipher 2.1.0 Database provider: Microsoft.EntityFrameworkCore.Sqlite.Core 6.0.8 Target framework: (e.g. .NET Core 6.0) Operating system: Windows 10 IDE: (e.g. Visual Studio 2022)

bricelam commented 1 year ago

Duplicate of https://github.com/dotnet/efcore/issues/28774

ericsink commented 1 year ago

Closing as external