iOS Which is better? 5000 tables with 3000 records vs 1 table with 15000000 records

[mikezang]

I have 5000 binary files and I thought to read and update those files at first, I knew it is not good idea after some test.

I want to use SQLite3 to store those data, my question is what store methos is better in this specific case.

1. create 5000 tables and every table including about 3000 records
2. create 1 table and 15000000 records inside that table.

One more detail about file, one file is for one stock data, basically, in most case, only one stock's data should be read in one time, except when I need to draw a compare graph for some stocks.

I hope you can give me your suggestion based on your experience about SQLite3.

 

[Sykte]

I have 5000 binary files and I thought to read and update those files at first, I knew it is not good idea after some test.

I want to use SQLite3 to store those data, my question is what store methos is better in this specific case.

1. create 5000 tables and every table including about 3000 records
2. create 1 table and 15000000 records inside that table.

One more detail about file, one file is for one stock data, basically, in most case, only one stock's data should be read in one time, except when I need to draw a compare graph for some stocks.

I hope you can give me your suggestion based on your experience about SQLite3.

I'm confused on why you would want to create 5,000 tables..... Could you explain a little more regard what you are trying to accomplish. You may want to research SQL relationships.

 

[mikezang]

I just want to know the performance in different cases, then one file is for one stock data so I think one table is for one stock data.

 

[Sykte]

Not sure, can't say I have ever seen one application that needed 5000 tables.

 

[Hansr]

One table per ticker is a horrid idea. Use one table and index on the ticker.

 

[mikezang]

One table per ticker is a horrid idea. Use one table and index on the ticker.

5000 tables just a imaging.

So one table with all records and index is better? How about 10 tables? I am afraid the table is too large and with a bad performance...

 

[Hansr]

5000 tables just a imaging.

So one table with all records and index is better? How about 10 tables? I am afraid the table is too large and with a bad performance...

10 Tables is acceptable but if they all contain the same data it's meaningless because indexing them correctly will give you the same speed because it's a relational database.

Normally when working with tickers need to be accessed quickly I'd use kdb+ instead of a SQL based db but that's quite note applicable for iPhone dev.

 

[mikezang]

10 Tables is acceptable but if they all contain the same data it's meaningless because indexing them correctly will give you the same speed because it's a relational database.

Normally when working with tickers need to be accessed quickly I'd use kdb+ instead of a SQL based db but that's quite note applicable for iPhone dev.

Like you said, in fact, I thought use original binary files for every stock, but it is said that will be slow in iPhone so that I think I have to convert files to SQL, do you think it is better to use original binary files?

Original format
struct {
int date,
int open,
int high,
int low,
int close,
float volume,
}

 

[Hansr]

Nope I think SQLlite3 is a better option because de-constructing the data from the binary would be slow and cumbersome memory wise.