CODEWITHSUNDEEP
sqldatabasesqlite
Hardik
Hardik

Reputation: 259

SQLite large database handling

I have database .db file and to access it I am using SQLite.

The file size of database is around 100 GB and it's have more than 300 tables.

My Requirement is I need to prepare different queries which checking the cross tables records.

Tables are joined with Primary and Foreign key.

What will be the best ways to find query output faster ?

After reading some articles I came to know I need to run

is there anything else I can do ?

Upvotes: 2

Views: 2412

Answers (2)

Daniel Flöijer
Daniel Flöijer

Reputation: 184

Like Tianyun Ling writes your question is too broad. You need to analyze what queries you run to figure out how to best optimize your database and indexes. If you need to read data in many different ways this is no trivial task. For instance, remember that there's always a trade-off in adding indexes. You will read data quicker, but updates will be slower. Try starting with searching this site and the web for tutorials etc to give you a starting point. Then if you have more specific questions come back and ask them here.

Also, as Tianyun Ling and others have implied, SQLite is not suited for such a large database. I advice you to switch to a different database manager.

From the SQLite webpage (my highlight):

Suggested Uses For SQLite:

  • Application File Format. Rather than using fopen() to write XML, JSON, CSV, or some proprietary format into disk files used by your application, use an SQLite database. You'll avoid having to write and troubleshoot a parser, your data will be more easily accessible and cross-platform, and your updates will be transactional. (more...)

  • Database For Gadgets. SQLite is popular choice for the database engine in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. SQLite has a small code footprint, makes efficient use of memory, disk space, and disk bandwidth, is highly reliable, and requires no maintenance from a Database Administrator.

  • Website Database. Because it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites.

  • Stand-in For An Enterprise RDBMS. SQLite is often used as a surrogate for an enterprise RDBMS for demonstration purposes or for testing. SQLite is fast and requires no setup, which takes a lot of the hassle out of testing and which makes demos perky and easy to launch.

http://www.sqlite.org/features.html

And further:

Appropriate Uses For SQLite

SQLite is not directly comparible to other SQL database engines such as Oracle, PostgreSQL, MySQL, or SQL Server since SQLite is trying to solve a very different problem.

Other SQL database engines strive to implement a shared repository of enterprise data. They emphasis scalability, concurrency, centralization, and control.

SQLite, on the other hand, strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite is not designed to compete with Oracle. SQLite is designed to compete with fopen().

http://www.sqlite.org/whentouse.html

Upvotes: 0

CL.
CL.

Reputation: 180270

When the data does not fit into any cache, the execution time of a query is dominated by the time needed to read all the data from disk.

Indexes help reduce the amount of data that needs to be read (a binary search in an index needs to read only a few entries, while scanning a table must read all rows).

There is no other method to reduce the amount of data, unless you rewrite the query to return less data, or to not doing unneeded lookups. This always depends on the actual query.

Upvotes: 4

Related Questions