[Db2 LUW] Query with optional parameters?

Greg Palgrave

[Db2 LUW] Query with optional parameters?

Hi List,

Just calling on your collective wisdom here. I've noticed a query on one of our Prod systems which is taking up around 30-40% of the total execution time during the online day. It runs 3-500 times per hour and it seems to be one that comes from a web application of some sort. It sounds worse than it is, but as we've tuned other stuff, it's now become more obvious.

I've done explains, and had a play around looking at indexing, and there is an index which helps slightly (maybe 5% improvement in the cost estimate), but I think the chief issue is that it appears that the query has two optional parameters which are only used in the WHERE clause.

e.g.

WHERE ( ? LIKE '' OR ITEM_KEY >= ? )
AND ( ? = 0 OR ITEM_NO = ? )
ORDER BY ITEM_KEY ASC, ITEM_NO ASC
FETCH FIRST 100 ROWS ONLY

ITEM_KEY is CHAR(20) NOT NULL

ITEM_NO is INTEGER NOT NULL

There is no ITEM_NO=0, and ITEM_KEY >= '' would just return the first 100 rows.

Traditionally, we could look at doing a dynamic query, or having logic in the code to decide which version of the query to run depending on which fields had values, but coding for performance is old-fashioned (aka too hard)  so I'm not optimistic of getting code logic rewritten to deal with it.

But, just looking at the WHERE clause, and the data, it seems to me it could be simplified to just:

WHERE ( ITEM_KEY >= ?  OR  ITEM_NO = ? )

 

Have I missed something in the logic here? It's making my head hurt :)  

I'll still have to run it past the Dev team, but I don't want to suggest the change if I've missed something obvious.

Testing the query explain with that change to the WHERE clause reduces the estimated cost by 20%, which I will call a win if we can do it.

 

Cheers

Greg

 

 

 

 

Suresh Sane

[Db2 LUW] Query with optional parameters?
(in response to Greg Palgrave)
Tried REOPT?

Thx
Suresh

________________________________
From: Greg Palgrave <[login to unmask email]>
Sent: Thursday, July 16, 2020 1:43 AM
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - [Db2 LUW] Query with optional parameters?


Hi List,

Just calling on your collective wisdom here. I've noticed a query on one of our Prod systems which is taking up around 30-40% of the total execution time during the online day. It runs 3-500 times per hour and it seems to be one that comes from a web application of some sort. It sounds worse than it is, but as we've tuned other stuff, it's now become more obvious.

I've done explains, and had a play around looking at indexing, and there is an index which helps slightly (maybe 5% improvement in the cost estimate), but I think the chief issue is that it appears that the query has two optional parameters which are only used in the WHERE clause.

e.g.

WHERE ( ? LIKE '' OR ITEM_KEY >= ? )
AND ( ? = 0 OR ITEM_NO = ? )
ORDER BY ITEM_KEY ASC, ITEM_NO ASC
FETCH FIRST 100 ROWS ONLY

ITEM_KEY is CHAR(20) NOT NULL

ITEM_NO is INTEGER NOT NULL

There is no ITEM_NO=0, and ITEM_KEY >= '' would just return the first 100 rows.

Traditionally, we could look at doing a dynamic query, or having logic in the code to decide which version of the query to run depending on which fields had values, but coding for performance is old-fashioned (aka too hard) so I'm not optimistic of getting code logic rewritten to deal with it.

But, just looking at the WHERE clause, and the data, it seems to me it could be simplified to just:

WHERE ( ITEM_KEY >= ? OR ITEM_NO = ? )



Have I missed something in the logic here? It's making my head hurt :)

I'll still have to run it past the Dev team, but I don't want to suggest the change if I've missed something obvious.

Testing the query explain with that change to the WHERE clause reduces the estimated cost by 20%, which I will call a win if we can do it.



Cheers

Greg









-----End Original Message-----

Joe Geller

RE: [Db2 LUW] Query with optional parameters?
(in response to Suresh Sane)

Unfortunately, your format :

WHERE ( ITEM_KEY >= ?  OR  ITEM_NO = ? )

is not equivalent.  If both optional parameters were filled in, then both columns must match the parameter, but in the simplified Where, only one has to match.

As Suresh suggested, REOPT should work, as would dynamic SQL. If you don't want the prepare overhead, then your suggestion is the way to go - if ITEM_NO is passed in, then run query1, else if ITEM_KEY is filled in, run query2, else run query3.

