ThinkGeo.com    |     Documentation    |     Premium Support

SQLiteFeatureLayer Table Definition

#1

MapSuite Team,

I’m looking to get more speed out of my large SQLiteFeatureLayer(s).

Below is an example of the SQL to create a table. Would you offer your opinion on these statements from the perspective of table access efficiency?

Thanks,
Dennis

CREATE TABLE Buildings (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, geometry BLOB, date_bldg_ NUMERIC, time_bldg_ TEXT, bldg_condi TEXT, bldg_id NUMERIC, bldg_name1 TEXT, bldg_name2 TEXT, bldg_sq_fo NUMERIC, bldg_statu TEXT, cdb_city_i TEXT, comments TEXT, date_condi NUMERIC, time_condi TEXT, create_use TEXT, date_demol NUMERIC, time_demol TEXT, date_edit_ NUMERIC, time_edit_ TEXT, edit_sourc TEXT, edit_useri TEXT, f_add1 NUMERIC, footprint_ TEXT, harris_str TEXT, label_hous TEXT, no_of_unit NUMERIC, no_stories NUMERIC, non_standa TEXT, orig_bldg_ NUMERIC, pre_dir1 TEXT, date_qc_da NUMERIC, time_qc_da TEXT, qc_source TEXT, qc_userid TEXT, shape_area NUMERIC, shape_len NUMERIC, st_name1 TEXT, st_type1 TEXT, stories NUMERIC, suf_dir1 TEXT, t_add1 NUMERIC, unit_name TEXT, vacancy_st TEXT, x_coord NUMERIC, y_coord NUMERIC, year_built NUMERIC, z_coord NUMERIC)


CREATE VIRTUAL TABLE idx_Buildings_geometry USING rtree_i32(Id, minx, maxx, miny, maxy)


CREATE UNIQUE INDEX IF NOT EXISTS main.idx_Buildings_Id ON Buildings (Id);
#2

Hi Dennis,

Create index is most important, after did it, you should want to split your data into different databases and different tables for make sure one table is not so heavy. Please optimize your database format follow your requirement here.

And you can modify some setting to get better performance for example increase cache value(PRAGMA cache_size), set Journal mode(PRAGMA journal_mode = memory) etc. I think you can find more articles about it from Google.

And if you want to read data by your custom code, please notice the sql statement effect the performance, please optimize it.

Wish that’s helpful.

Regards,

Ethan

#3

hi Ethan,

Thanks for the suggestions. Are the below statements the proper way to issue PRAGMA?

The return on the ExecuteNonQuery is a -1.

I ask because there seems to be no difference in rendering speed no matter the value of cache_size. It can be set to 0, 4096, 8192, or any other value with no difference in speed.

Thanks,
Dennis

int TheExecuteNonQueryResult;
string TheExecuteNonQueryPragma;

SQLiteFeatureLayer TheFeatureLayer;

TheFeatureLayer.Open();

TheExecuteNonQueryPragma = string.Format("PRAGMA  journal_mode = memory;");
TheExecuteNonQueryResult = TheFeatureLayer.FeatureSource.ExecuteNonQuery(TheExecuteNonQueryPragma);

TheExecuteNonQueryPragma = string.Format("PRAGMA  cache_size = 4096;");
TheExecuteNonQueryResult = TheFeatureLayer.FeatureSource.ExecuteNonQuery(TheExecuteNonQueryPragma);
#4

Hi Dennis,

I found the optimize way for call “PRAGMA” statement is from internet, I think that’s maybe helpful for certain scenario, if that’s not works in your machine you can try other ways.

And our API mainly works for “Select” statement, “PRAGMA” works for modify the environment variables of SQLite, I am not sure whether it works by our API, so you should want to try set that via command line.

Regards,

Ethan