Something is wrong with the performance on big data

[RezaT1994]

Describe the bug
When I query the first 5000 rows of a table, it takes about 10 seconds to respond

Expected behavior
It should be a lot faster. as it was in orator

Screenshots or code snippets

class Product(SoftDeletesMixin, Model):
	pass

products = Product.where('is_active', '1').limit(5000).get()
print(products[0].id) # 1

here is the query log:

Running query SELECT * FROM `products` WHERE `products`.`is_active` = %s AND `products`.`deleted_at` IS NULL LIMIT 5000, ['1']. Executed in 0.20ms

Desktop:

• OS: [Windows]
• Version [11]

What database are you using?

• Type: [MySQ]
• Version [8.0.26]
• Masonite ORM [v1.0.65]

[josephmancuso]

You really should not be loading 5000 rows into memory like that. It is much better to use chunking. I'm not sure how much faster Orator was from Masonite ORM but the slowness comes from hydrating 5000 models in memory and not the query. Not really anything we can do unless you improve your query.

Here is an example on the same query above but chunking through the results:

products_chunk = Product.where('is_active', '1').limit(5000).chunk(200)
for product in products_chunk:
    print(product.id)

Chunking uses a generator which is much faster and uses much less memory.

[RezaT1994]

Now when I use eager loading, It's like not working with chunk. It's working when using get

Product.with_trashed().with_('brand').limit(5000).chunk(200)

[josephmancuso]

Fixed in #553