Hey guys, I have a search input in a page with a Table Records that return the results. But the results of the search are taking too long, because the database is growing every day.


This is the Filter that I'm using in the Aggregate of the search Screen.


CampoBusca is the local variable of the search input.

Devedor.Nome like "%" + CampoBusca + "%" or Devedor.CPF_CNPJ like "%" + CampoBusca + "%"
or CDA.Num_CDA like "%" + CampoBusca + "%"


I'm using the same place to search the name, the cpf/cnpj (ID used in Brazil) and CDA. The Aggregate Max Records is set to 35.


How can I optimize this filter to do the search more quickly? Thanks!!

Hi @Israel


try to add index to the aggreate

for  example you can see in the below image,

you can add all your attributes as seperate index, by doing  this your search speed will increase

I hope this helps you,


cheers

shree


Hi,

To speed up you search limit or eliminate the usage of like, and ensure you have indexes on the entity attributes on which you want to search.

Regards,

Daniel

Hi Israel,


Agree with Daniel,

just adding indexes probably won't do anything for you, since all the values you compare using like start with a wildcard.  (  I'm no dba, so correct me if I'm wrong, but index would only be hit for   ... like "abcd%"... not for like "%abcd%"  )


Regards,

Dorine

Srinivas K Singadi wrote:

Hi @Israel


try to add index to the aggreate

for  example you can see in the below image,

you can add all your attributes as seperate index, by doing  this your search speed will increase

I hope this helps you,


cheers

shree


I will try that in a week (when my boss return from vacation). I don't have privileges to change stuff in the database.

Thanks.


Daniël Kuhlmann wrote:

Hi,

To speed up you search limit or eliminate the usage of like, and ensure you have indexes on the entity attributes on which you want to search.

Regards,

Daniel


Hi, how can I eliminate the like in the Aggregate? Thanks for the answer.

Hi,

I am agree with Daniel and Dorine Boudry. By making indexes of the search field will not be the solution. You needs to Remove the wild char(% )from the suffixes.  Please check what they told it will reduce the search time.


Thank you,

Sudip

Israel wrote:

Hey guys, I have a search input in a page with a Table Records that return the results. But the results of the search are taking too long, because the database is growing every day.


This is the Filter that I'm using in the Aggregate of the search Screen.


CampoBusca is the local variable of the search input.

Devedor.Nome like "%" + CampoBusca + "%" or Devedor.CPF_CNPJ like "%" + CampoBusca + "%"
or CDA.Num_CDA like "%" + CampoBusca + "%"


I'm using the same place to search the name, the cpf/cnpj (ID used in Brazil) and CDA. The Aggregate Max Records is set to 35.


How can I optimize this filter to do the search more quickly? Thanks!!

hi, 

if the query involved many tables with millions or thousands records, here the guides:

1. do not show all in the screen until user types some filtering values

2. create one table for the search purpose and populate it using job at night


Israel wrote:

Hey guys, I have a search input in a page with a Table Records that return the results. But the results of the search are taking too long, because the database is growing every day.


This is the Filter that I'm using in the Aggregate of the search Screen.


CampoBusca is the local variable of the search input.

Devedor.Nome like "%" + CampoBusca + "%" or Devedor.CPF_CNPJ like "%" + CampoBusca + "%"
or CDA.Num_CDA like "%" + CampoBusca + "%"


I'm using the same place to search the name, the cpf/cnpj (ID used in Brazil) and CDA. The Aggregate Max Records is set to 35.


How can I optimize this filter to do the search more quickly? Thanks!!

Hi Israel,

First you need to apply index on your entity attribute and Second is you use Adv,SQL in place of aggregate because aggregate return all the attribute from entity(when aggregate fetching all attribute so its take's time )  and we need only  limited attribute which are showing on Screen.

Devedor.Nome like "%" + CampoBusca + "%" or Devedor.CPF_CNPJ like "%" + CampoBusca + "%"
or CDA.Num_CDA like "%" + CampoBusca + "%

Please apply filter on column which exactly you need data  from table not on every column  otherwise remove unnecessary  filter from condition .


Thanks

Nik

Nikhil Gondane wrote:

... and Second is you use Adv,SQL in place of aggregate because aggregate return all the attribute from entity(when aggregate fetching all attribute so its take's time )  and we need only  limited attribute which are showing on Screen...

This is not entirely true, aggregates are, if possible, optimized by the OS platform, only retrieving the columns that are needed.  The condition is that the platform can identify what the aggregate is going to be used for.

For example an aggregate in a screen, that is used as a source for a table records, will only return the columns that are actually displayed/used in the table records.


Dorine

@Nikhil: I totally agree with Dorine, you are stating something that's not true.

@Israel: do you have DBAs in your company? They might advise you how to optimize for full text searches. There are, depending on the database used, possibilities to index that outside the OutSystems Platform, but that may or may not be very manageable technically. Otherwise you're stuck: "LIKE '%' + searchTerm + '%'" can only do a full table scan, which is always very slow.

Daniël Kuhlmann wrote:

Hi,

To speed up you search limit or eliminate the usage of like, and ensure you have indexes on the entity attributes on which you want to search.

Regards,

Daniel

Hi guys.

How can I eliminate the usage of like?

Thanks!


Hi Israel,


What Daniel means (i think) is to simply not use it as often.  

Meaning that for every search option on a large table you want to offer to your users, think of ways to do it without a like, and only resorting to a like if there are no other options and they really need to search on that field.

So the easy way out as a developer, to just give them one search box, slap '%' in front and at the back and search with a like in every field, is the opposite of this, and is probably not going to be good enough, except for small tables or in a setting where waiting longer is acceptable.

This is not really an Outsystems topic, but a general sql database one, and a lot can be found in places like StackOverflow on indexes, query performance, query tuning,...  Besides sticking to some best practices like "don't use 'like' too often" and "make sure there is an index on the search fields", there's a whole world of tools, information, trade-offs,... but that's not really beginner stuff, and at some point details like what dbms you are using come into play.


So for your specific screen, you'll have to spend some time on figuring out what search options are really necessary, and what searches your users are comfortable with having to type the beginning or the whole value.  I find it very odd, for example, that a user would know only part of a cpf_cnpj number, so just ask your users to fill in that number from the beginning and don't use '%' in front of it. (and index it, of course)

And I don't know what CDA is, but it looks like a table you join with Devedor?  So if that table has only very limited amount of possible values for Num_CDA you might want to have a dropdown with these values for searching on num_cda instead of a wildcard search.  

And so on, this is something for you to work out with your users, explaining to them the performance cost of just having a wildcard on everything.


Good luck,

Dorine


Edit : Oh a general remark on 'like' : the problem is not with the '%' you add at the back in a like clause, that can still make use of indexes, but with the '%' you add at the front, as that will cause a table scan.