Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

This is in the scope of a web application. I have a database which has a few nested relations. There is a feature which depicts the history of a large chain of relations. It is essentially a data analysis feature. The issue is that in order to search, a large object graph must be loaded - the loading time for this object graph is not quick enough to be viable. The problem is that without loading the whole graph it makes searching from a single string nearly impossible. In order to search, explicit fields must be specified and the search data supplied.

Is there a design pattern for exposing the data in a way which facilitates a single string search instead of having to explicitly define parameters?

share|improve this question

marked as duplicate by JeffO, gnat, GlenH7, Kilian Foth, World Engineer Nov 12 '13 at 23:15

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

First off, a single string search as part of a web application (or any other system in which the possibility of a hostile user exists) is never a good idea. There are all sorts of little ways that a hostile user can manipulate query strings--even ones that someone is trying hard to sanitize properly--into creating a special query that hacks your database. This whole class of attacks is known as SQL Injection, and the only truly safe way to handle it is with parameterized queries. – Mason Wheeler Nov 12 '13 at 0:50
Second, it's a bit confusing what you're asking for. Can you edit your question to clarify a little about your large object graph and what it has to do with setting up searches? Right now I'm having a lot of trouble visualizing the problem you're trying to solve. – Mason Wheeler Nov 12 '13 at 0:51
@MasonWheeler - There is no risk of a SQL Injection here, that is what modern frameworks are for. As for the object graphs.. There are a list of objects. Each object has several strings defining it. Each object also has a child object. Each child object has several strings defining it and set of grandchildren objects. Each grandchild object has a set of strings defining it and a great-grandchild object. I am trying to efficiently search each of those strings for a search term. – Travis J Nov 12 '13 at 0:55
+1 I think this is a very practical and potentially useful question, as it exposes what can happen when an unexpected feature requirement (like full text search) is not trivial to implement with a given existing large relational database system. – BrianDHall Nov 12 '13 at 3:44
up vote 1 down vote accepted

Why can't you just search the appropriate tables one at a time:

term = "%" + term + "%";

"select * from some_table where first_field like :term or
                                second_field like :term"

Then load only the parts of the object graph that actually matched your search terms?

share|improve this answer
Ever seen what LIKE can do to a large column on a few million rows of data? – Wyatt Barnett Nov 12 '13 at 19:09

I faced somehow the same problem and I came to the conclusion that I was like trying to use the wrong tool to solve it.

Keep in mind that RDBMS have been designed for efficient storage purpose where Search Engines have been designed to search!

Using your DB might not be the good way to go here.
I know about FullText Search, but they have A LOT of limitations compared to what a Search Engine can do:

  • Tied to the DB schema, tied to structured data
  • Only character-based columns are searchable
  • Expensive if you need to scale up
  • Every search hits the DB (performances, bandwidth, ...)

Search Engines:

  • Not tied to any schema/structure
  • Every kind of data is indexable / searchable
  • Easy to scale up
  • Facets and other pretty out-of-the-box things

There are a lot of Search Engines, they have all their pros & cons (there are tons of articles and SO questions that will help you out to choose one depending on your existing infrastructure, your programming langage or even your existing DB).

One of them is ElasticSearch. It comes with an an easy way to automatically index data coming from any kind of source (including RDBMS like SQLServer), using what they call a River. ES comes with a Rest API and use its own NoSQL store.

Consider having a look this "ES - Beyond full-text search" presentation that just have been published!

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.