0

This is question related to question here, my question is rather from the perpective of Java application. How to optimally query the database, if I have a webservice that returns data from a database depending on what client is allowed to get?

  • does it make sense to do a select with the maximal set of columns and filter values in Java code?
  • or is it better to construct many SQL PreparedStatements/NativeQueries, each would ask only for columns which will be really returned?
  • is a good compromise to construct basic queries determined only by joined tables and not by selected columns (these would be all)?

What would be good both for database and maintainable application? What I know at this time:

  • most JDBC drivers/SQL databases optimize the SQL query and caches optimized version for future requests. Every new query must be analysed and compared, optimized version must be found or created ... and hold in memory ... in worst case every new variant will produce new optimized version. Will be the optimized version same for these simple selects?
    select name, surname from person
    select name from person
  • writing many variants of selects is ugly - every variant must be tested, maintained, fast ...
Community
  • 1
  • 1
dmatej
  • 1,518
  • 15
  • 24

2 Answers2

1

No, it's not a premature optimization. Only fetch columns that are of interest; fetching columns that are not of interest increases network round trips, which impacts performance.

Fetching extra columns also prevents databases from possibly eliminating access to tables in cases where access to only an index might be required.

Adam Musch
  • 13,286
  • 2
  • 28
  • 32
0

Depends, generally it's best, from a performance perspective, to filter in the database. If this gets too involved, it may be better from a readability perspective to do so at the client level.

hd1
  • 33,938
  • 5
  • 80
  • 91