0

I'm displaying a large table on a website and now I want to add server-side filtering.

To build these filters, I need all distinct values for each column. What's the best practice to do that?

I feel like performing a SELECT DISTINCT or GROUP BY statement for each column and on every page load would be too expensive for the database.

Note: Unfortunately I can't change the database so creating tables for foreign keys is not possible.

Rui Costa
  • 417
  • 2
  • 11
timbmw
  • 3
  • 1
  • 4
    Use reference tables with foreign key relationships. Then you can just use the reference table. – Gordon Linoff Jul 09 '21 at 10:59
  • 2
    Without the option to change the database, your only route is to use `SELECT DISTINCT`. You can try caching the information to not overload the DATABASE, and also lazy load it to your website. – Rui Costa Jul 09 '21 at 11:06
  • You can use query to get all foreign keys. See: [1](https://stackoverflow.com/questions/1143728/how-can-i-find-which-tables-reference-a-given-table-in-oracle-sql-developer) or [2](https://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference-in-oracle-db/1730054) – Maciej Los Jul 09 '21 at 11:23
  • How many rows does the table have? You can cache the list of values for each filtering column in the app. Then you can refresh it every hour or so. – The Impaler Jul 09 '21 at 11:32

1 Answers1

0

If indexing the columns isn't possible and you cannot change the database structure (which is the right thing to do from a database perspective) then I see a few of options

Select Distinct

You are just going to bite the bullet and run this every.time.but it isn't ideal if the values rarely change. The results will be cached on the database so you will get some minimal efficiency.

Select Distinct Everything

Run a single query that gets all the possible combinations of distinct values. You then would need to deduplicate each column but you would only be having a single trip to the database.

This obviously depends on cardinality.

Cache the values on your app

You haven't mentioned what you are using for your webapp but you should be able to use some sort of persistence / caching framework so you load the distinct list of values once and then keep them in memory and update as necessary.

This approach also depends on where updates are taking place. If the database is changing outside of your webapp then this becomes problematic.

Jameson_uk
  • 432
  • 2
  • 12