topic Best performance select condition in Application Development and Automation Discussions

Best performance select condition

flvia_s — Tue, 26 Jan 2016 11:38:14 GMT

Hi guys.

I would like to listen your opinion on this performance issue.

I have a requirement to do a select on a Z table that has the primary key fields A, B and C.

The selection is based on the fields D, E, F, G and there exist an index for these four fields combined.

The data selection is this:

- field D = 01,

- field F is 'S', if E = 001

F is ''or 'E' if E = 002 or 003

- field G is in range_g

Now, what's the best selection for this? Would it be better to:

- use where condition with D = 01 and ( ( E = 001 and F = 'S') or ( E in (002, 003) and F in ('',E)) and G in range_g?

- use two separated selects?

Would really appreciate your opinion!

Best regards,

Alm.

Re: Best performance select condition

matt — Tue, 26 Jan 2016 11:49:39 GMT

Have you tried the different approaches and traced them through ST05? It's actually impossible for anyone to give a definitive answer as the performance depends very much on the data in the table.

Re: Best performance select condition

flvia_s — Tue, 26 Jan 2016 11:52:13 GMT

Hi Matthew, the thing is that the table is new, so there is no data enough to test it...

Re: Best performance select condition

matt — Tue, 26 Jan 2016 11:54:45 GMT

Nonetheless. It's still impossible to give a definitive answer.

Write a program to put some data in it that's as realistic as possible. Then run ST05 sql trace on all the possibilities and see how the database optimiser handles it - which indexes are used and the performance.