Home » RDBMS Server » Networking and Gateways » DG4ODBC taking a long time to fetch from HANA database over dblink with a where clause (DG4ODBC , Windows 64, Oracle gateway 12C)
DG4ODBC taking a long time to fetch from HANA database over dblink with a where clause [message #660992] Fri, 03 March 2017 03:28 Go to next message
arnabarnab
Messages: 2
Registered: March 2017
Junior Member
Dear All,

We have a requirement of fetching some data from HANA database from Oracle via database link.
While we have successfully configured the dg4odbc driver and Oracle Gateway and we are able to fetch the data from HANA successfully over DBLink, we are facing a strange problem when specifying a where clause in the query.

The select * (without the where clause) works just fine which rules out any issue with the network, however whenever we specify a where clause(with any column)in the query, it takes a very very long time to return the result over DBLINK.

Did anyone face any problem like this before? Any pointer towards possible circumvention of the issue would be highly appreciated. Attaching a pertinent trace file from the hs/trace folder of the oracle gateway(truncated a bit to meet the size limit)


select * from "_SYS_BIC"."cdw.Finance.POC/GCBilling"@DG4ODBC GCBilling --- comes in 8 seconds(HANA takes 7.8 seconds)
select * from "_SYS_BIC"."cdw.Finance.POC/GCBilling"@DG4ODBC GCBilling where sales_order = '0055077917' --- comes in 80 seconds(HANA takes 2 seconds)

and

select * from "_SYS_BIC"."cdw.Finance.POC/GCActualCost"@DG4ODBC comes in 60 seconds(HANA takes 59 seconds)
select * from "_SYS_BIC"."cdw.Finance.POC/GCActualCost"@DG4ODBC where sales_order = '0055793352' -- comes in 9 minutes(HANA takes 3 seconds)



Thanks
Arnab

Report message to a moderator

Re: DG4ODBC taking a long time to fetch from HANA database over dblink with a where clause [message #660993 is a reply to message #660992] Fri, 03 March 2017 03:55 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to talk to your SAP HANA administrators to understand what is happenning on their side. Things as basic as whether they are using the column store or the row store architecture. Note that your use of SELECT * will not help, you should be projecting a list of only the colums you want.

There are work arounds. For example, you could select all the rows into a Global Temporary Table and apply your predicates to that.

Report message to a moderator

Re: DG4ODBC taking a long time to fetch from HANA database over dblink with a where clause [message #660994 is a reply to message #660993] Fri, 03 March 2017 06:03 Go to previous messageGo to next message
arnabarnab
Messages: 2
Registered: March 2017
Junior Member
Thanks a lot John for the prompt turnaround.
We had enabled HANA end to end tracing and by that got to know that the where clause is not getting executed in HANA, but instead Oracle is retrieving all the rows from the remote table and applying the where clause inside oracle, most probably due to the mismatch in the DATA Types.

Hence the following worked to match the data type of the column with the right hand side and thereafter, everything started to work really fast.

select * from "_SYS_BIC"."cdw.Finance.POC/GCBilling"@DG4ODBC where sales_order = CAST('XXXXXXXX' AS NVARCHAR2(100))


Thanks
Aranb

Report message to a moderator

Re: DG4ODBC taking a long time to fetch from HANA database over dblink with a where clause [message #660995 is a reply to message #660994] Fri, 03 March 2017 07:58 Go to previous message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Thanks for giving the solution. I'm sure it will help others. I have seen type mismatch problems with links to SQL Server also, where everything seemed to come across as nvarchar2.

Report message to a moderator

Previous Topic: Where are predicates applied when linking Oracle to SQL Server
Next Topic: Not seeing All Columns and Values of SQL Server Table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 12:10:47 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.