1

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).

enter image description here

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;
2
  • SQLite is a fine enough database system that 5 GB of data is a small amount. How many rows are in your tables roughly? In order to help you with performance we'd need to see the exact query you're running, the definitions of the indexes you've created on the tables, and the query plan which you can get with EXPLAIN. FWIW, I've found most times data manipulations and aggregations are most performant when done in the database layer not the application layer. Also, curious why you're constrained to SQLite and can't use any other free database system.
    – J.D.
    Nov 10, 2023 at 13:12
  • 1
    I will start from the end, SQLite, because it must be created using R, I cannot install additional applications (limitations of the security office, fighting against them is a Sisyphean task). The main 3 tables (loaded from files, the rest are the result of their conversions) have approximately 20 million records and 8 columns. I placed the codes in the main thread by editing.
    – tomsu
    Nov 10, 2023 at 13:45

1 Answer 1

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.

8
  • In tables maritime_transport, railway_transport, air_transport I don't have column Company, where are Company_From and Company_To. You recommend to define new indexes to all columns in tables, if so, what is their point? Based on the knowledge I have, creating an index on all columns at once is no different than not creating an index at all. Am I right?
    – tomsu
    Nov 13, 2023 at 9:46
  • This is confirmed by performance tests, the times before and after their installation are equal/comparable
    – tomsu
    Nov 13, 2023 at 10:04
  • @tomsu Ah, I misread your query. You'll probably want to try two indexes then, since you're selecting from the tables on two different Company fields. One index on (Datetime, Day, Week, Month, Year, Company_From, Value) and the other on (Datetime, Day, Week, Month, Year, Company_To, Value). But your query might need to be written or the data restructured differently to really fix the performance issue. Without the results of the EXPLAIN command it's hard to tell what the query is doing and needs to fix it.
    – J.D.
    Nov 13, 2023 at 13:25
  • @tomsu "Based on the knowledge I have, creating an index on all columns at once is no different than not creating an index at all. Am I right?" - No, that's incorrect. Indexes organize the rows of the table logically in order by the fields those indexes are defined on, by those fields from left to right. So an index on columns (A, B, C) logically store the rows in order on A first, then within that sorting by B next, and then finally by C. Even if those are the only columns in the table, if you are operating on those columns such as a filter WHERE clause, it's still faster to...
    – J.D.
    Nov 13, 2023 at 13:28
  • ...search the ordered rows by an index than to search a random set of unordered rows in the table. A table with no indexes (more specifically no clustered index) is stored in a Heap then which stores the data unordered and makes searching against inefficient. The reason your performance tests are the same before and after are because the index I provided you is unlikely being used (in a sargable way). The way to confirm all of this is again by looking at what the query is actually doing before and after with the EXPLAIN.
    – J.D.
    Nov 13, 2023 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.