Additional 150ms SQLite query response at random times from .netcore (dapper)-CodePudding

I ran into a problem when the sqlite part of the select queries takes 150ms longer than the other.

I am new to sqlite. Using: .net core 3.1 Dapper

It does not depend on the server (it is reproduced on the local machine). This does not depend on the size of the database, it also works in an empty database. At the time of reading, there is no write, only reading.

> 734. All=8 ms, Query=8 ms, ToList=0ms
> 735. All=2 ms, Query=2 ms, ToList=0ms
> 736. All=2 ms, Query=2 ms, ToList=0ms
> 737. All=4 ms, Query=4 ms, ToList=0ms
> 738. All=3 ms, Query=3 ms, ToList=0ms
> 739. All=2 ms, Query=2 ms, ToList=0ms
> 740. All=158 ms, Query=158 ms, ToList=0ms
> 741. All=2 ms, Query=2 ms, ToList=0ms
> 742. All=1 ms, Query=1 ms, ToList=0ms
> 743. All=1 ms, Query=1 ms, ToList=0ms
> 744. All=1 ms, Query=1 ms, ToList=0ms
> 745. All=158 ms, Query=158 ms, ToList=0ms
> 746. All=1 ms, Query=1 ms, ToList=0ms
> 747. All=158 ms, Query=158 ms, ToList=0ms
> 748. All=2 ms, Query=2 ms, ToList=0ms
> 749. All=157 ms, Query=157 ms, ToList=0ms
> 750. All=1 ms, Query=1 ms, ToList=0ms
> 751. All=3 ms, Query=3 ms, ToList=0ms
> 752. All=1 ms, Query=1 ms, ToList=0ms
> 753. All=1 ms, Query=1 ms, ToList=0ms
> 754. All=3 ms, Query=3 ms, ToList=0ms
> 755. All=1 ms, Query=1 ms, ToList=0ms
> 756. All=1 ms, Query=1 ms, ToList=0ms
> 757. All=1 ms, Query=1 ms, ToList=0ms
> 758. All=1 ms, Query=1 ms, ToList=0ms
> 759. All=1 ms, Query=1 ms, ToList=0ms
> 760. All=1 ms, Query=1 ms, ToList=0ms
> 761. All=5 ms, Query=5 ms, ToList=0ms
> 762. All=1 ms, Query=1 ms, ToList=0ms

Test source code:

public static void Main(string[] args)
        {

            var tasks = new List<Task>();
            var times = new List<TimeInfo>();
            Stopwatch sw = new Stopwatch();

            for (int i = 1; i <= 1000; i  )
            {
                var j = i * 1;
                tasks.Add(Task.Run(() =>
                {
                    Stopwatch sw = new Stopwatch();
                    Stopwatch swAll = new Stopwatch(); swAll.Start();
                    sw.Start();
                    IEnumerable<Cross> result;
                    using (var cnn = new SqliteConnection("Data Source=crosses.db"))
                    {
                        result = cnn.Query<Cross>("SELECT * FROM Crosses WHERE ArticleId=2106053  AND Source=7");
                    }
                    var timeRequest = sw.ElapsedMilliseconds;
                    sw.Restart();
                    var list = result.ToList();
                    sw.Stop();
                    var timeToList = sw.ElapsedMilliseconds;
                    swAll.Stop();
                    times.Add(new TimeInfo()
                    {
                        I = j,
                        TimeAll = swAll.ElapsedMilliseconds,
                        TimeQuery = timeRequest,
                        TimeToList = timeToList,
                    });

                }));


            }

            Task.WaitAll(tasks.ToArray());
            foreach (var item in times.OrderBy(w => w.I))
            {
                Console.WriteLine("{0}. All={1} ms, Query={2} ms, ToList={3}ms", item.I, item.TimeAll, item.TimeQuery, item.TimeToList);
            }
            Console.ReadKey();
        }

Additional info

Schema - one table with index by ArticleId Source

create table Crosses
(
    Id             INTEGER not null
        constraint PK_Crosses
            primary key autoincrement,
    ArticleId      INTEGER not null,
    CrossId        INTEGER not null,
    CrossBrand     TEXT,
    CrossArticle   TEXT,
    Source         INTEGER not null,
    AdditionalInfo TEXT
);

create index IX_Crosses_ArticleId_Source
    on Crosses (ArticleId, Source);

Queries make by EF Core (<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.1.22" />) and over Dapper in sample app (

<PackageReference Include="Dapper" Version="2.0.78" />
<PackageReference Include="Microsoft.Data.Sqlite" Version="3.1.25" />

). Results is same..

CodePudding user response：

The problem was in the library Microsoft.Data.Sqlite.Core --version 3.1.26 Changing the library version to 6.0.6 solved the problem.