Platform
Learn
Community
Support
Partner Center
Sign up
or
Log in
logout
Account Settings
Profile
messages
Messages
logout
Logout
Platform
Home
Downloads
IPP
Licensing
Project Sizing
Learn
Training
Documentation
Evaluation Guide
What's New
Community
Home
Forums
Forge
Ideas
Members
Support
Partner Guide
Resources Library
Opportunities
Account Management
Sign up
or
Log in
Home
Forums
Forge
Ideas
Members
Dear ,
What would improve your OutSystems Community experience? Let us know by taking this 2-minute survey.
Pick up the survey
Community
›
Forums
›
Technology & Integration
Autocomplete with massive dataset
Community
›
Forums
›
Technology & Integration
Autocomplete with massive dataset
New Post
New Post
Joop Stringer
Posted on 2011-10-11
Joop Stringer
Rank: #105
Posted on 2011-10-11
Hi all,
Anybody know how to go about using the autocomplete on a massive dataset ?
Let's say 100.000 records ...
I've seen this
http://stackoverflow.com/questions/678987/how-to-implement-autocomplete-on-a-massive-dataset
but don't know how to create that TRIE (no misspelling...)
João Rosado
Posted on 2011-10-11
João Rosado
Rank: #13
Posted on 2011-10-11
Solution
Hi Joop,
Looked that info about the Ties ..and it seems a bit over-engineering.
First how many results do you want to display at a given time? A good autocomplete should only display 5-7 sugestions for a given input. (yes I seen autocompletes with drop downs of 5000+ results ..noone will ever find anything there)
If sugestions are good enough how many characters would the user write?
In a given period of time how many different inputs are sent to server before users get their full result?
If the inputs->results pairs are not a huge size I would consider doing it on a very simple aproach ..without going into Ties or complex algorithms.
And if database performance is an issue, use cache in the Actions to reduce the load.
Regards
João Rosado
Solution
Ricardo Araújo
Posted on 2011-10-11
Ricardo Araújo
Rank: #87
Posted on 2011-10-11
Solution
Hi Joop,
I'm beliving you already try this one
Index(TextToSearchWhatYouWant,"["+Entity.Attribute+"]")>-1
You can use this where clause in a simple query to perform a better performance in the search with text attributes. After all, 100k its not 1M ;)
Cheers,
RNA
Solution
António Chinita
Posted on 2011-10-11
António Chinita
Rank: #15
Posted on 2011-10-11
Solution
Might be difficult to implement a Trie using Outsystems only, because it doesn't allow recursive data structures.
This is just a guess, but what about implementing a few rules to reduce those 100k records?
Eg.
Limit query maximum rows.
Only query for the auto complete when the search string has more than N characters, where N is whatever you think is right.
Solution
Hermínio Mira
Posted on 2011-10-12
Hermínio Mira
Rank: #85
Posted on 2011-10-12
Solution
Hi Joop,
"Only query for the auto complete when the search string has more than N characters, where N is whatever you think is right."
I would go along with António in this one, its the simpler to implement and you avoid the really heavy first queries where you search for anything with that first letter the user wrote.
Cheers,
Hermínio Mira
Solution
Ricardo Araújo
Posted on 2011-10-12
Ricardo Araújo
Rank: #87
Posted on 2011-10-12
Solution
Joop,
merge both solutions, António and mine, don't use LIKE in the query as you probably know ;)
Cheers,
Solution
Miguel Ventura
Posted on 2011-10-12
Miguel Ventura
Rank: #72
Posted on 2011-10-12
Solution
Usually tries are used when the dataset is loaded into memory of your application or some kind of data structure where you have the full dataset. This isn't usually the case with web applications that feed from the database. In this case, having a good structure for a fast lookup is the database's responsibility, not the programmers' business logic.
Implementing an autocomplete over a massive dataset which is stored in a database may actually be as simple as performing the infamous LIKE query with a limited number of results and no "order by" clause. If you limit your query to 11 results and only pass 10 to the autocomplete, you may use the existence of the 11th record to signal a "there's more results" to be shown somewhere, tipping the user to refine his search query.
I'd really advise you to try this simpler solution before digging into more complex data structures and algorithms that may not actually be required to solve your particular problem.
Cheers,
Miguel
Solution
Joop Stringer
Posted on 2011-10-12
Joop Stringer
Rank: #105
Posted on 2011-10-12
Solution
@ricardo : please explain where should that be used ?
@Hermino: did that already, 3 chars before search
@Joao: the pitty is it needs to search for addresses in a major city of the netherlands :-), and it uses a like %xxx%
Solution
António Chinita
Posted on 2011-10-13
António Chinita
Rank: #15
Posted on 2011-10-13
Solution
Joop,
You don't need to use the "LIKE %InputParameter%".
Replace the "Entity.Address LIKE %InputParameter%" condition with what Ricardo sugested: Index(InputParameter,"["+Entity.Address+"]")>-1
Best regards.
Solution
Joop Stringer
Posted on 2011-10-13
Joop Stringer
Rank: #105
Posted on 2011-10-13
Solution
Hey Guys,
Tried this Index stuff, but it
doesn't work
.
Please can you provide a sample OML to see this working !
Solution
António Chinita
Posted on 2011-10-13
António Chinita
Rank: #15
Posted on 2011-10-13
Solution
Here's a
screenshot
Joop. Hope it helps.
Solution
Joop Stringer
Posted on 2011-10-13
Joop Stringer
Rank: #105
Posted on 2011-10-13
Solution
Duh ... the parameters are the aother way around :-)
Index(
"["+Entity.Attribute+"]" ,
TextToSearchWhatYouWant )>-1
Solution
Carlos Rocha
Posted on 2011-10-13
Carlos Rocha
Rank: #138
Posted on 2011-10-13
Solution
Hi Joop,
Check this image,
this two conditions have the same value, but index is actualy a litle bit faster than like.
Regards,
Carlos Rocha
Solution
Joop Stringer
Posted on 2011-10-13
Joop Stringer
Rank: #105
Posted on 2011-10-13
Solution
Solution
Quick Reply
attachment
Choose File
No file chosen
Submit
Login to reply
New Post
Available Forums
Technology & Integration
News and Announcements
Forge Discussions
Meta
Community Quick Guides
Usability
Installation Troubleshooting
Personal Environment Troubleshooting
Forum Notifications
Email Digest Settings
Loading...