Question Re: SQL Query Where Clause condition Magic in Technology Q&A

SQL Query Where Clause condition Magic

Former Member — Thu, 20 Jul 2017 19:52:09 GMT

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.

Re: SQL Query Where Clause condition Magic

lbreddemann — Thu, 20 Jul 2017 23:54:57 GMT

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.