oracle - SQL query with binding variables in Java is taking a long time -


im trying execute query parameters. if execute sql in pl/sql developer (oracle) literal parameters runs in 1 second. if execute sql using java, jdbc driver , literal parameters runs fast too. example using code:

    string query = "select id, (a lot of other columns)"             + " vcp_tit_liqdc"             + " where"             + " ((123 not null , id_tit = 123 , exists"             + " (select 1 sd_dual"             + " ((exists"             + " (select 1"             + " fn_config_usr cfg"             + " cfg.nome_usr_bd = 'name' , cfg.ind_acesso_qualquer_site = 's') or exists"             + " (select 1"             + " fn_rel_usr_site rel"             + " rel.nome_usr_bd = 'name' , rel.cod_site = decode(vcp_tit_liqdc.id_empresa_lider, null, vcp_tit_liqdc.cod_site, vcp_tit_liqdc.cod_site_empresa_lider)))))) or"             + " (123 null , (28 null or"             + " ((id_empresa = nvl(28 ,id_empresa) , id_empresa_lider null) or"             + " id_empresa_lider = 28 )) , decode (id_empresa_lider, null, cod_site, cod_site_empresa_lider) in"             + " (select nvl (null , site.cod_site)"             + " fn_site site, fn_rel_usr_site rus, fn_config_usr cfg"             + " cfg.nome_usr_bd = 'name' , rus.nome_usr_bd (+) = cfg.nome_usr_bd , site.cod_site = decode (cfg.ind_acesso_qualquer_site, 's', site.cod_site, rus.cod_site) group site.cod_site))) , (usr_cadastro = 'name' or ( id_tit in"             + " (select ac.id_tit"             + " cp_titulo_acesso ac, cp_usr_acesso_janela jan"             + " ac.id_acesso = jan.id_acesso , 'window' = nvl(jan.nome_janela_sis, 'window' ) , jan.nome_usr_bd = 'name' ))) order id_tit desc";      preparedstatement p = connection.preparestatement(query);     resultset rs = p.executequery();

but if set parameters using "?" (binding) takes 10 minutes or more run. example in code:

    string query = "select id, (a lot of other columns)"             + " vcp_tit_liqdc"             + " where"             + " ((? not null , id_tit = ? , exists"             + " (select 1 sd_dual"             + " ((exists"             + " (select 1"             + " fn_config_usr cfg"             + " cfg.nome_usr_bd = ? , cfg.ind_acesso_qualquer_site = 's') or exists"             + " (select 1"             + " fn_rel_usr_site rel"             + " rel.nome_usr_bd = ? , rel.cod_site = decode(vcp_tit_liqdc.id_empresa_lider, null, vcp_tit_liqdc.cod_site, vcp_tit_liqdc.cod_site_empresa_lider)))))) or"             + " (? null , (? null or"             + " ((id_empresa = nvl(? ,id_empresa) , id_empresa_lider null) or"             + " id_empresa_lider = ? )) , decode (id_empresa_lider, null, cod_site, cod_site_empresa_lider) in"             + " (select nvl (? , site.cod_site)"             + " fn_site site, fn_rel_usr_site rus, fn_config_usr cfg"             + " cfg.nome_usr_bd = ? , rus.nome_usr_bd (+) = cfg.nome_usr_bd , site.cod_site = decode (cfg.ind_acesso_qualquer_site, 's', site.cod_site, rus.cod_site) group site.cod_site))) , (usr_cadastro = ? or ( id_tit in"             + " (select ac.id_tit"             + " cp_titulo_acesso ac, cp_usr_acesso_janela jan"             + " ac.id_acesso = jan.id_acesso , ? = nvl(jan.nome_janela_sis, ? ) , jan.nome_usr_bd = ? ))) order id_tit desc";      preparedstatement p = connection.preparestatement(query);      p.setint(1, 123);     p.setint(2, 123);     p.setstring(3, "name");     p.setstring(4, "name");     p.setint(5, 123);     p.setint(6, 28);     p.setint(7, 28);     p.setint(8, 28);     p.setstring(9, null);     p.setstring(10, "name");     p.setstring(11, "name");     p.setstring(12, "window");     p.setstring(13, "window");     p.setstring(14, "name");      resultset rs = p.executequery();

is there way resolve problem? because sql same in each case.

i using 10g database , tested versions of jdbc: 10.2.0.1.0, 11.2.0.2.0 , 12.1.0.2.0.

the query containing static values has predicates such "123 null , (28 null" static values optimizer knows conditions never true can ignore them. analogy, if told go shop , buy tin of striped paint, can see there's no point in going because there's no such thing.

when actual variables there potential can null there whole bunch of work needs doing.

you best off testing of values null-ness in java code , constructing simpler query execute.


Comments

Post a Comment

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more