0

I have a simple, pasted below, statement called against an Oracle database. This result set contains names of businesses but it has 24,000 results and these are displayed in a drop down list.

I am looking for ideas on ways to reduce the result set to speed up the data returned to the user interface, maybe something like Google's search or a completely different idea. I am open to whatever thoughts and any direction is welcome.

SELECT BusinessName FROM MyTable ORDER BY BusinessName;

Idea: 

SELECT BusinessName FROM MyTable WHERE BusinessName LIKE "A%;

I'm know all about how LIKE clauses are not wise to use but like I said this is a LARGE result set. Maybe something along the lines of a BINARY search?

Brian Evans
  • 215
  • 3
  • 17
  • 2
    There's nothing wrong with LIKE clauses as long as there isn't a leading percent. Is the issue that the drop down takes too long to load, or that 24,000 names are too long to look through, or both? This sounds like a user interface issue not a database issue. i.e. load all of the records into some local smart control that does google search type functionality. Is this web or thick client? – Nick.McDermaid Nov 14 '13 at 02:26
  • If you have business categories, or locations, or something like that, then related selects might be better. – Dan Bracuk Nov 14 '13 at 02:26

2 Answers2

1

The last query can perform horribly. String comparisons inside the database can be very slow, and depending on the number of "hits" it can be a huge drag on performance. If that doesn't concern you that's fine. This is especially true if the Company data isn't normalized into it's own db table.

As long as the user knows the company he's looking up, then I would identify an existing JavaScript component in some popular JavaScript library that provides a search text field with a dynamic dropdown that shows matching results would be an effective mechanism. But you might want to use '%A%', if they might look for part of a name. For example, If I'm looking for IBM Rational, LLC. do I want it to show up in results when I search for "Rational"?

Either way, watch your performance and if it makes sense cache that data in the company look up service that sits on the server in front of the DB. Also, make sure you don't respond to every keystroke, but have a timeout 500ms or so, to allow the user to type in multiple chars before going to the server and searching. Also, I would NOT recommend bringing all of the company names to the client. We're always looking to reduce the size and frequency of traversals to the server from the browser page. Waiting for 24k company names to come down to the client when the form loads (or even behind the scenes) when shorter quicker very specific queries will perform sufficiently well seems more efficient to me. Again, test it and identify the performance characteristics that fit your use case best.

These are techniques I've used on projects with large data, like searching for a user from a base of 100,000+ users. Our code was a custom Dojo widget (dijit), I 'm not seeing how to do it directly with the dijit code, but jQuery UI provides the autocomplete widget.

Also use limit on this query with a text field so that the drop down only provides a subset of all the matches, forcing the user to further refine the query.

mangr3n
  • 199
  • 1
  • 7
  • I'll try this. The kind of advice I was looking for. I will pull all results from the database asynchronously and then search through those with the autocomplete widget. Thanks. – Brian Evans Nov 14 '13 at 02:51
  • Love to hear the results and any further performance tradeoff decisions you encounter. I've seen this in a few different areas, and had to solve it with different mechanisms. For example, the value "picker" looks completely different when the user doesn't know what he's looking for. – mangr3n Nov 14 '13 at 02:56
-3
SELECT BusinessName FROM MyTable ORDER BY BusinessName LIMIT 10
superwese
  • 61
  • 5