I have written a validation parser which gets called using webservice (meaning need high performance code to be run),
I have added the Example link in Live sql
where I have added conditional clauses (for null checking several input parameters and query accordingly) to the where clause of the sql part (thinking that sql optimizer will make conditional code faster) rather than using traditional IF conditions to handle the sql clauses .
However the Code reviewer thinks , that such and\or conditional clauses reduce performance of sql query , whereas I think such clauses will reduce PLSQL performance instead , (believing SQL Optimizer can optimize these conditional clauses quite efficiently)
SELECT COUNT (*)
FROM
MTL_MANUFACTURERS WHERE ( :P_MANUFACTURER_ID IS NOT NULL AND :P_MANUFACTURER_NAME IS NULL AND
MANUFACTURER_ID = :P_MANUFACTURER_ID ) OR ( :P_MANUFACTURER_ID IS NULL AND :P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = :P_MANUFACTURER_NAME ) OR ( :P_MANUFACTURER_ID IS NOT NULL AND :P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = :P_MANUFACTURER_NAME AND MANUFACTURER_ID = :P_MANUFACTURER_ID );
Is there any other way to increase the performance for this query?
"It depends".
The advantage of separate statements you choose between with IF clauses is you can hand-tune each. The downside is this gets impractical as you add more optional parameters.
Having a single SQL statement is easier to manage. But the downside of having all the conditional tests in one SQL statement is the optimizer will struggle to come up with a good solution in all cases. And it'll tend towards a full table scan, no matter which indexes you create.
But there is another technique which may help.
Create single columns indexes on all your searchable columns. Then write a query which:
- Has a separate select for each column matching the corresponding parameter, returning the rowid
- Union all these rowids together
- Check that the number of times each rowid is returned matches the number of non-null parameters
Which for you would look something like:
select * from mtl_manufacturers
where rowid in (
select rid from (
select rowid rid from mtl_manufacturers
where manufacturer_id = p_manufacturer_id
union all
select rowid rid from mtl_manufacturers
where manufacturer_name = p_manufacturer_name
)
group by rid
having count(*) = nvl2(p_manufacturer_id,1,0) +
nvl2(p_manufacturer_name,1,0)
);For more details on this method, read this post from Oren Nakdimon who came up with this approach:
http://db-oriented.com/2018/04/14/a-single-query-with-many-filter-combinations/ Now, there's
no guarantee this will be better. Ultimately, there's only one way for you to know:
TEST!
Load the table with realistic production data. And compare your different methods. See which comes out best.