Sqlite storage makes asp.net core application very slow

[ricsmania]

I'm trying to use Hangfire with Sqlite, and when I do that my application gets very slow.

Using Visual Studio 2017, I created a Asp.Net Core Web API project.

I added the configuration to Startup.cs:

        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();
            //services.AddHangfire(x => x.UseRedisStorage("10.0.3.109"));
            //services.AddHangfire(x => x.UseSqlServerStorage("Data Source=(localdb)\\mssqllocaldb;Database=aspnet-TesteUsuario-b590c41d-8723-4abc-abe0-1c93ac2af944"));

            services.AddHangfire(x => x.UseSQLiteStorage("Data Source=c:/temp/hangfire.db;Version=3;"));
            services.AddSingleton<IBackgroundService, BackgroundService>();
            services.AddSingleton<BackgroundService, BackgroundService>();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            app.UseMvc();
            var option = new BackgroundJobServerOptions { WorkerCount = 1 };
            app.UseHangfireServer(option);
            app.UseHangfireDashboard();             
        }

I then added a Hangfire call to my Get methd on the values controller

        // GET api/values
        [HttpGet]
        public IEnumerable<string> Get()
        {
            BackgroundJob.Enqueue(() => _service.Process(1));
            return new string[] { "value1", "value2" };
        }

When I use Redis the controller responds in around 7-15 ms. Executed action TesteHangfire.Controllers.ValuesController.Get (TesteHangfire) in 7.6838ms When I use SqlServer the controller responds in around 70-150 ms. Executed action TesteHangfire.Controllers.ValuesController.Get (TesteHangfire) in 78.5856ms But when I use Sqlite the controller takes more than 28000 ms Executed action TesteHangfire.Controllers.ValuesController.Get (TesteHangfire) in 28842.5443ms

At first I was getting all kind of erros when writing to DB, but then I added this config and the errors stopped, but the application is still slow:

            var option = new BackgroundJobServerOptions { WorkerCount = 1 };
            app.UseHangfireServer(option);

[zhakupov]

It looks like we are waiting for QueuePollInterval time span twice: SQLiteStorageConnection.CreateExpiredJob and SQLiteWriteOnlyTransaction.Commit wait until db write lock is released (see SQLiteJobQueue.Dequeue on the Worker thread). The interval is 15 seconds by default, this is why we might get something like 28000 ms.

I think I've fixed the issue in the #10 pull request, waiting for feedback. In the meantime you could fiddle with QueuePollInterval.