Is better to use a for over a list or multiple access to database-CodePudding

I'm having a doubt over the better implementation of a method that is often used. The solution in production as the following form:

public List<Element> method(String text) {  
        List<Element> element = select * from element e where e.text like text
        return element
        }

the new implementation instead load the complete list 1 time

List<Element> allElement =select * from element

and the method become something like

    public List<Element> method(String text) {  
        List<Element> element; 
        for Element e : allElement{
          if e.text.contains(text) element.add(e)
        }
        return element
        }

Which one is the better solution?

CodePudding user response：

First approach looks better , as you are leveraging DB functionality to filter the result(Which is more optimised to do that task). Second approach significantly increases the roundtrip time required to fetch all the elements(depending on number of elements)and added time complexity as you are looping through whole list and also it might consume more memory on your application

CodePudding user response：

Its much better to get all the data in one go. This is because you don't need to waste time on TCP/IP packet roundtrips to the database each time.

At the same time, be aware that if the result set you are trying to load in one go is too big you can get an OutOfMemory error.

In short:

Getting data in one go is better
Be sure that you have enough RAM on the JVM to accomodate the (potentially) large data set.