SQL Lite on Azure App Service - Inserts Slow and Timeout

Question

We have a process that needs to create a sql lite database with a couple tables with about 750k records/100mb. It gets uploaded somewhere else (Azure Storage Blob). I know Azure App's have very slow disk I/O and when running this locally it takes a few seconds, it always times out on Azure. I've tried setting the WEBSITE_LOCAL_CACHE_OPTION to always and using the temp folder but that didn't help.

I looked into using a sql lite in memory database but there seems to be no way to avoid the file system if I want to convert that into a byte array (or stream) which is slow in an azure app. Ideally getting access to the in memory database to stream to a blog would be best case scenario.

Are there any tweaks in sql lite or in the azure app service that would allow this to finish in a reasonable amount of time?

Using service stack's ormlite. Here is an example:

 using (var trans = dba.OpenTransaction( System.Data.IsolationLevel.ReadUncommitted))
                {
                    dbLite.InsertAll(locs);
                    foreach (var s in sales)
                    {
                        dbLite.Insert<Sales>(s);
                    }

                    trans.Commit();
                }

Interesting enough I got the time down from never working (10 minutes it has written 5mb so I know it will never finish) to 4-5 minutes with

dbLite.ExecuteSql("pragma page_size = 8192");
dbLite.ExecuteSql("pragma synchronous = OFF");
dbLite.ExecuteSql("PRAGMA journal_mode = OFF");

This is compared to 1 second locally. The synchronous mode set to off seems to help the most in my scenario.

What is the App Service plan / pricing tier for your azure website? Where did you execute the processing about creating a sqlite database and insert about 750k records/100mb, a request under web application or webjobs ? — Bruce Chen, Oct 12 '17 at 08:19
How much time it costs on azure and what is the detailed error trace? For azure app service, there is a 230 seconds timeout for request, you could refer to this [issue](https://stackoverflow.com/questions/38673318/azure-asp-net-webapp-the-request-timed-out). Moreover, you could use `dbLite.InsertAll(sales)` instead of iterating over `sales` and insert sale data for better performance. — Bruce Chen, Oct 12 '17 at 08:27
I've tried insert all /etc. The slowness is due to the slow disk io as I can open a console and see the files slowly increase. I ended up using hang fire and a background thread. It takes 4-6 minutes with synchronous and journal set to off and is 75mb non zipped. — lucuma, Oct 12 '17 at 12:57

lucuma · Accepted Answer · 2017-12-19T16:50:36.903

It seems that setting dbLite.ExecuteSql("pragma synchronous = OFF"); had the biggest benefit of reducing the query from an estimated 140 minutes down to 4-5 minutes. There are drawbacks to turning it off but for my case we are just archiving some data so they are minimal. If anyone has other ways to increase the insert time further I will accept them if they materially impact the time.

I ultimately got my time down to 1 minute and 5 seconds for a 70mb database with 4 tables and roughly 600k rows.

            dbLite.ExecuteSql("pragma page_size = 8192");
            dbLite.ExecuteSql("pragma synchronous = OFF");
            dbLite.ExecuteSql("PRAGMA journal_mode = OFF");
            dbLite.ExecuteSql("PRAGMA LOCKING_MODE = OFF");
            context.WriteLine("Creating Tables");

and

 using (var trans = dba.OpenTransaction(System.Data.IsolationLevel.ReadUncommitted))
 {}

SQL Lite on Azure App Service - Inserts Slow and Timeout

1 Answers1