-
Jan 19th, 2017, 09:39 AM
#1
Thread Starter
Fanatic Member
Database design advice
Hello Everyone,
I am just started using SQLite database system for my application. The application is related to stock market data. It will read from a text file and update data in database. Another program will read data from database and do some work.
The current scene is I have created 1 database for each company. Each company can contain from a hundred thousand records to over three hundred thousand. The number of companies can be around 500. Thus there are 2 scene:
1) create 500 or whatever number databases and read and write to them. The pro is in case one gets corrupt the others will work. Plus we only need to manipulate records for that company so the number of rows will be around the figure I mentioned above. Con: Most of time I need to read a bunch or all of them so I need to open connection read or write then close and repeat the process.
2) Have only 1 database and put all the data in it. Pro: managing is easy and we only need to open 1 db connection rather than each connection for scene above. Con: The database can have over fifty million records and that's a good number. Reading or inserting may take time (I ain't sure).
Currently I open a connection for a db, read or write then close. This happens in loop as each company is a db. Is this method ok?
Any advice will be appreciated.
Thank you,
Cheers,
GR
-
Jan 19th, 2017, 10:02 AM
#2
Re: Database design advice
A properly designed database shouldn't have an issue with 50 million rows and personally I would much rather work with a single database rather than 500 separate databases.
If you are needing to deal with all the rows for a particular customer then using a WHERE clause on your SQL will filter it to just the records you need, if you have appropriate indexing this should be a fast operation.
Unless you are hitting the limits of a single database I wouldn't go the multiple database route as it will add a lot of complexity without many benefits.
-
Jan 19th, 2017, 11:13 AM
#3
Thread Starter
Fanatic Member
Re: Database design advice
Originally Posted by PlausiblyDamp
A properly designed database shouldn't have an issue with 50 million rows and personally I would much rather work with a single database rather than 500 separate databases.
If you are needing to deal with all the rows for a particular customer then using a WHERE clause on your SQL will filter it to just the records you need, if you have appropriate indexing this should be a fast operation.
Unless you are hitting the limits of a single database I wouldn't go the multiple database route as it will add a lot of complexity without many benefits.
I guess I can try it. By default sqlite creates index automatically on primary key. However in this case primary key will be a combination of 2 columns. Will SQLite also auto create index in this case?
Last edited by greatchap; Jan 19th, 2017 at 11:21 AM.
-
Jan 19th, 2017, 11:53 AM
#4
Re: Database design advice
I really don't know SQLite that well but I would imagine it is fairly easy to create indexes on columns, http://www.sqlitetutorial.net/sqlite-index/ might be worth a look.
-
Jan 19th, 2017, 11:58 PM
#5
Thread Starter
Fanatic Member
Re: Database design advice
Originally Posted by PlausiblyDamp
Thank you and I did read about it. However I have two questions.
1) Before I create an index I need to check if an index exists or not
2) Once I create an index when do I need to re-index it. Or does index gets updated automatically as data is added.
-
Jan 21st, 2017, 11:07 AM
#6
Re: Database design advice
Originally Posted by greatchap
I guess I can try it. By default sqlite creates index automatically on primary key. However in this case primary key will be a combination of 2 columns. Will SQLite also auto create index in this case?
Yes.
Olaf
-
Jan 21st, 2017, 11:19 AM
#7
Re: Database design advice
Originally Posted by greatchap
I am just started using SQLite database system for my application. The application is related to stock market data. It will read from a text file and update data in database. Another program will read data from database and do some work.
The current scene is I have created 1 database for each company. Each company can contain from a hundred thousand records to over three hundred thousand. The number of companies can be around 500. Thus there are 2 scene:
1) create 500 or whatever number databases and read and write to them. The pro is in case one gets corrupt the others will work. Plus we only need to manipulate records for that company so the number of rows will be around the figure I mentioned above. Con: Most of time I need to read a bunch or all of them so I need to open connection read or write then close and repeat the process.
2) Have only 1 database and put all the data in it. Pro: managing is easy and we only need to open 1 db connection rather than each connection for scene above. Con: The database can have over fifty million records and that's a good number. Reading or inserting may take time (I ain't sure).
I'd go with 1), if your DBs have a simple Schema (not more than 5 Tables) and
if the cases are rare, where you have to do a Select across tables of two or more Companies
(although the latter issue can be solved by attaching other DB-Files to an existing Connection).
Originally Posted by greatchap
Currently I open a connection for a db, read or write then close. This happens in loop as each company is a db. Is this method ok?
Opening a Connection with a simple Schema (< 5 Tables) takes about 1-2msec,
a midsized Schema 5-30 Tables with a lot of Columns, Indexes, Views, and Foreign-Table-Definitions takes about 5-20msec -
and really huge DB-Schema-Definitions (DBs with more than 50 Tables) will need perhaps 50-100msec at the DB-Open-stage.
This is the only thing you will have to consider.
(in your case, I think the 1-2msec will apply - just do your own tests, and maybe time your
"loop over the Company-DBs" when all you do in it, is just the Connection-Open-call - just to be sure.
Olaf
-
Jan 21st, 2017, 11:26 AM
#8
Re: Database design advice
Originally Posted by greatchap
Thank you and I did read about it. However I have two questions.
1) Before I create an index I need to check if an index exists or not
2) Once I create an index when do I need to re-index it. Or does index gets updated automatically as data is added.
Of course an index will be automatically updated when Data is added
(and therefore too many un-needed Indexes can slow down your "Bulk-Insert-performance").
As said, in case you have defined your important Columns already as a Primary-Key,
you don't need to create any extra-indexes for them.
If those Primary-Keys are a combination of Fields, or in case of single Columns, are not defined as "INTEGER PRIMARY KEY",
you can optimize further (sparing the RowID-automatic-indexing of SQLite) by using " WITHOUT ROWID" at the end of your Create Table statement.
https://www.sqlite.org/withoutrowid.html
Olaf
-
Jan 22nd, 2017, 11:59 PM
#9
Thread Starter
Fanatic Member
Re: Database design advice
Thank you for your reply.
My database has 1 table with fields: symbol text ,dt datetime,open real,high real,low real,close real,vol int
The primary key in on 2 fields i.e. Symbol & Dt. In such a case do you think I should create an index because the primary key is on 2 fields and not one. Plus the row id thing you mentioned, does it apply here (Without RowID).
I am currently creating 1 index on Symbol & Dt (primary keys).
Last edited by greatchap; Jan 23rd, 2017 at 01:24 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|