Tips & Tricks

Here are listed some life hacks that can be helpful when working with our database.

Very Fast Importing of a Single CSV File (Single Table)

When starting server side (database core), you can specify arguments to import local CSV file. This way of importing is faster than web client's importer (because there is no networking to slow it down), but can only be performed when starting server side and is limited to import only single CSV file (database with only one table). The reason for this is, that this feature is there for backup purposes only, when web client CSV importer is not available for some reason. So, how to do it? You need to open command line interface (powershell or cmd on Windows) and write the following command:

Linux

./dropdbase_instarea path_to_csv_file [database_name_optional] [block_size_optional]

Windows

.\dropdbase_instarea.exe path_to_csv_file [database_name_optional] [block_size_optional]

You do not have to specify database name and block size. Database block size will be used as table block size as well. If database name is not specified, the default value 'TestDb' will be used. If block size is not specified, the default value '1048576' will be used.

Faster query execution time

Query

Use only those columns, that you truly need. It takes more memory and takes more time to execute SELECT * FROM Table; if a table has many columns than SELECT ColumnA, ColumnB FROM Table;

Data Types

If there are string enumerates, that can be internally represented as integer in database, save this enumeration in integer format for much better query performance.

Block Size

If there are minimum cache misses, the optimal block size is the largest one, that can be stored in the single graphic card (read Hardware Limitations - Block Size). Otherwise it depends on each query and data and the only way to find optimal block size is to try different block sizes on the same set of queries and choose the best one.

Hardware

Better hardware means better performance, see chapter Hardware Impact on Performance to see, what hardware components are the most important in terms of speed.

Stop Auto-Loading a Database

If you do not want to load a specific database from your databases directory, just change .db extension of the main database file to something else (e.g. .dbx).