Level 15
You could try eloquent's chunking if your result set is too big, which could be the reason behind the delayed response.
Jun 8, 2019
27
Level 2
Optimizing Eloquent Query
I have an autocomplete text box that works well. I need to increase speed of the drop down list. Its too long - you can finish typing before it suggest anything. I have indexed the table and no improvements. So right now I just want to break down the problem into smaller pieces. Lets for now look at the eloquent query itself. Is this the most optimal I could have written it? I got this off the Internet so im not exactly sure why it was written this way it just works- I guess this is how I get to understand the query and optimizing it:
$query1 = $request->get('term','');
$countries=\DB::table('T_EMPLOYEE_SERVICE_AREAS');
if($request->type=='EmpService')
{
$countries->where('EmployeeService','LIKE',$query1.'%')
->groupBy('EmployeeService')
->get();
}
$countries=$countries->get();
$data=array();
foreach ($countries as $country) {
$data[]=array('EmployeeService'=>$country->EmployeeService);
}
if(count($data))
return $data;
else
return ['EmployeeService'=>''];
Level 20
Do you really need to fetch all the results in the DB? That can be quite heavy if you have a large table. If it's for autocomplete, something like the top 10 closest results is probably enough. You can limit the query with ->take() or ->limit(), e.g. $countries->take(10)->get();.
You are also looping through the results with foreach just to format it a little differently / fetch a single column? You could use ->pluck('EmployeeService') if you only need that one column; in fact, you could probably ->select() a single column if you don't need the rest (the more you load, the heavier your query will be).
Level 2
@NASH - I did a take(5) as suggested not much change. Do you mind showing where exactly would i put the select EmployeeService from statement, instead of the select *
Level 20
Show your query. Make sure ->take(5) comes before ->get(), otherwise it will first fetch everything in the db and then take 5 from the resulting collection.
Also, make sure to actually read the query builder docs, it has plenty of examples on how to use things like select.
Level 122
Prioritise removing having to iterate over the collection to create your reply.
You should be able to get what you want from the database without needing to do this.
Q. why do you need the grouping? Are you trying to get unique values?
Level 2
@SNAPEY - Yes the grouping is for unique values.
Level 2
I have made these changes and i dont see any improvements. in fact i think there might have been a miniscule deterioration in speed.
public function searchResponse(Request $request){
$query1 = $request->get('term','');
$countries=\DB::table('MY_TABLE');
if($request->type=='EmpService')
{
$countries->where('EmployeeService','LIKE',$query1.'%')
->groupBy('EmployeeService')
->take(5)
->get();
}
$countries=$countries->get();
return $countries;
}
Do you have anymore suggestions around optimizing this snippet? I want to maybe move into Caching and see if that will work.
Level 20
@SCREWTAPE_MK - Hmm...you should probably limit the query regardless so it doesn't fetch everything if type is not set. Here's a slightly modified version of your example, see if playing around with this helps.
$countries = \DB::table('MY_TABLE')
->select('EmployeeService') // does selecting a single column help?
->when($request->type == 'EmpService', function ($query) use ($query1) {
$query->where('EmployeeService','LIKE', $query1.'%')
->distinct(); // distinct instead of groupBy?
})
->take(5) // always limit the query
->get();
return $countries;
What do you mean by slow and how much data is in that table? Are you sure it's not a client side problem? How long does it take for a single request to finish in the browser dev tools network tab?
Level 122
install laravel debugbar to measure the actual query durations
Level 2
@NASH - When checking the network tab on chrome dev tools Waiting (TTFB) is about 600ms and initial connection averages 300ms - which makes it a total of 1s and that enough time to finish typing a word without a suggestion list.
You suggested modification doesn't return anything - only "Internal Error 500" in the console log. Not sure what could be wrong
Level 2
I ended up with:
$query1 = $request->get('term','');
$countries=\DB::table('MY_TABLE');
if($request->type=='EmpService')
{
$countries->select('EmployeeService')
->where('EmployeeService','LIKE',$query1.'%')
->distinct()
->take(5)
->get();
}
$countries=$countries->get();
return $countries;
}
Not much improvement still. What i will end up doing is caching. i don't want to move this to cloud server just yet.
Level 2
i also suspect my javascript event handler might be an issue - the auto complete is fast when you type just one character and wait - its extremely slow when u type several characters - its as though it waits for you to finish typing then send that as the term to search for - maybe it would work best to return a suggested list based on the first 2 characters typed
Level 122
Its probably sending multiple requests each is vying for database access
did you consider debugbar?
Level 2
Debugbar seem to require laravel 5.1 and abover - im using 5.0
Level 67
I'd look at debouncing/throttling on the javascript end for the keypresses/sending data.
Check your network tab in your console as you type the search term. Does it send an ajax request for each keypress? Like if you started typing "search", does it send 6 ajax requests? (one for "s", 2 for "se", 3 for "sea", etc)
That could be really slowing things down as each of those queries hits the database (milliseconds apart). Using a debounce function, you could basically make it only send one ajax request "after the person stops typing (or pauses typing) for 500ms" or something, which is a lot more efficient.
Level 2
If its of any significance the javascript handling the typing event is as follows:
$(document).on('focus','.autocomplete_txt',function(){
type = $(this).data('type');
if(type =='EmpService' )autoType='EmployeeService';
$(this).autocomplete({
minLength: 0,
source: function( request, response ) {
$.ajax({
url: SPANIBOX.search_ajax,
dataType: "json",
data: {
term : request.term,
type : type,
},
success: function(data) {
var array = $.map(data, function (item) {
return {
label: item[autoType],
value: item[autoType],
data : item
}
});
response(array)
}
});
},
select: function( event, ui ) {
var data = ui.item.data;
id_arr = $(this).attr('id');
id = id_arr.split("_");
elementId = id[id.length-1];
$('#EmpService_'+elementId).val(data.name);
}
})
});
Level 67
That doesn't really tell us anything.
Level 2
@CRONIX - I had not seen your update - let me do what u reccommended!.
Level 2
@CRONIX - when i type and then stop then it sends a single ajax request. I typed "Pai" and it sent:
http://spanibox.com/searchajax?term=pai&type=EmpService
when i typed slowly like 1st "P" and then "a" then it sent 2 requests:
http://spanibox.com/searchajax?term=p&type=EmpService
and
http://spanibox.com/searchajax?term=p&type=EmpService
respectively
Level 2
@CRONIX - I really think the problem here is if you typing it waits until you finish typing to send the Ajax request. Thats why it appears slow since it waits for the typing to stop. What it must do is start sending ajax as you type - maybe after 2 characters or so
Level 67
We don't know what autocomplete library you are using here. There are many out there.
$(this).autocomplete({
Have you looked at the docs for it? There are usually a lot of options you can set for stuff like this, like you have minLength: 0 set... I'm sure there are others?
Level 2
@CRONIX - if you look at the js i posted above i am using $(this).autocomplete({
and minLength: 0. i am now just gonna move on to caching and see how that improves speed
Level 122
You might be using $(this).autocomplete({ and minLength: 0
does not mean its correct, or that you can pass in properties this way. You state you have found a delay before the query is sent. This needs to be disabled. (not minLength related)
Level 2
@SNAPEY - Please elaborate what needs disabling. Its not so much that i have found a delay or its cause thereof...its really more like i observed a delay - please elaborate on what needs disabling. Is there anything you can pick up on the js above that suggest the i might be be using the autocomplete library wrongly or the min length option
Level 122
As @cronix said earlier. We could do with knowing what autocomplete library you are using
Please or to participate in this conversation.