-- Only one parameter with phonetic:
 select pvPartnerAddressDE.* from vista.VPVPART_ADDR as pvPartnerAddressDE  where   ( ( vista.vistasoundex('KUCHELMEISTER')=
 pvPartnerAddressDE.namesx or  pvPartnerAddressDE.uppername like 'KUCHELMEISTER%'   ) and  (pvPartnerAddressDE.temp is null or pvPartnerAddressDE.temp <> 'Y')
 and pvPartnerAddressDE.pvalidfrom <= current date and pvPartnerAddressDE.pvalidto >= current date  ) and   
 pvPartnerAddressDE.pvalidto >= current date and pvPartnerAddressDE.pvalidfrom <= current date  and  (  ( 
 pvPartnerAddressDE.avalidto >= current date and pvPartnerAddressDE.avalidfrom <= current date and 
 pvPartnerAddressDE.bvalidto >= current date and pvPartnerAddressDE.bvalidfrom <= current date  )  
 or pvPartnerAddressDE.c_tpvadrsid = -1  or exists (   select Addr_past.KPVPARTID, max(Addr_past.validto)  
 from vista.TPVADDRESS Addr_past where  Addr_past.KPVPARTID = pvPartnerAddressDE.pkpvpartid and  Addr_past.TPVADRSID = pvPartnerAddressDE.tpvadrsid  
 group by Addr_past.KPVPARTID  having max(Addr_past.validto) < current date   )  or exists (   select Addr_future.KPVPARTID, min(Addr_future.validfrom) 
 from vista.TPVADDRESS Addr_future where  Addr_future.KPVPARTID = pvPartnerAddressDE.pkpvpartid and  Addr_future.TPVADRSID = pvPartnerAddressDE.tpvadrsid 
 group by Addr_future.KPVPARTID  having min(Addr_future.validfrom) > current date   )  )
 ORDER BY pvPartnerAddressDE.uppername, pvPartnerAddressDE.upperfirstname, pvPartnerAddressDE.pkpvpartid    Fetch first    300     rows only;
-- prodZH: 951 sec ('KUCHELMEISTER') / 200 



-- Alternative without like:
 select pvPartnerAddressDE.* from vista.VPVPART_ADDR as pvPartnerAddressDE  where   ( ( vista.vistasoundex('KUCHELMEISTER')=
 pvPartnerAddressDE.namesx  ) and  (pvPartnerAddressDE.temp is null or pvPartnerAddressDE.temp <> 'Y')
 and pvPartnerAddressDE.pvalidfrom <= current date and pvPartnerAddressDE.pvalidto >= current date  ) and   
 pvPartnerAddressDE.pvalidto >= current date and pvPartnerAddressDE.pvalidfrom <= current date  and  (  ( 
 pvPartnerAddressDE.avalidto >= current date and pvPartnerAddressDE.avalidfrom <= current date and 
 pvPartnerAddressDE.bvalidto >= current date and pvPartnerAddressDE.bvalidfrom <= current date  )  
 or pvPartnerAddressDE.c_tpvadrsid = -1  or exists (   select Addr_past.KPVPARTID, max(Addr_past.validto)  
 from vista.TPVADDRESS Addr_past where  Addr_past.KPVPARTID = pvPartnerAddressDE.pkpvpartid and  Addr_past.TPVADRSID = pvPartnerAddressDE.tpvadrsid  
 group by Addr_past.KPVPARTID  having max(Addr_past.validto) < current date   )  or exists (   select Addr_future.KPVPARTID, min(Addr_future.validfrom) 
 from vista.TPVADDRESS Addr_future where  Addr_future.KPVPARTID = pvPartnerAddressDE.pkpvpartid and  Addr_future.TPVADRSID = pvPartnerAddressDE.tpvadrsid 
 group by Addr_future.KPVPARTID  having min(Addr_future.validfrom) > current date   )  )
 ORDER BY pvPartnerAddressDE.uppername, pvPartnerAddressDE.upperfirstname, pvPartnerAddressDE.pkpvpartid    Fetch first    300     rows only;
-- prodZH: 147 sec ('KUCHELMEISTER') / 200 


 select pvPartnerAddressDE.* from vista.VPVPART_ADDR as pvPartnerAddressDE  where   ( vista.vistasoundex('KUCHELMEISTER')=
 pvPartnerAddressDE.namesx  ) 
 ORDER BY pvPartnerAddressDE.uppername, pvPartnerAddressDE.upperfirstname, pvPartnerAddressDE.pkpvpartid    Fetch first    300     rows only;
-- prodZH: 284 sec


 select pvPartnerAddressDE.* from vista.TPVPARTNER as pvPartnerAddressDE  where   ( vista.vistasoundex('KUCHELMEISTER')=
 pvPartnerAddressDE.namesx  ) 
 ORDER BY pvPartnerAddressDE.uppername, pvPartnerAddressDE.upperfirstname, pvPartnerAddressDE.kpvpartid    Fetch first    300     rows only;
-- SIT015: 11.8 sec


select pvPartnerAddressDE.* from vista.VPVPART_ADDR as pvPartnerAddressDE  where pvPartnerAddressDE.pkpvpartid in
(
    select kpvpartid from vista.tpvpartner where namesx = vista.vistasoundex('KUCHELMEISTER')
) 
ORDER BY pvPartnerAddressDE.uppername, pvPartnerAddressDE.upperfirstname, pvPartnerAddressDE.pkpvpartid    Fetch first    300     rows only;
-- 692 sec.