Joe

In Reply to Suresh Sane:

Tried REOPT?

Thx
Suresh

________________________________
From: Greg Palgrave <[login to unmask email]>
Sent: Thursday, July 16, 2020 1:43 AM
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - [Db2 LUW] Query with optional parameters?


Hi List,

Just calling on your collective wisdom here. I've noticed a query on one of our Prod systems which is taking up around 30-40% of the total execution time during the online day. It runs 3-500 times per hour and it seems to be one that comes from a web application of some sort. It sounds worse than it is, but as we've tuned other stuff, it's now become more obvious.

I've done explains, and had a play around looking at indexing, and there is an index which helps slightly (maybe 5% improvement in the cost estimate), but I think the chief issue is that it appears that the query has two optional parameters which are only used in the WHERE clause.

e.g.

WHERE ( ? LIKE '' OR ITEM_KEY >= ? )
AND ( ? = 0 OR ITEM_NO = ? )
ORDER BY ITEM_KEY ASC, ITEM_NO ASC
FETCH FIRST 100 ROWS ONLY

ITEM_KEY is CHAR(20) NOT NULL

ITEM_NO is INTEGER NOT NULL

There is no ITEM_NO=0, and ITEM_KEY >= '' would just return the first 100 rows.

Traditionally, we could look at doing a dynamic query, or having logic in the code to decide which version of the query to run depending on which fields had values, but coding for performance is old-fashioned (aka too hard) so I'm not optimistic of getting code logic rewritten to deal with it.

But, just looking at the WHERE clause, and the data, it seems to me it could be simplified to just:

WHERE ( ITEM_KEY >= ? OR ITEM_NO = ? )



Have I missed something in the logic here? It's making my head hurt :)

I'll still have to run it past the Dev team, but I don't want to suggest the change if I've missed something obvious.

Testing the query explain with that change to the WHERE clause reduces the estimated cost by 20%, which I will call a win if we can do it.



Cheers

Greg









-----End Original Message-----

Greg Palgrave

RE: [Db2 LUW] Query with optional parameters?
(in response to Joe Geller)

Thanks Joe and Suresh,

I will have to check into the REOPT options.

I think this application is using a JDBC connection, so that's another can of worms to delve into :D
I might just have to see if I can persuade them to alter the code.

Cheers

Greg

In Reply to Joe Geller:

Unfortunately, your format :

WHERE ( ITEM_KEY >= ?  OR  ITEM_NO = ? )

is not equivalent.  If both optional parameters were filled in, then both columns must match the parameter, but in the simplified Where, only one has to match.

As Suresh suggested, REOPT should work, as would dynamic SQL. If you don't want the prepare overhead, then your suggestion is the way to go - if ITEM_NO is passed in, then run query1, else if ITEM_KEY is filled in, run query2, else run query3.

Joe

In Reply to Suresh Sane:

Tried REOPT?

Thx
Suresh

Roy Boxwell

[Db2 LUW] Query with optional parameters?
(in response to Greg Palgrave)
A large stick with some rusty nails in it helps a lot at the negotiation table…



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Greg Palgrave <[login to unmask email]>
Sent: Friday, July 17, 2020 3:22 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 LUW] Query with optional parameters?



Thanks Joe and Suresh,

I will have to check into the REOPT options.

I think this application is using a JDBC connection, so that's another can of worms to delve into :D
I might just have to see if I can persuade them to alter the code.

Cheers

Greg

In Reply to Joe Geller:

Unfortunately, your format :

WHERE ( ITEM_KEY >= ? OR ITEM_NO = ? )

is not equivalent. If both optional parameters were filled in, then both columns must match the parameter, but in the simplified Where, only one has to match.

As Suresh suggested, REOPT should work, as would dynamic SQL. If you don't want the prepare overhead, then your suggestion is the way to go - if ITEM_NO is passed in, then run query1, else if ITEM_KEY is filled in, run query2, else run query3.

Joe

In Reply to Suresh Sane:

Tried REOPT?

Thx
Suresh



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: [Db2 LUW] Query with optional parameters?
(in response to Greg Palgrave)

Greg,

I am dismayed to hear coding for performance is old fashioned. Coding for bad performance is allowed, using this technique, which I call "Switch-on, Switch-off" predicates, and stuffs up DB2's ability to get index matching and screening.

This coding technique could well be banned, unless related to very weak filtering predicates.  The ITEM_KEY one may not be so serious, but
? LIKE = ' ' 

