Named Native query with optional list parameter taking very long time than the query dynamically formed


Named Native Query :
--------------------------

select name from person where person :tempName= 0 OR person.name in (:name))


Query query = em.createNamedQuery("namedQuery")
if(nameList== null || nameList.isEmpty()){
query.setparameter(tempName ,0)
query.setparameter(name,"")
}else{
query.setparameter("tempName", 1);
query.setparameter("name" , nameList)
}

Is this right?



0=0 will return all rows to you

Orwtech , Thank you for your time, Yes, get me records for list of names, if list is empty get me all the records. I'm using this condition as part of joins.

This may not apply to all Database but it applies to Oracle and Informix for sure.

Using optional parameters as given in the example will do sequential scan instead of looking at index.

Reason: When Database parses this sql it replaces binding parameters with placeholders and stores the statement in sql statement cache.
Therefore, the statement is optimized without the database server having access to the values of these variables. Hence Database is not
sure if the filter is applied all the time and resulting in sequential scan.

Refer below article which gives very good example:
http://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic


Also Beware of Parameter sniffing:

Consider a query below. Once Your query's execution plan is cached. Even if your filter is in different table, it will always use the same execution plan , because this query is parsed exactly the same for all scenarios.


Select
(****PO INFORMATIONS ACROSS JOINED TABLES *****)
FROM PURCHASE_ORDER PO
INNER JOIN ITEM ITM ON(JOIN CONDN...)
INNER JOIN ITEM_DETAILS ITDET ON(JOIN CONDN...)
INNER JOIN SKU SK ON(JOIN CONDN...)
INNER JOIN SKU_INFO SKI ON(JOIN CONDN...)
INNER JOIN MANUFACTURER MAN ON(JOIN CONDN...)
WHERE
(?=0 OR PO.ID IN(?))
AND(?=0 OR ITEM.ID IN(?))
AND(?=0 OR ITDET.ID IN(?))
AND(?=0 OR SK.ID IN(?))
AND(?=0 OR SKI.ID IN(?))
AND(?=0 OR MAN.ID IN(?))