Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have a database having around 15 tables.Currently I am working on a report generation page, which is having 16 criteria.The values are distributed among multiple tables.User can select any number of search criteria. The problem is, if the user is entering 2-3 values, it is better to filter first by certain value and then apply the next level of filtration on remaining values and this order of filtration depends on the user input always. That means, I have to check all the possible ways of input permutation and give execution path for each, which will be horrible.Is there any way by which I can reduce the execution paths. I am not able to come up with an efficient algorithm.

Suppose I have 3 parameters: name, skill, programExp

If name is given, then I should filter based on name first. If skill is given, then I should filter using skill, programExp and then name.

share|improve this question
1  
Can you give a (reduced) example of the problem you face? It is not entirely clear.<br/> Do you mean that if you have search criteria A, B and C, then if all three are given, you should filter on A first, but if it is A+B or A+C, the you should filter on A last? –  Bart van Ingen Schenau Mar 25 '13 at 8:41
1  
Your question is not very clear, you should give some more details and examples. For a maybe similar problem I have used Apache Solr and its facet queries successfully. But if this would work for you is difficult to say, since it depends on the search criteria and what results you need. –  thorsten müller Mar 25 '13 at 9:10
1  
I have added one small example –  Renjith Mar 25 '13 at 9:59
    
Is doing this with database queries mandatory for some reason? I would really put these values in a Solr Document, flatten the data structure that way and then query with Solr. Not only for speed, but the resulting code would be far less complex. Your example doesn't help very much. If these would be in one table, it would be a very simple query and even with multiple tables optimizing the order of queries would mostly become interesting if you have very large tables. –  thorsten müller Mar 25 '13 at 10:08
    
Is it safe to assume that the results of your query are, essentially, several of your tables joined and the rows winnowed down using a WHERE clause? –  Blrfl Mar 25 '13 at 12:39
add comment

3 Answers

up vote 2 down vote accepted

I'm going to go on the assumption that the query results are the results of a JOIN filtered using a WHERE clause.

I have to check all the possible ways of input permutation and give execution path for each, which will be horrible.

That will be horrible. It sounds like you're trying to do something any decent database already does for you.

Good databases have a query optimizer whose job it is to examine incoming queries and come up with an execution plan for carrying it out. Plans are constructed to consume the fewest resources (CPU, memory, access to storage) while answering queries quickly and correctly. The major factors that go into these decisions are the tables used, JOIN conditions, WHERE clauses, available indexes and table statistics. For example, if the optimizer sees that one of your WHERE clauses can cut the result set of a JOIN from a billion rows to, say, a thousand, that condition will be applied earlier rather than later so fewer intermediate results have to be handled.

What this means for you is that you don't have to worry about what order you apply the conditions. Stack them up in a WHERE clause and the optimizer will take care of the rest. Once you've made it work and work right, you can worry about making it work fast if you've determined that your queries are a bottleneck. The cure will almost universally not be trying to outsmart the optimizer or handling the data yourself, it will be tuning your database so the optimizer has what it needs to do a better job. Databases with optimizers often have an EXPLAIN or SHOW PLAN feature that will let you see what the optimizer comes up with for a given query, and the parts that cause performance problems will stick out like a sore thumb.

Bottom line: build a query based on the conditions in force and let the database do its job.

share|improve this answer
add comment

You should not be coding an “Sql execution Path\Plan decision tree”

You need to optimize your Query (singular) to perform as best it can with NO criteria. Then add a Dynamic where clause encompassing all the selected search criteria.

share|improve this answer
add comment

As already suggested in a comment, a search index is a better solution that database queries. I can second the Thorsten's suggestion of apache Solr. You could probably get a working index up in an hour.

share|improve this answer
    
...and then must develop a way to keep the index in sync with the database. –  Blrfl Mar 25 '13 at 12:34
    
@Blrfl - not a problem, Solr already has mechanisms for this –  Qwerky Mar 25 '13 at 12:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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