Optimizing the SQLite database as source data for R scripts

Question

I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to create a database and store the data in the target form there. So when a new file appears, it goes through the ETL process (extract from csv, transformation to the target structure and loading into db). Unfortunately, due to administrative limitations, I only have the SQLite engine at my disposal. At the beginning, the database worked great, but after loading a larger data set into it (the .db file is about 5GB), performance problems began to appear. First things first. I have 3 files, so I created 3 tables (separate for each file type). Each of them has an identical structure, differing only in the source of data. The tables are called:

• maritime_transport
• railway_transport
• air_transport

And they have the following structure:

• Datetime text NOT NULL - exact time stamp of the transaction
• Day text - the day of the transaction determined on the basis of Datetime (to facilitate subsequent aggregation)
• Week text - a week of transactions determined on the basis of Datetime, in the form of the last day of the week (to facilitate subsequent aggregation)
• Month text - the month of the transaction determined on the basis of Datetime, in the form of the last day of the month (to facilitate subsequent aggregation)
• Year text - the year of the transaction determined on the basis of Datetime, in the form of the last day of the year (to facilitate subsequent aggregation)
• From text - exporter
• To text - importer
• Value numeric - transaction value Each table has approximately 20 million rows. I have indexes set up on the Day columns in each of the above tables. And this is my source data.

The first additional layer contains balances for each company: sum(export - import) - separately for each table.

Additionally, it needs daily, weekly, monthly and annual aggregation for each type (i.e. for exporter-importer pairs and for the balance sheets of each company).

What's the best way to solve this problem to maximize application performance?

My first idea was to create views:

• for balance sheets for each contractor
• individual aggregates (day, week, month, year) for the exporter-importer relationship
• individual aggregates (day, week, month, year) per contractor But this solution worked well for small data sets or multi-million tables, there was a problem with the performance of views, let alone the view from views (aggregation by dates for balance sheets).

I am also wondering whether to treat db only as a data storage and not perform any calculations there. For example, if a new data file appears, should it be loaded and then any aggregation performed in the application. Another option considered is to convert the views into physical tables and force updates of these tables each time new data appears (e.g. if a new day appears in a given month, the monthly aggregate currently located in the db is deleted, the R script recalculates the aggregate for a given month and loads it into db).

Or are there any methods to optimize SQLite apart from indexing which didn't help much?

Thank you very much for all your advice and comments!

---

CREATE TABLE IF NOT EXISTS maritime_transport(Datetime text NOT NULL, Day text, Week text, Month text, Year text, Company_From text, Company_To text, Value numeric);
CREATE TABLE IF NOT EXISTS railway_transport(Datetime text NOT NULL, Day text, Week text, Month text, Year text, Company_From text, Company_To text, Value numeric);
CREATE TABLE IF NOT EXISTS air_transport(Datetime text NOT NULL, Day text, Week text, Month text, Year text, From text, Company_To text, Value numeric);

CREATE INDEX maritime_day_idx ON maritime_transport (Day);
CREATE INDEX railway_day_idx  ON railway_transport (Day);
CREATE INDEX air_day_idx      ON air_transport (Day);

CREATE VIEW IF NOT EXISTS v_balance_maritime as
with source as (
    SELECT Datetime, Day, Week, Month, Year, Company_From Company,  Value FROM maritime_transport UNION ALL
    SELECT Datetime, Day, Week, Month, Year, Company_To   Company, -Value FROM maritime_transport 
)
SELECT Datetime, Day, Week, Month, Year, Company, sum(Value) Value
FROM source
GROUP BY Datetime, Day, Week, Month, Year, Company;


CREATE VIEW IF NOT EXISTS v_balance_railway as
with source as (
    SELECT Datetime, Day, Week, Month, Year, Company_From Company,  Value FROM railway_transport UNION ALL
    SELECT Datetime, Day, Week, Month, Year, Company_To   Company, -Value FROM railway_transport 
)
SELECT Datetime, Day, Week, Month, Year, Company, sum(Value) Value
FROM source
GROUP BY Datetime, Day, Week, Month, Year, Company;


CREATE VIEW IF NOT EXISTS v_balance_air as
with source as (
    SELECT Datetime, Day, Week, Month, Year, Company_From Company,  Value FROM air_transport UNION ALL
    SELECT Datetime, Day, Week, Month, Year, Company_To   Company, -Value FROM air_transport 
)
SELECT Datetime, Day, Week, Month, Year, Company, sum(Value) Value
FROM source
GROUP BY Datetime, Day, Week, Month, Year, Company;