Is silly code.   Why use LIKE instead of Equals?   Maybe they could have coded just ITEM_KEY >= ?

Suresh idea to REOPT is good. This may allow DB2 to eliminate through query rewrite, the ?=0 predicate when its true. Not 100% sure for LUW but try it.  Dynamic SQL for this type of query is good, or the option to write multiple variations. Multiple variations could be written as a single SQL by UNION ALL on the variations, and a technique to cause only one leg of the UNION ALL to do anything.

You could add predicate (as additional) without losing any data (it allows extra rows though still to be filtered by the original predicates. This redundant predicate is worthwhile if gives a better access path:

WHERE ( ITEM_KEY >= ?  OR  ITEM_NO = ? )

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 20, 2020 - 08:47 AM (Australia/Melbourne)

Greg Palgrave

RE: [Db2 LUW] Query with optional parameters?
(in response to Michael Hannan)


Hi Michael,

That's the one that I don't understand either. I actually didn't think that should work, but reading the syntax for the LIKE it is allowed. It's just silly, as you say.

It's lazy programming.   We have people who can write ridiculously complex pieces of SQL, rather than write a program, or even stored procedure, with some logic in it. Just so it is done in a single query.

Just because you CAN do something, doesn't make it right.

And, of course, when you complain about it - 'but it works!"  <sigh>

Cheers

Greg

In Reply to Michael Hannan:

This coding technique could well be banned, unless related to very weak filtering predicates.  The ITEM_KEY one may not be so serious, but

? LIKE = ' ' 

Is silly code.   Why use LIKE instead of Equals?   Maybe they could have coded just ITEM_KEY >= ?

 

Michael Hannan,
DB2 Application Performance Specialist

James Campbell

[Db2 LUW] Query with optional parameters?
(in response to Michael Hannan)
> ? LIKE = ' ' 
> Is silly code.   Why use LIKE instead of Equals?   Maybe they could have coded just ITEM_KEY >= ?

Because
' ' = '' is true (apostrophe, space, apostrophe equals apostrophe, apostrophe)
' ' LIKE '' is false

Perhaps
LENGTH(?) = 0
is clearer.

James Campbell

On 19 Jul 2020 at 15:40, Michael Hannan wrote:

> Greg,
>
> I am dismayed to hear coding for performance is old fashioned. Coding for bad performance is allowed, using this technique, which I call "Switch-on, Switch-off" predicates, and stuffs up DB2's ability to get index matching and screening.
> This coding technique could well be banned, unless related to very weak filtering predicates.  The ITEM_KEY one may not be so serious, but
> ? LIKE = ' ' 
> Is silly code.   Why use LIKE instead of Equals?   Maybe they could have coded just ITEM_KEY >= ?
> Suresh idea to REOPT is good. This may allow DB2 to eliminate through query rewrite, the ?=0 predicate when its true. Not 100% sure for LUW but try it.  Dynamic SQL for this type of query is good, or the option to write multiple variations. Multiple variations could be written as a single SQL by UNION ALL on the variations, and a technique to cause only one leg of the UNION ALL to do anything.
> Michael Hannan,
> DB2 Application Performance Specialist
>
>


--
This email has been checked for viruses by AVG.
https://www.avg.com

Michael Hannan

RE: [Db2 LUW] Query with optional parameters?
(in response to Greg Palgrave)

In Reply to Greg Palgrave:

It's lazy programming.   We have people who can write ridiculously complex pieces of SQL, rather than write a program, or even stored procedure, with some logic in it. Just so it is done in a single query.

Just because you CAN do something, doesn't make it right.

And, of course, when you complain about it - 'but it works!" 

 

Greg,

I can be right to do something in the SQL engine to reduce SQL call overheads. If doing something strange in the SQL, comments are permitted to explain why.  This technique of "Switch-on Switch-off" predicates is indeed lazy because it degrades performance, by use of good filtering stage 2 predicates.  There are smart SQL techniques, and this is not one of them, yet seen very commonly in every shop. There is a lot of copying of dumb ideas going on.

We don't want the filtering to be done in the program. However the programmer could code nice SQL variations depending on search criteria with the main Equals predicate either in or out, or could make a Dynamic SQL to achieve the same, including just the desired predicates.

So has REOPT  produced a much better performance for you? If it can do internal rewrite of the query to drop the always true equals switch predicate, it might be giving a better performance.
I think unfortunately it won't work because the LIKE predicate is not rewritten, and remains part of a Stage 2 OR construct. So the LIKE predicate would have outsmarted the Optimizer.

I think unfortunately the only solution will be to rewrite the SQL. Either as Dynamic or split into 2 SQLs, with and without the Equals predicate.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 21, 2020 - 10:31 AM (Australia/Melbourne)

Greg Palgrave

RE: [Db2 LUW] Query with optional parameters?
(in response to Michael Hannan)

Hi Michael,

 

Unfortunately, this is not a compiled program, there is no package bound for it, so there's no simple use of REOPT. 

I would have to rebind the JDBC/CLI packages with REOPT, and that may have unforeseen side-effects, so I will just have to push it back to the developers and see if they will be able to modify their approach. It won't get a high priority at the moment due to project work.

In other news, the sky is blue, water is wet... etc. No surprises there either :D

Cheers

Greg

In Reply to Michael Hannan:

So has REOPT  produced a much better performance for you? If it can do internal rewrite of the query to drop the always true equals switch predicate, it might be giving a better performance.

I think unfortunately it won't work because the LIKE predicate is not rewritten, and remains part of a Stage 2 OR construct. So the LIKE predicate would have outsmarted the Optimizer.

I think unfortunately the only solution will be to rewrite the SQL. Either as Dynamic or split into 2 SQLs, with and without the Equals predicate.

Michael Hannan,
DB2 Application Performance Specialist

Joe Geller

RE: [Db2 LUW] Query with optional parameters?
(in response to Greg Palgrave)

Hi Greg,

I know you will get pushback, but you have to be firm.  I get hit by this type of coding a couple of times a year.  I just say "you have to make this change; this program will have lousy response time in production (and I sometimes have to explain why production sized databases do not perform the same as test or QA)."   It helps to have the explain to show them the access path and how it can't use the indexes. 

It also helps to give them an example to show them the change is really simple and will take them an hour or less to code and then they test it (I give each new developer and example from a different program that has been modified this way).  Although sometimes it takes as long to explain it to them as it will take them to make the change.  And I am not exaggerating how quick it is to code.

Joe

In Reply to Greg Palgrave:

Hi Michael,

 

Unfortunately, this is not a compiled program, there is no package bound for it, so there's no simple use of REOPT. 

I would have to rebind the JDBC/CLI packages with REOPT, and that may have unforeseen side-effects, so I will just have to push it back to the developers and see if they will be able to modify their approach. It won't get a high priority at the moment due to project work.

In other news, the sky is blue, water is wet... etc. No surprises there either :D

Cheers

Greg

In Reply to Michael Hannan:

So has REOPT  produced a much better performance for you? If it can do internal rewrite of the query to drop the always true equals switch predicate, it might be giving a better performance.

I think unfortunately it won't work because the LIKE predicate is not rewritten, and remains part of a Stage 2 OR construct. So the LIKE predicate would have outsmarted the Optimizer.

I think unfortunately the only solution will be to rewrite the SQL. Either as Dynamic or split into 2 SQLs, with and without the Equals predicate.

Michael Hannan,
DB2 Application Performance Specialist

Michael Hannan

RE: [Db2 LUW] Query with optional parameters?
(in response to Greg Palgrave)

Greg,

You can test stand alone with a dynamic Explain to see if would have worked to get a good access path for specific test values for the parameter markers. I suspect not in this case, because is too complex for internal query rewrite to work for you.

If it would have worked, there is a SET SQL to set REOPT on before a dynamic SQL (at least in Db2 zOS there is and I guess other platform won't be so different).

Yes no solution is going to be easy to apply. If there was, I would have fixed all of them at my site already. Fix them if they are hurting bad enough.  Train your developers not to do this.

In the end The SQL is going to need to be written better as Dynamic to insert the predicate clauses needed instead of switch-on switch-off construct.

I agree with Joe. We see this horrible technique many times per year. So many people wanted to copy this very dumb technique. After all, if it works, who cares about performance????  I should rename this technique to "performance disabling switch predicates".

Switch-on Switch-off predicates, being Stage 2, are not so disastrous if the level of filtering by the whole construct is very weak, meaning most rows will qualify anyway. If it does strong filter, the performance is really shitty!

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 23, 2020 - 09:26 AM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jul 23, 2020 - 09:27 AM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jul 23, 2020 - 09:30 AM (Australia/Melbourne)