CODEWITHSUNDEEP
javasqldatabasespringperformance
WHATEVER
WHATEVER

Reputation: 3

What is the most efficient way to retrieve specific data from DB without doing loop for all records?

I want to ask about what is the most efficient way to search about specific data from a database without doing a for loop in all of the records?

I have a project on java spring and I have this Entity:

@Entity  
@Table(name = "USERS") public class USERS  {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "UID") 
private Integer id;

@Column(name = "FName") 
private String firstName;

@Column(name = "SName") 
private String secondName;

@Column(name = "TName") 
private String thirdName;

@Column(name = "LName") 
private String fourthName;

@Column(name = "Email") 
private String email;

@Column(name = "PW") 
private String password;

@Column(name = "MNumber") 
private String mobileNumber;

@Column(name = "ISDeleted") 
private boolean isUserDeleted;


//---------------------- Getters and Setters ----------------------

and I made this service:

public List<USERS> findAllActive() {
    List<USERS> usersList = new ArrayList<USERS>();
    for (USERS users: usersRepository.findAll()){
        if (!users.isUserDeleted()){
            usersList.add(users);
        }
    }
    return usersList;
}

For example; I have one property for User, if he is active or not.

So, my question; what is the most efficient way to do get specific data like retrieving all of the active users from the DB without doing a for loop like in the code above? Because if the list of users is a 1 Million or more, it could have performance issues.

Upvotes: 0

Views: 207

Answers (4)

Deepak Jain
Deepak Jain

Reputation: 347

You don't need to specify any sql query or where clause. CrudRepository will do it for you automatically. Just use below code and pass true/false on need basis

List<Users> findIsUserDeleted(boolean isDeleted)

Upvotes: 0

Twister
Twister

Reputation: 161

The solution from @Madis is okay. But if you always want to get users which are not deleted in all queries, you can specify it on Entity:

@Entity 
@Table(name = "USERS")
@Where("ISDeleted = false")
public class USERS  {

So now the condition "ISDeleted = false" is automatically append to all queries from the UserRepository. You can use usersRepository.findAll() instead of.

Upvotes: 0

user10367961
user10367961

Reputation:

First of all, use an index on the field you want to search on (this won't help you much if the column has only two distinct values, but will make a huge difference if the value has high sparsity).

@Entity 
@Table(name = "USERS",
       indexes = {
           // not a huge performance gain, since the column values are true/false
           @Index(name = "index_by_active",  columnList="ISDeleted", unique = false),
           // possible huge performance gain, since only the relevant records are scanned        
           @Index(name = "index_by_first_name", columnList="FName", unique = false)})
public class USERS {...}

Then, define a query method that uses the indexed field (if you are using spring data it would look as follows).

public interface UsersRepository extends CrudRepository<USERS, Long> {

    List<USERS> findUsersByISDeleted(boolean deleted);

    List<USERS> findUsersByFName(String name); 

    List<USERS> findUsersByFNameAndISDeleted(String name, boolean deleted); 

}

Queries on indexed fields will leverage the underlying index and provide an efficient access plan (so you won't end up scanning the whole table in order to extract a subset of entities matching a given criteria).

Upvotes: 0

Madis R
Madis R

Reputation: 121

Assuming that you are using JpaRepository then you can create custom query.

@Query("SELECT u FROM USERS u WHERE u.userDeleted = false") 

List<USERS> findNotDeletedUsers();

and then call usersRepository.findNotDeletedUsers();

Upvotes: 2

Related Questions