SQL Query Where Clause condition Magic

[Former Member]

Hi All,

We had performance issue in HANA SP12 1.22 where this query that had where clasue like CalMonth le 12 was taking 140 seconds. Soon we changed query where clasue to (CalMonth ge 1 and Calmonth Le 12 ), query responded in 2.8 seconds. I am attaching two screenshot highlighting where clasue and performance improvement.

I would like to take your insight why this made such huge performance improvement.

[lbreddemann]

As with most "Why is my SQL fast/slow?" questions your first step of analysis should be to review what's happening on DB level.

Capture the SQL and run an EXPLAIN PLAN against both variants. That should provide a first idea on where the difference is in the execution of both statements.

Also, since you're selecting from a (complex) view, you might want to review the view design for possible bottlenecks. E.g. if there is a large join before the months-restriction gets applied that could be a cause.