Simple database needed to store JSON using C#

Question

I'm new to databases. I've been saving a financials table from a website in JSON format on a daily basis, accumulating new files in my directory every day. I simply parse the contents into a C# collection for use in my program and compare data via Linq.

Obviously I'm looking for a more efficient solution especially as my file collection will grow over time.

An example of a row of the table is:

    {"strike":"5500","type":"Call","open":"-","high":"9.19B","low":"8.17A","last":"9.03B","change":"+.33","settle":"8.93","volume":"0","openInterest":"1,231"}

I'd prefer to keep a 'compact file' per stock that I can access individually as opposed to a large database with many stocks. What would be an 'advisable' solution to use? I know that's a bit of an open ended question but some suggestions would be great.

I don't mind slower writing into the DB but a fast read would be beneficial.

What would be the best way to store the data? Strings or numerical values?

I found this link to help with the conversion How to Save JSON data to SQL server database in C#?

Thank you.

Charlie · Accepted Answer · 2018-03-11 11:12:28Z

0

For a faster reading in a DB, I would suggest denormalization of the data.

Read "Normalization vs Denormalization"

Judging from your JSON file, it doesn't seems like you have any table joins. Keeping that structure should be fine.

For the comparison between varchar(string) and int(numeric), int are faster than varchar, for the simple fact that ints take up much less space than varchars. int takes up 2-8 bytes where varchar takes up 4 bytes plus the actual characters.

answered Mar 11, 2018 at 11:12

Charlie

841 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sim22 Over a year ago

Thank you Charlie. Do you think SQLite would suit my purpose?

Charlie Over a year ago

Yes, SQLite would have a better performance. But then there are some limitiation on it compared to SQL server.

KansaiRobot Over a year ago

@CharlieAngriawan why did you mention the bit about table joins when using sqlite?

Collectives™ on Stack Overflow

Simple database needed to store JSON using C#

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related