[cx-oracle-users] issue with Unicode parameters
Brought to you by:
atuining
From: Valentin K. <vk...@gm...> - 2016-06-03 14:39:51
|
Hi, I identified a problem with cx_Oracle driver and would like to understand how it can be resolved. Basically, I observed a huge latencies with query if I pass unicode string as binded parameter value. Below a stand-alone code which reproduce a problem with our Oracle. We run CERN Scientific Linux distribution (SLC 6.8), the code runs on x86_64 platform with gcc493 and cx_Oracle 5.2.1 import cx_Oracle def test_cx_oracle(uname, pwd, host, sql, params): time0 = time.time() connection = cx_Oracle.connect(uname, pwd, host) cursor = connection.cursor() cursor.execute(sql, **params) lfn = params['logical_file_name'] print("lfn type=%s, cursor execute %s sec" % (type(lfn), time.time()-time0)) for row in cursor: print row cursor.close() def main(): # read password from a file ppp = open('ppp').readline().replace('\n','').replace('oracle://', '') line = ppp.split('@') host = line[-1] uname, pwd = line[0].split(':') sql = 'SELECT F.LOGICAL_FILE_NAME FROM cms_dbs3_int_global_owner.FILES F WHERE F.IS_FILE_VALID <> -1 AND F.LOGICAL_FILE_NAME = :logical_file_name' lfn = '/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root' params = {'logical_file_name': lfn} test_cx_oracle(uname, pwd, host, sql, params) params = {'logical_file_name': u'%s' % lfn} test_cx_oracle(uname, pwd, host, sql, params) if __name__ == '__main__': main() The output of this program is the following: lfn type=<type 'str'>, cursor execute 0.0578980445862 sec ('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',) lfn type=<type 'unicode'>, cursor execute 144.633666992 sec ('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',) As you can see using string parameter we quickly get results from Oracle in 0.06 sec, while using unicode we see huge latency 144sec. Any suggestions why unicode parameters such poorly behaves and where the time is spent on? Thanks, Valentin. |