Querying a data from table and put to Map

[Hisouka Devera]

Good Day,

I have a table like this

COUNTRY_CODE | NUMBERS
US | 1234
US | 2341
AU | 3452
AU | 3423
AU | 3333

I would like to query all the data in the table that will store into a Map<String, List<Integer>>
which will put the COUNTRY_CODE as the Key and all the NUMBERS in the List of Integer.

so my Map will be:
US,[1234,2341]
AU,[3452,3423,3333]

Regards

[salvin francis]

That's an interesting program. How much have you done so far ?

[salvin francis]

I would like to suggest the following approach :

Query the database for the data and fetch the result in a resultset.

Loop through the resultset data and for every loop iteration, you can call a method like "insertDataIntoMap" with the country code and numbers

Inside the "insertDataIntoMap" method, you would need to first check if the map already contains the key or not, if it does not, insert a new List containing the value

If it does exist,fetch the list and append your value to it.

[Dave Tolls]

A minor modification to salvin's flow, from my point of view, would be ORDER BY COUNTRY_CODE.
That way you can build each List and do a single put into the Map, without a load of gets. When the country_code changes, put the current List into the Map and start a new one for the new code.

[Hisouka Devera]

salvin francis wrote:I would like to suggest the following approach :

Query the database for the data and fetch the result in a resultset.

Loop through the resultset data and for every loop iteration, you can call a method like "insertDataIntoMap" with the country code and numbers

Inside the "insertDataIntoMap" method, you would need to first check if the map already contains the key or not, if it does not, insert a new List containing the value

If it does exist,fetch the list and append your value to it.

This is a very good approach.

I would like to ask only if this approach is fast enough? as it takes a loop and a comparison. Also the data is approx a million or two.

Thanks so much.

[Hisouka Devera]

Dave Tolls wrote:A minor modification to salvin's flow, from my point of view, would be ORDER BY COUNTRY_CODE.
That way you can build each List and do a single put into the Map, without a load of gets. When the country_code changes, put the current List into the Map and start a new one for the new code.

Hi, Do you mean I need to query by country code? correct me if im wrong.

Thanks so much.

[salvin francis]

Hisouka Devera wrote:I would like to ask only if this approach is fast enough? as it takes a loop and a comparison. Also the data is approx a million or two.Thanks so much.

Well, then you need to tell us what is it that you are trying to achieve here. Why are you intending to hold data of millions of records in memory in a Map ? If Its some kind of filtering then don't you think this is a problem well suited for an SQL query ?

[Campbell Ritchie]

Surely you will be using
SELECT COUNTRY_CODE, NUMBERS FROM COUNTRIES ...
and you can add the ORDER BY instruction to that query.

[Hisouka Devera]

Campbell Ritchie wrote:Surely you will be using
SELECT COUNTRY_CODE, NUMBERS FROM COUNTRIES ...
and you can add the ORDER BY instruction to that query.

Then I will use Salvins' approach?

[Dave Tolls]

Hisouka Devera wrote:
Then I will use Salvins' approach?

I would still use my modification. That's why the ORDER BY is there.

Anyway, you should think about salvin's question re: why are you reading all this data out into a Map in the first place?

[Tim Holloway]

Dave Tolls wrote:A minor modification to salvin's flow, from my point of view, would be ORDER BY COUNTRY_CODE.
That way you can build each List and do a single put into the Map, without a load of gets. When the country_code changes, put the current List into the Map and start a new one for the new code.

What I usually do is more like this - which doesn't require ordered data:

if ( ! map.containsKey(countryCode) ) {
    map.put( countryCode, new ArrayList<Numbers>);
}
List<Numbers> list = map.get(countryCode);
list.add(newestNumber);

For better results, make the list constructor initialize for the average number of Numbers if using an ArrayList, otherwise there will be hidden overhead if the default List doesn't have enough free slots or wasted storage if the default list is too long. Or you could use something like a LinkedList, if sequential retrieval speed isn't a major consideration (for example, less than 10 objects/list). A LinkedList doesn't require growth slots.

[Dave Tolls]

I have a basic rule with DBs, and that's get the data out of the database in the structure you want to use it.

The less processing the Java side has to do the better.

A related rule is, only get the data you actually need.

Essentially, where possible, get the database to do the work.

In this case, since it's going into a Map, I would argue that structure has an ORDER BY associated with it based on the key.
Of course, there is still the question of whether all this data should be stuck in a Map. It's an awful lot of data.

[Tim Holloway]

A Map is NOT an ordered collection, however, and the example didn't show the List data in order, either. So there's no abstract virtue in making the database server do the extra work to sort data before transferring it.

Certain types of Maps, in fact, will have horrible performance when loaded sequentially, especially anything that uses balanced trees as indexes. That's why preferred practice to bulk loading database tables is to load the data first and then build the index after all the data is loaded.

[Dave Tolls]

Well, a GROUP BY doesn't work.
The ORDER BY is so you don't have to keep querying the Map for a List.
That's all I mean.
There's an implicit grouping needed (by country code), and ORDER BY is the way to achieve that.

[salvin francis]

Its a different discussion altogether to load it list wise using order by vs to load it in unordered fashion as per my original approach.

What I am trying to get at is... why load such huge data in a collection in the first place ?
I am not saying its wrong, but I would definitely ask my self this a lot of times before doing so.

[Tim Holloway]

If you've got a choice between repeated hash lookups and index re-organizations, the lookup is going to win by a wide margin almost every time. Obviously, we're talking premature optimization here, in any event, but I prefer not to make the DBMS server do any more work than it needs to, since while that machine(s) is/are probably beefier than the average servers, they're also presumably also going to have multiple demands, so up to a point I'd rather put the load on the client rather than on the DBMS server. That point, incidentally usually DOES include having the DBMS do sorting, but only if the data actually benefits from being ordered.

More to the point, as Salvin reminds us, keeping large data sets in RAM isn't something that's usually desirable. Often the overhead of on-demand database fetches is low enough to outweigh the supposed benefits of having everything local (especially for volatile datasets!) And where it isn't, frequently using a local cache manager can split the difference.

[Dave Tolls]

Fair points, and I quite agree that salvin's question needs answering.
Obviously the initial answers were based on the small example data set.
Millions of rows is another issue entirely...and an ORDER BY there would put a load on whatever area the db in question uses for handling sorts.