sql query performance: finding max

Question

I have a table with group and permission column. I want to find the max permission from a list of group. I am using java and oracle database, I thought of two ways to do this:

Way 1:

in java loop through the group list
    result = select permission from table where group = currentgroup
    if result > max, max = result

Way 2:

max = select max(permission) from table where group in (group list)

I thought way 2 would be faster, but then group list can be very long and I dont know if it is a good idea to have long list in a single sql query.

I don't understand, why should the list length affect the outcome of MAX() unless your query is flawed? — Sterling Archer
– Sterling Archer, Commented Oct 1, 2013 at 14:58
From where do you get this group list? I would assume it comes from your DB. Use an inner join on your groups table. Then you can both do all the work on the DB and not have to provide the entire groups list in the query. — Dark Falcon
– Dark Falcon, Commented Oct 1, 2013 at 14:59
In oracle there are limits for maximum number of bind variables in a query. Maybe there is a limit for constants also. Anyway I think the bind variable limit is at least 1000. and the constant limit if there is any should be much higher. — Lajos Veres
– Lajos Veres, Commented Oct 1, 2013 at 15:00
@help: Is you group list a variable? If so, then please rewrite it as group_list (or whatever), so it doesn't get confused with the GROUP keyword. — maaartinus
– maaartinus, Commented Oct 1, 2013 at 15:14

BlackBox · Accepted Answer · 2013-10-01 15:00:23Z

1

From the information you've given, the second approach is by far the best. Databases are optimised directly for these kinds of tasks, so within reason, its always best to narrow the data down with the database. The first approach means the database needs to return all values anyway, increasing processing time, bandwidth and using up memory within your java application.

answered Oct 1, 2013 at 15:00

BlackBox

2,2331 gold badge22 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

sql query performance: finding max

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related