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?
4
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?
1
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!