I want auto search option in textbox and data is fetching from database. I have thousands of data in my database table (almost 8-10000 rows). I know how to achieve this but as I am fetching thousands of data, it will take a lot of time to fetch. How to achieve this without getting slow down? Should I follow any other methodology to achieve this apart from simple fetching methods? I am using Oracle SQL Developer for database.
Asked
Active
Viewed 411 times
0
-
Are you already using `DISTINCT` ? Or you can simply `LIMIT` the number of the rows returned – fantaghirocco Aug 07 '15 at 13:51
-
Using auto search in a text box to query 10,000 rows doesn't seem like a good idea. Does that mean that as soon as the user types a letter e.g. "a" you immediately display all rows that have an "a" in the column you are matching on, or what? – Tony Andrews Aug 07 '15 at 15:06
2 Answers
0
Besides the obvious solutions involving indexes and caching, if this is web technology and depending on your tool you can sometimes set a minimum length before the server call is made. Here is a jquery UI example: https://api.jqueryui.com/autocomplete/#option-minLength
"The minimum number of characters a user must type before a search is performed. Zero is useful for local data with just a few items, but a higher value should be used when a single character search could match a few thousand items."
Steve Greene
- 12,029
- 1
- 33
- 54
0
It depends on your web interface, but you can use two tecniques:
- Paginate your data: if your requirements are to accept empty values and to show all the results load them in block of a predefined size. goggle for example paginates search results. On Oracle pagination is made using the rownum special variable (see this response). Beware: you must first issue a query with a order by and then enclose it in a new one that use rownum. Other databases that use the limit keyword behave in a different way. If you apply the pagination techique to a drop down you end up with an infinite scroll (see this response for example)
- Limit you data imposing some filter that limits the number of rows returned; your search display some results only after the user typed at least n chars in the field
You can combine 1 & 2, but unless you find an existing web component (a jquery one for example) it may be a difficult task if you don't have a Javascript knowledge.
