Laravel Eloquent loads slow
there is a lot of similar topics but couldn't find a solution for me. This query loads very slow.
public function getAllBids()
{
return Bid::with('user', 'group')
->join('auct_lots', function ($join) {
$join->on('bids.lot_id', '=', 'auct_lots.lot_id')
->where('auct_lots.company', 'LIKE', '%' . request('brand') . '%')
->where('auct_lots.model_name', 'LIKE', '%' . request('models') . '%')
->where('auct_lots.grade_en', 'LIKE', '%' . request('carGrade') . '%')
->where('auct_lots.auction_name', 'LIKE', '%' . request('auctions') . '%')
->where('auct_lots.model_type_en', 'LIKE', '%' . request('chassis') . '%')
->where('auct_lots.bid', 'LIKE', '%' . request('lots') . '%')
->where('auct_lots.lot_date', 'LIKE', '%' . request('date') . '%');
})
->orderBy('auct_lots.' . request('order_column'), request('order_type'))
->paginate(30);
}
I think the problem is that auct_lots has more than 40,000 records... But I'm not sure how to refactor this code to work faster.
php laravel-5.6
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
add a comment |
there is a lot of similar topics but couldn't find a solution for me. This query loads very slow.
public function getAllBids()
{
return Bid::with('user', 'group')
->join('auct_lots', function ($join) {
$join->on('bids.lot_id', '=', 'auct_lots.lot_id')
->where('auct_lots.company', 'LIKE', '%' . request('brand') . '%')
->where('auct_lots.model_name', 'LIKE', '%' . request('models') . '%')
->where('auct_lots.grade_en', 'LIKE', '%' . request('carGrade') . '%')
->where('auct_lots.auction_name', 'LIKE', '%' . request('auctions') . '%')
->where('auct_lots.model_type_en', 'LIKE', '%' . request('chassis') . '%')
->where('auct_lots.bid', 'LIKE', '%' . request('lots') . '%')
->where('auct_lots.lot_date', 'LIKE', '%' . request('date') . '%');
})
->orderBy('auct_lots.' . request('order_column'), request('order_type'))
->paginate(30);
}
I think the problem is that auct_lots has more than 40,000 records... But I'm not sure how to refactor this code to work faster.
php laravel-5.6
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
2
Dont fetch 40000 at once, just get 50 or 100 records and use pagination.
– C2486
Nov 24 '18 at 2:39
@C2486 I'm not sure that I understand you...
– Dmitry Malys
Nov 24 '18 at 2:42
@DmitryMalys what he means is getting 40000 records will be taxing, which is why Laravel has a feature called Pagination, that allows you to do things like have a 'next' and 'previous' page for chunking all the results in manageable pieces. You can read about it here: laravel.com/docs/5.7/pagination
– Mav
Nov 24 '18 at 3:51
add a comment |
there is a lot of similar topics but couldn't find a solution for me. This query loads very slow.
public function getAllBids()
{
return Bid::with('user', 'group')
->join('auct_lots', function ($join) {
$join->on('bids.lot_id', '=', 'auct_lots.lot_id')
->where('auct_lots.company', 'LIKE', '%' . request('brand') . '%')
->where('auct_lots.model_name', 'LIKE', '%' . request('models') . '%')
->where('auct_lots.grade_en', 'LIKE', '%' . request('carGrade') . '%')
->where('auct_lots.auction_name', 'LIKE', '%' . request('auctions') . '%')
->where('auct_lots.model_type_en', 'LIKE', '%' . request('chassis') . '%')
->where('auct_lots.bid', 'LIKE', '%' . request('lots') . '%')
->where('auct_lots.lot_date', 'LIKE', '%' . request('date') . '%');
})
->orderBy('auct_lots.' . request('order_column'), request('order_type'))
->paginate(30);
}
I think the problem is that auct_lots has more than 40,000 records... But I'm not sure how to refactor this code to work faster.
php laravel-5.6
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
there is a lot of similar topics but couldn't find a solution for me. This query loads very slow.
public function getAllBids()
{
return Bid::with('user', 'group')
->join('auct_lots', function ($join) {
$join->on('bids.lot_id', '=', 'auct_lots.lot_id')
->where('auct_lots.company', 'LIKE', '%' . request('brand') . '%')
->where('auct_lots.model_name', 'LIKE', '%' . request('models') . '%')
->where('auct_lots.grade_en', 'LIKE', '%' . request('carGrade') . '%')
->where('auct_lots.auction_name', 'LIKE', '%' . request('auctions') . '%')
->where('auct_lots.model_type_en', 'LIKE', '%' . request('chassis') . '%')
->where('auct_lots.bid', 'LIKE', '%' . request('lots') . '%')
->where('auct_lots.lot_date', 'LIKE', '%' . request('date') . '%');
})
->orderBy('auct_lots.' . request('order_column'), request('order_type'))
->paginate(30);
}
I think the problem is that auct_lots has more than 40,000 records... But I'm not sure how to refactor this code to work faster.
php laravel-5.6
php laravel-5.6
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
asked Nov 24 '18 at 2:07
Dmitry MalysDmitry Malys
395819
395819
2
Dont fetch 40000 at once, just get 50 or 100 records and use pagination.
– C2486
Nov 24 '18 at 2:39
@C2486 I'm not sure that I understand you...
– Dmitry Malys
Nov 24 '18 at 2:42
@DmitryMalys what he means is getting 40000 records will be taxing, which is why Laravel has a feature called Pagination, that allows you to do things like have a 'next' and 'previous' page for chunking all the results in manageable pieces. You can read about it here: laravel.com/docs/5.7/pagination
– Mav
Nov 24 '18 at 3:51
add a comment |
2
Dont fetch 40000 at once, just get 50 or 100 records and use pagination.
– C2486
Nov 24 '18 at 2:39
@C2486 I'm not sure that I understand you...
– Dmitry Malys
Nov 24 '18 at 2:42
@DmitryMalys what he means is getting 40000 records will be taxing, which is why Laravel has a feature called Pagination, that allows you to do things like have a 'next' and 'previous' page for chunking all the results in manageable pieces. You can read about it here: laravel.com/docs/5.7/pagination
– Mav
Nov 24 '18 at 3:51
2
2
Dont fetch 40000 at once, just get 50 or 100 records and use pagination.
– C2486
Nov 24 '18 at 2:39
Dont fetch 40000 at once, just get 50 or 100 records and use pagination.
– C2486
Nov 24 '18 at 2:39
@C2486 I'm not sure that I understand you...
– Dmitry Malys
Nov 24 '18 at 2:42
@C2486 I'm not sure that I understand you...
– Dmitry Malys
Nov 24 '18 at 2:42
@DmitryMalys what he means is getting 40000 records will be taxing, which is why Laravel has a feature called Pagination, that allows you to do things like have a 'next' and 'previous' page for chunking all the results in manageable pieces. You can read about it here: laravel.com/docs/5.7/pagination
– Mav
Nov 24 '18 at 3:51
@DmitryMalys what he means is getting 40000 records will be taxing, which is why Laravel has a feature called Pagination, that allows you to do things like have a 'next' and 'previous' page for chunking all the results in manageable pieces. You can read about it here: laravel.com/docs/5.7/pagination
– Mav
Nov 24 '18 at 3:51
add a comment |
1 Answer
1
active
oldest
votes
When you are having large amount of data, it is better to use server side caching. This will improve performance very much faster.
Step 1: Create a Trait and write logic for store values in caching.
Ex:
trait Student{
public function storeStudentDataInCache(){
$students = Cache::rememberForever('studentList', function () {
return Student::
with(['class', 'exams'])
->where('is_published', 1)->orderBy('display_sequence', 'ASC')->get();
});
return $students;
}
}
Now in your controller call this method, this will return collection of data. So you do not need run sql queries all the time to get data. This method will run query only after one time. After that data will be stored in caching so it will get data's collection from caching text file.
Note: Whenever you update your data,please do not forget to delete this cache.
And you can also apply where condition for laravel collection or you can use filter method of collection to implement more filter logic.
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454607%2flaravel-eloquent-loads-slow%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
When you are having large amount of data, it is better to use server side caching. This will improve performance very much faster.
Step 1: Create a Trait and write logic for store values in caching.
Ex:
trait Student{
public function storeStudentDataInCache(){
$students = Cache::rememberForever('studentList', function () {
return Student::
with(['class', 'exams'])
->where('is_published', 1)->orderBy('display_sequence', 'ASC')->get();
});
return $students;
}
}
Now in your controller call this method, this will return collection of data. So you do not need run sql queries all the time to get data. This method will run query only after one time. After that data will be stored in caching so it will get data's collection from caching text file.
Note: Whenever you update your data,please do not forget to delete this cache.
And you can also apply where condition for laravel collection or you can use filter method of collection to implement more filter logic.
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
add a comment |
When you are having large amount of data, it is better to use server side caching. This will improve performance very much faster.
Step 1: Create a Trait and write logic for store values in caching.
Ex:
trait Student{
public function storeStudentDataInCache(){
$students = Cache::rememberForever('studentList', function () {
return Student::
with(['class', 'exams'])
->where('is_published', 1)->orderBy('display_sequence', 'ASC')->get();
});
return $students;
}
}
Now in your controller call this method, this will return collection of data. So you do not need run sql queries all the time to get data. This method will run query only after one time. After that data will be stored in caching so it will get data's collection from caching text file.
Note: Whenever you update your data,please do not forget to delete this cache.
And you can also apply where condition for laravel collection or you can use filter method of collection to implement more filter logic.
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
add a comment |
When you are having large amount of data, it is better to use server side caching. This will improve performance very much faster.
Step 1: Create a Trait and write logic for store values in caching.
Ex:
trait Student{
public function storeStudentDataInCache(){
$students = Cache::rememberForever('studentList', function () {
return Student::
with(['class', 'exams'])
->where('is_published', 1)->orderBy('display_sequence', 'ASC')->get();
});
return $students;
}
}
Now in your controller call this method, this will return collection of data. So you do not need run sql queries all the time to get data. This method will run query only after one time. After that data will be stored in caching so it will get data's collection from caching text file.
Note: Whenever you update your data,please do not forget to delete this cache.
And you can also apply where condition for laravel collection or you can use filter method of collection to implement more filter logic.
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
When you are having large amount of data, it is better to use server side caching. This will improve performance very much faster.
Step 1: Create a Trait and write logic for store values in caching.
Ex:
trait Student{
public function storeStudentDataInCache(){
$students = Cache::rememberForever('studentList', function () {
return Student::
with(['class', 'exams'])
->where('is_published', 1)->orderBy('display_sequence', 'ASC')->get();
});
return $students;
}
}
Now in your controller call this method, this will return collection of data. So you do not need run sql queries all the time to get data. This method will run query only after one time. After that data will be stored in caching so it will get data's collection from caching text file.
Note: Whenever you update your data,please do not forget to delete this cache.
And you can also apply where condition for laravel collection or you can use filter method of collection to implement more filter logic.
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
edited Nov 26 '18 at 14:24
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
answered Nov 26 '18 at 14:17
Yash JoshiYash Joshi
362
362
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454607%2flaravel-eloquent-loads-slow%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Dont fetch 40000 at once, just get 50 or 100 records and use pagination.
– C2486
Nov 24 '18 at 2:39
@C2486 I'm not sure that I understand you...
– Dmitry Malys
Nov 24 '18 at 2:42
@DmitryMalys what he means is getting 40000 records will be taxing, which is why Laravel has a feature called Pagination, that allows you to do things like have a 'next' and 'previous' page for chunking all the results in manageable pieces. You can read about it here: laravel.com/docs/5.7/pagination
– Mav
Nov 24 '18 at 3:51