CREATE VIEW IF NOT EXISTS v_balance_hour_view as
with source as (
    SELECT Datetime, Company, Value, 'maritime' Type FROM v_balance_maritime UNION
    SELECT Datetime, Company, Value, 'railway'  Type FROM v_balance_railway  UNION
    SELECT Datetime, Company, Value, 'air'      Type FROM v_balance_air
)
SELECT a.Type, a.Datetime, a.Company, b.Longitude, b.Latitude, a.Value
FROM source a
    JOIN Coordinates b ON a.Company = b.ISO;


CREATE VIEW IF NOT EXISTS v_balance_day_view as
with source as (
    SELECT Day, Company, Value, 'maritime' Type FROM v_balance_maritime UNION
    SELECT Day, Company, Value, 'railway'  Type FROM v_balance_railway  UNION
    SELECT Day, Company, Value, 'air'      Type FROM v_balance_air
),
aggDatata as (
    SELECT Type, Day Datetime, Company, sum(Value) Value FROM source GROUP BY Type, Day, Company
)
SELECT a.Type, a.Datetime, a.Company, b.Longitude, b.Latitude, a.Value
FROM aggDatata a
    JOIN Coordinates b ON a.Company = b.ISO;


CREATE VIEW IF NOT EXISTS v_balance_week_view as
with source as (
    SELECT Week, Company, Value, 'maritime' Type FROM v_balance_maritime UNION
    SELECT Week, Company, Value, 'railway'  Type FROM v_balance_railway  UNION
    SELECT Week, Company, Value, 'air'      Type FROM v_balance_air 
),
aggDatata as (
    SELECT Type, Week Datetime, Company, sum(Value) Value FROM source GROUP BY Type, Week, Company
)
SELECT a.Type, a.Datetime, a.Company, b.Longitude, b.Latitude, a.Value
FROM aggDatata a
    JOIN Coordinates b ON a.Company = b.ISO;


CREATE VIEW IF NOT EXISTS v_balance_month_view as
with source as (
    SELECT Month, Company, Value, 'maritime' Type FROM v_balance_maritime UNION
    SELECT Month, Company, Value, 'railway'  Type FROM v_balance_railway  UNION
    SELECT Month, Company, Value, 'air'      Type FROM v_balance_air
),
aggDatata as (
    SELECT Type, Month Datetime, Company, sum(Value) Value FROM source GROUP BY Type, Month, Company
)
SELECT a.Type, a.Datetime, a.Company, b.Longitude, b.Latitude, a.Value
FROM aggDatata a
JOIN Coordinates b ON a.Company = b.ISO;


CREATE VIEW IF NOT EXISTS v_balance_year_view as
with source as (
    SELECT Year, Company, Value, 'maritime' Type FROM v_balance_maritime UNION
    SELECT Year, Company, Value, 'railway'  Type FROM v_balance_railway UNION
    SELECT Year, Company, Value, 'air'      Type FROM v_balance_air
),
aggDatata as (
    SELECT Type, Year Datetime, Company, sum(Value) Value FROM source GROUP BY Type, Year, Company
)
SELECT a.Type, a.Datetime, a.Company, b.Longitude, b.Latitude, a.Value
FROM aggDatata a
JOIN Coordinates b ON a.Company = b.ISO;


CREATE VIEW IF NOT EXISTS v_flow_hour_view as
with source as (
    SELECT Datetime, Company_From, Company_To, Value, 'maritime' Type FROM maritime_transport UNION
    SELECT Datetime, Company_From, Company_To, Value, 'railway'  Type FROM railway_transport  UNION
    SELECT Datetime, Company_From, Company_To, Value, 'air'      Type FROM air_transport
)
SELECT a.*, b.Longitude Longitude_From, b.Latitude Latitude_From, c.Longitude Longitude_To, c.Latitude Latitude_To
FROM source a
    JOIN Coordinates b ON a.Company_From = b.ISO
    JOIN Coordinates c ON a.Company_To   = c.ISO;


