Data based on optional conditions

[rindia]

Hi,

A requirement is like this:

2 tables - employee and condition. In condition table, there can be 0 or n number of records.

Based on condition table, data from employee has to be fetched depending on attribute operator and value.

An attribute may occur more than once also (example EID).

With dynamic SQL, it is easy to evaluate but it effects the performance as my table size is very huge.

How to achieve this efficiently?

Copying

Regards

Raj

[lucas_oliveira]

Hi Raj,

Just throwing ideas here: is the CONDITION table changed often? If not, one option would be to set a trigger on CONDITION so whenever a row is inserted/updated/deleted it generates the complete where clause and ships it to another table (CONDITION_AGGR).

Later on you can use that complete filter from with APPLY_FILTER on the employee table. Downside here is that the search term on apply_filter can not be greater than 5000 chars.

If that's not a problem then it should work fine.

BRs,

Lucas de Oliveira

[rindia]

Hi Lucas,

The CONDITION table often changes on various parameters. In the example, i shown only three attributes but there are many attributes and for each attribute there can be many operators like (BETWEEN, NOT IN, etc) for each of the employee.

I'm looking to construct in CV only.

To handle the optional conditions I'm using flag. If the condition is present then flag is false else flag is set to to true. Similarly for each operator, I'm using a flag.

Ex: LOCATION IN (SELECT DISTINCT LOCATION FROM .....) OR 1 = FLAG_LOCATION

If there is condition on Location then FLAG_LOCATION is 0 else 1.

As of now I'm coding like this and will post the performance results after I'm done with testing.

Better solutions are welcome

Regards

Raj

[lbreddemann]

Why are you doing this?

With SQL you could formulate the query straight away.

If your users should not use SQL, provide them with a query tool that in turn builds the SQL for them.

That's the efficient way to build this.

SQL and the code that processes SQL is not prepared or optimised to handle arbitrary meta-query constructs. The performance of something like this, will very likely always be worse than the respective statement in pure SQL.

Besides this, you're also introducing another layer of dependency that no tool supports you with (what if you change column names? How do you managed column type mismatches between table and conditions?).

Honestly, I don't think this solution is a good idea.

my 2 cts.

[rindia]

Hi Lars,

The front end is web based application built on top of HANA which uses the data from the Calculation View which fetches the data based on condition table.

The Condition table data itself is populated on some logic by front end application and all the mismatch, etc are handled there itself.

Hence i need to build a straight away CV to fetch data based on condition table.

Why are you doing this?
With SQL you could formulate the query straight away.

I planned to use the flag to check optional conditions.

Regards

Raj

[lbreddemann]

Ok, so your front end queries data, that should be filtered by what the front end put into the database before?

And any type discrepancies should be handled by the front end, too.

Honestly, in this scenario it would be more efficient to have the front end either construct proper SQL statements or SQL views, if reuse is important.

Doing it the way you outlined is basically implementing a query interface once again. This makes the solution only more complex and leaves the database with less options for optimisation.