Database to C# in the most efficient way

Question

I’m working with the above database design in SQLite. The goal is to load the data into a C# application in the shortest amount of time possible. The amount of records in the Address table can go from a couple of 100's to a couple of 10.000's depending on how many Customers there are (not yet integrated in the model). Ultimately, every address in the world should be able to be in the database generating millions of records. This should also be taken in consideration when inserting the data in C#. I know it can’t be as fast if there are only 100 records, but it should just be as fast as possible.

For the middle line (Continent -> Address) I came up with the following query:

SELECT * FROM ((((Continent 
LEFT OUTER JOIN Country ON Continent.Code = Country.ContinentCode) 
LEFT OUTER JOIN SubDivision ON Country.Code = SubDivision.CountryCode)
LEFT OUTER JOIN City ON SubDivision.SubDivisionID = City.SubDivisionID)
LEFT OUTER JOIN Address ON City.CityID = Address.CityID)
ORDER BY Continent.Code ASC, Country.Code ASC, SubDivision.SubDivisionID ASC, City.CityID ASC;

Approach 1

This query gets all the one-to-many information from the database in a single table. The way I would approach inserting this in C# is by inserting the 1st row as new data. Then, check every time if the last inserted info is different. If it’s different, create a new object, if it’s the same, use the last object and this for each table. (Each table represents a model in C#.) The 5 tables would be “converted” to 5 models in a single step.

All good but then there is no information about the spoken languages, used currencies and capitals. This would be added later on then by looking up indexes in the lists of continents, countries, ...

Approach 2

A second approach I'm thinking about is as follow: Query the continents and insert them in a list. Query the languages and currencies and insert them in a list. Query the countries with information about the languages and currencies. This will generate duplicate country records but if sorted, can be sealed with by checking if they’re already added or not and just add the language and currency to the list inside the country model. Rinse and repeat for the rest of the data.

By doing this, each time something is inserted to C#, an index will have to be looked up in the lists so no duplicate objects exist. Also multiple queries have to be processed to insert everything.

Are any of my 2 approaches good or is there a 3rd better approach? I will admit I haven’t tested either of the above approaches to save programming time and I rather think first about the problem instead of trying something and realizing it’s not working.

Although that's not your question: you have no zipcode field... and if you want to get frustrated, look into english addresses with their multiple street and city names and maybe italian where they have differently colored house numbers (so you can have a number 1 red and a number 1 black in the same street). — nvoigt
– nvoigt, Commented Oct 20, 2015 at 15:41
Oh and "millions of records" would maybe cover a small country like the netherlands. "The world" will surely be a ton of data that is too much to load into RAM anyway. — nvoigt
– nvoigt, Commented Oct 20, 2015 at 15:43
And you need to find a way to store "10a" in your "house number" (or make that two fields). — nvoigt
– nvoigt, Commented Oct 20, 2015 at 15:44
@alex, What I understood from Code Review is that only completed code can be on there. — Krowi
– Krowi, Commented Oct 20, 2015 at 19:22
@Krowi Concerning RAM, you already have it in a database. Why would you want to load it all into RAM anyway? Does having your model in RAM at once grant you any benefit that you need? If so, maybe asking for the required benefit directly might be the better question. — nvoigt
– nvoigt, Commented Oct 20, 2015 at 19:43

Antonín Lejsek · Accepted Answer · 2015-10-21 01:59:30Z

1

You can just load every table into dictionary with PK as a key. It would not waste memory or connection bandwidth, would be quite fast, object relations can be looked up easily. Question really is, why would You want to mirror the whole database into Your application.

answered Oct 21, 2015 at 1:59

Antonín Lejsek

6,1132 gold badges18 silver badges18 bronze badges

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

1 Comment

Krowi Over a year ago

A reason for me would be to see which information is in the database. If for example I want to see which continent or countries with their detailed information are in the database. I know going further than cities is kind of useless since you probably know which country the city is in in you want to load it into C# which reduces the amount of data significantly.

Collectives™ on Stack Overflow

Database to C# in the most efficient way

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related