CREATE VIEW IF NOT EXISTS v_flow_day_view as
with source as (
    SELECT Day, Company_From, Company_To, Value, 'maritime' Type FROM maritime_transport UNION
    SELECT Day, Company_From, Company_To, Value, 'railway'  Type FROM railway_transport UNION
    SELECT Day, Company_From, Company_To, Value, 'air'      Type FROM air_transport
),
aggData as (
    SELECT Type, Day Datetime, Company_From, Company_To, sum(Value) Value
    FROM source
    GROUP BY Type, Day, Company_From, Company_To
)
SELECT a.*, b.Longitude Longitude_From, b.Latitude Latitude_From, c.Longitude Longitude_To, c.Latitude Latitude_To
FROM aggData a
    JOIN Coordinates b ON a.Company_From = b.ISO
    JOIN Coordinates c ON a.Company_To   = c.ISO;


CREATE VIEW IF NOT EXISTS v_flow_week_view as
with source as (
    SELECT Week, Company_From, Company_To, Value, 'maritime' Type FROM maritime_transport UNION
    SELECT Week, Company_From, Company_To, Value, 'railway'  Type FROM railway_transport UNION
    SELECT Week, Company_From, Company_To, Value, 'air'      Type FROM air_transport
),
aggData as (
    SELECT Type, Week Datetime, Company_From, Company_To, sum(Value) Value
    FROM source
    GROUP BY Type, Week, Company_From, Company_To
)
SELECT a.*, b.Longitude Longitude_From, b.Latitude Latitude_From, c.Longitude Longitude_To, c.Latitude Latitude_To
FROM aggData a
    JOIN Coordinates b ON a.Company_From = b.ISO
    JOIN Coordinates c ON a.Company_To   = c.ISO;


CREATE VIEW IF NOT EXISTS v_flow_month_view as
with source as (
    SELECT Month, Company_From, Company_To, Value, 'maritime' Type FROM maritime_transport UNION
    SELECT Month, Company_From, Company_To, Value, 'railway'  Type FROM railway_transport UNION
    SELECT Month, Company_From, Company_To, Value, 'air '     Type FROM air_transport
),
aggData as (
    SELECT Type, Month Datetime, Company_From, Company_To, sum(Value) Value
    FROM source
    GROUP BY Type, Month, Company_From, Company_To
)
SELECT a.*, b.Longitude Longitude_From, b.Latitude Latitude_From, c.Longitude Longitude_To, c.Latitude Latitude_To
FROM aggData a
    JOIN Coordinates b ON a.Company_From = b.ISO
    JOIN Coordinates c ON a.Company_To   = c.ISO;


CREATE VIEW IF NOT EXISTS v_flow_year_view as
with source as (
    SELECT Year, Company_From, Company_To, Value, 'maritime' Type FROM maritime_transport UNION
    SELECT Year, Company_From, Company_To, Value, 'railway'  Type FROM railway_transport UNION
    SELECT Year, Company_From, Company_To, Value, 'air'      Type FROM air_transport
),
aggData as (
    SELECT Type, Year Datetime, Company_From, Company_To, sum(Value) Value
    FROM source
    GROUP BY Type, Year, Company_From, Company_To
)
SELECT a.*, b.Longitude Longitude_From, b.Latitude Latitude_From, c.Longitude Longitude_To, c.Latitude Latitude_To
FROM aggData a
    JOIN Coordinates b ON a.Company_From = b.ISO
    JOIN Coordinates c ON a.Company_To   = c.ISO;

Answer 1

Your indexes are currently defined on only the Day field, but that is not the only column you're querying by, so they pretty much don't help your queries at all.

One recurring theme in your code is GROUP BY Datetime, Day, Week, Month, Year, Company. You also SELECT these columns. You also aggregate on the Value field. Later on in your other queries, you also join by the Company field. It probably makes sense to put all of these in the same index, to make it easier on the queries when they predicate on these fields and perform the grouping.

I would recommend the following indexes for your tables to ideally make them covering:

CREATE INDEX maritime_composite1_idx ON maritime_transport (Datetime, Day, Week, Month, Year, Company_From, Value);
CREATE INDEX maritime_composite2_idx ON maritime_transport (Datetime, Day, Week, Month, Year, Company_To, Value);

CREATE INDEX railway_composite1_idx  ON railway_transport  (Datetime, Day, Week, Month, Year, Company_From, Value);
CREATE INDEX railway_composite2_idx  ON railway_transport  (Datetime, Day, Week, Month, Year, Company_To, Value);

CREATE INDEX air_composite1_idx      ON air_transport (Datetime, Day, Week, Month, Year, Company_From, Value);
CREATE INDEX air_composite2_idx      ON air_transport (Datetime, Day, Week, Month, Year, Company_To, Value);

You can verify if there's been any improvement by looking at the query plans via the EXPLAIN command. You should run this before and after tuning the indexes, to compare query plans.