CODEWITHSUNDEEP
javasqlalgorithmswingsorting
Peaches491
Peaches491

Reputation: 1239

To sort locally or to requery?

I am developing a java application which will be graphically depicting and organizing hundreds of objects. Each of these objects is loaded in from a SQL database, for use in this program.

The user sorts this data, in order to gain information about the dataset, i (as the programmer) have come across three distinct methods to accomplish this:

  1. Use individual SQL queries to return pre-sorted data to the program.
  2. Use a sorting algorithm locally to sort the entire dataset.
  3. Use a local SQLite database to copy the entire dataset to, then query the local database for sorting instead.

Which would be preferable, or are there any other solutions I may have missed?

Upvotes: 2

Views: 567

Answers (5)

Kamahire
Kamahire

Reputation: 2209

I feel that SQLite approach will be better solution.

  • Writing your own algorithm may not be good enough as SQLite provided.

  • Caching is not good enough as compared to SQLlite.

Upvotes: 0

home
home

Reputation: 12538

In general all of the solutions may work, it depends on the concrete requirements - my thoughts:

1. Use individual SQL queries to return pre-sorted data to the program

Works well if the user defines sorting criteria upfront. What if she wants to resort after data retrieval (leads to #2)? It may make sense to define a default sort for each request or a parameter influencing the sort sequence based on certain criteria like user role.

2. Use a sorting algorithm locally to sort the entire dataset

As outlined before, a combination of #1 and #2 may be what you need.

3. Use a local SQLite database to copy the entire dataset to, then query the local database for sorting instead

Totally different strategy, several things you have to care about:

  1. data synchronization
  2. security; working in large enterprises, I know that certain security people (e.g. auditors) get crazy when talking about local data replication
  3. offline capabilities (advantage)
  4. local storage

Personal option: I'd go with a combination of #1 and #2. Furthermore, I'd try to define the interface in a way that supports scrolling (e.g. "retrieve rows #20-100").

Not a definite answer, but my opinion based on the facts you provided...

Upvotes: 2

Perception
Perception

Reputation: 80623

If your result set is only a few hundred objects then you gain alot from just applying your own sort algorithm. To speed things up have your first read from the DB be sorted according to your defaults.

Upvotes: 0

Jeremy
Jeremy

Reputation: 22435

Ideally, you would only query the database once, and then keep the objects cached in memory. You can construct the initial query with a default (or dynamically configured) sort, and then sort the collection in memory after that.

There are other factors to take into consideration, though. If the database is accessed over a network, then you really should cache the objects locally. If you do end up caching the objects, you'll have to make sure to keep the state of your cache and database in sync.

Upvotes: 0

tskuzzy
tskuzzy

Reputation: 36476

Pretty much always sort in the database if you can. It will almost always be faster than first loading unsorting data into your program and then sorting it manually in your code.

The reasons for this is because database engines are optimized to do just this sort of thing (pun not intended). They are designed to be disgustingly fast at retrieving data, filtering data, and sorting data. Not only will it be faster in terms of run-time, but it will generally be significantly faster to code as well.

Never do #3 for the purpose of sorting data. That just introduces unnecessary overhead and complexity with no gain as far as I can tell.

Upvotes: 0

Related Questions