Working on Collections is so slow ?

Not sure of the purpose of your query as your not doing anything with the query. You set your location array and at the end only return that array.

Secondly why don't you fetch a collection of all rows with a location versus looping through for each one?

You can also set an array inside first () and get() to only return the columns you want, first(['name']);

Hi jekinney, I'm storing for every anons location path ip like for example 1.12.235.589, next as you can see I'm exploading that path, and foreach I'm returning Location by id and finally I have attatched locations path to Anons model and I have there ip, slug and name, now I hope it make sens. But what about that performance problem? I'ts really so slow or I'm doing something wrong? I don't see to much place for making mistake, this is few lines of code.

@monsterdream you can load everything from the database in one single query, and then do the foreach on the loaded collection.

That way you'll get a significant performance improvement, as only one call to your database will be made, and the rest is just all PHP from then on.

112 queries whooops.

$location_numbers = get_all_locations_number();

$locations = Location::whereIn('id', $location_numbers)->get();

1 query.

Maaaan, I've done that in second exempel, I pased collection with all Locations and do that same in loop. That why I was expecting better performance but instead of that its 2-3x slower even that I'm saved more then 90 querries. That why I created that thread.

Your code can be simpler

$location_map = Location::whereIn('id', $location_ids)->lists('name', 'id')->all();

$location_name = $location_map[$location_id];

Hi again after whole day. I don't know if you understand my problem, please look again on code. In example nr 1 I'm doing 1 query for all locations and get every location by id from that Collection, it take 1,5 sek overall. In example nr 2 I'm doing query every time in loop (90 times overall) BUT in that case it is almost 3x faster.

Shouldn't be exactly opposite ? I was thought example nr 1 will take at last that same amount of time as example nr 2, I was hoping it will be even faster.

    public function locationPath($locationIp)
    {
        $allLocations = Location::all(); // All locations for example nr 1
        
        $locationArray = explode('.', $locationIp); // exlpolde for example 1.12.42.241

        foreach ($locationArray as &$location)
        {
            //example nr 1 -> this browse from above allLocations (1 query) -> total time 1,5sek
            $location = $allLocations->where('id', intval($location))->first()->name;
            
            //example nr 2 -> this make 90 queries -> total time 0,5sek
            $location = Location::select('name')->where('id', $location)->first()->name;
        }      
        return $locationArray;
    }

I made some research, you can see results below. As I thought, it seems to be like as I said, in that case is worth it to make additional 90 queries instead of 1 and working with Collection. But I was wrong, the difference is 10x instead 3x as I thought of beginning.

So what exactly are we looking at in the left and right columns? Neither one seems to be demonstrating a single query as you're suggesting. They both demonstrate numerous queries.

Sorry, explanation is clear when you read from beginning. Left side present method when I make a query to database in loop which generate something about 90 queries. I was hoping to optimize that making 1 query to variable where I have Collection all Locations and work on it asking that Collection for specific Location (right side on image), of course that does not generate any more queries to database but in the summary after research it take 10x more time then method 1 with that 90 extra query.

When you need to do so much manipulations to get usable data, it is time to reconsider if your database structure is suited to your application. Well structured data > any code.

Maybe I'm wrong, but if I understand correctly: You used to do 90 queries, using about 51ms. Now you do 33 queries, in about half a second. And you don't understand why. Well, apparently, your queries are more complicated then the first ones.

What you should do, is use 1 query, as @pmall sugested.

Apparently you people continue to ignore the problem at hand, which is that some collection operations are slow.

@Prullenbak It is not the queries that are slow. I do the exact same amount of queries now and with no changes in application logic except we switched from Collection::where to Collection::keyBy + Collection::find. The speedup was from 30 seconds to <1 second for a complicated problem. And it is obvious why that is the case if you read my answer. I suspect OP had the same kind of problem.

@pmall Not everything can be solved by better data modelling. There is always the fundamental problem of actually doing the calculations somewhere. Should we prepopulate the database with the values or should we calculate on demand? In our use case it is far better to calculate on demand since the data changes frequently, is read seldom, depends on complex rules and when you read it you only need it once. Of course you should always consider your data model but sometimes the best solution is to do it in code.

@pixelstore I was talking to the OP. He's asking/saying if/that the collections in laravel are slow. But in his examples I see 90 or 33 queries. I did see some people suggesting code with only one query, but no evidence that @monsterdream tried that.

That being said: First, you say that we don't understand the problem at hand, and later you only "suspect" what the problem was. Pot-kettle situation, much?

Let’s not delve into name-calling.

@monsterdream had a performance issue. He found that running find operations on a collection with less queries was actually slower than executing a lot of queries. When he reduced the number of queries, his response times went up because PHP/Laravel itself had to do more work, and was slower than just querying the database on demand.

It happens. Reducing the number of queries executed on a page isn’t the be all and end all to application performance. That’s why it irritates me when people post questions like, “How can I make my application faster?” as though there’s some magic bullet or, “Oh, just do this and your application will magically speed up.” It differs on a case-by-case basis.

Yes, you should strive to hit your database as least as possible, but in the OP’s case that simply moved the bottleneck to their application, and introduced another performance issue.

And @pixelstore, thanks for introducing me to the keyBy() method! ;)

You're right.

"Reducing the number of queries executed on a page isn’t the be all and end all to application performance. "

Especially if you're increasing the complexity of said queries at the same time ;)

The question wasn't "how can I make my application faster". The question was: "Why are operations on collections so slow?" But we're still not sure that it were, in fact, those operations that were slow.

But we're still not sure that it were, in fact, those operations that where slow

@monsterdream provided debug output, detailing the response times of calling methods on his Collection. What more proof do you need?

The point is to have a constant number of queries.

And of course a collection where lookup is slower than a db where lookup.

@martinbean No, he did NOT provide response times of calling methods on his collection. He only provided response times of requests with 33 queries and some collection methods, and with 90 queries.

Those queries were NOT the same. So this is nowhere near proof that the longer response times where (only) caused by those collectionmethods. The 33 queries were more complex so it's safe to assume they took more time on the database side.

Using @pixelstore 's suggestion, my code went from 21.2 sec runtime to 0.7 sec runtime. Amazing!

pixelstore was the only one to really understand the OPs question. I wanted to say thank you to both pixelstore and OP for posting about this. OP articulated it very well with a good diagram.

For me too, Collection where() was just super slow. 0.05 seconds per iteration, which quickly added up.

I used pixelstore's keyBy() code sample, along with a static variable to make sure I only ran the keyBy() once.

Literally an exponential increase and yes you can see its the u^2 problem rather than a u problem.

I signed in just to say thanks!

Use regular sql, let the database do the work. Or eloquent.

Ran into a similar issue, trying to compare two databases with very different structures containing partially overlapping data. Using foreach, $collection->each, $collection->where etc. were all very slow.

@pixelstore's solution is indeed amazing, both in its simplicity and performance improvements. What took hours now just takes minutes.

@pixelstore @jasleow me too, I just created an account just to say thank you 🤗