topic Loading data from ODBC oracle in QlikView

Loading data from ODBC oracle

Sean_BI — Sat, 16 Nov 2024 18:27:24 GMT

Hi,

Problem statement:

Reading data into to qlikview from ODBC (oracle) is slow

Sever configuration as below.

Qlikview client: 12.3 (64bit) - installed

Required connectors / odbc oracle drivers - installed

Oracle client version - Ora12c

O/s windows server 10 - 64 bit

No of server - 2 servers ( each server has same configuration ie, 512 GB RAM each, same Ora client each, same ODBC drives on each server)

RAM-512 GB

issue:

I use a simple sql query to read from oracle -.

Load a,b,c,d;

select a,b,c,d from Table1;

Table1 has 2 million records with 4 fields only.

Step1: Tested the connection successfully on both win server where oracle ODBC 64 bit drives are installed.

Step2: Ensured the tracing is turned off in ODBC driver manager (https://support.qlik.com/articles/000003925)

Step3: hit reload qv script editor.

Step4: The reading from database took almost 3 hrs to fetch 2 million rows on server A, where as the same query took 30 min to fetch on server B.

Also, followed the reference url: https://answers.microsoft.com/en-us/office/forum/office_2010-access/my-solution-to-access-being-slow-with-odbc/a5a6522f-a70f-421e-af1b-48327075e010

Any thoughts on this please.

Rgds,

Sean.

Re: Loading data from ODBC oracle

Brett_Bleess — Wed, 08 Jul 2020 20:27:39 GMT

I suspect the Oracle forums would be the better place to search for a solution on this, as in most cases, the issue is on the Oracle side of things, either something misconfigured in the ODBC client setup, or other issues along those lines. You will likely find better troubleshooting info on their site than ours. The one thing I would recommend as a sanity check is to try to run the same query using Excel etc. and see what it does, as if that too is slow, that will confirm it is most likely in the Oracle side of things... Sorry I do not have anything better for you, there are some other Oracle posts on the Community, so you may want to research those too just to see if you can pick up any further ideas from folks that have posted about their issues here too in the past.

Regards,
Brett

Re: Loading data from ODBC oracle

Sean_BI — Thu, 16 Jul 2020 13:30:18 GMT

Thank you. I shall check from Oracle side.

Before that will enable "Tracing" in ODBC connection to see what it does behind scenes.

Shall close the thread.

Rgds,

Sean.

Re: Loading data from ODBC oracle

Brett_Bleess — Thu, 16 Jul 2020 17:13:28 GMT

Hey Sean, I had a quick look in the defect system the only thing I see related to 12c driver and QlikView was an issue with using an * character in the connection password causing the login to fail, do not see see anything else, so hopefully you may be able to find some thing on the Oracle site. If you get further info, feel free to shout back.

Regards,
Brett

Re: Loading data from ODBC oracle

Sean_BI — Mon, 10 Aug 2020 07:11:50 GMT

Thank you Breet for response.

I need to re verify with ODBC connection and Oracle 12 c driver. The issue is still unknown and what is causing delay in reading from qlikview connecting to Oracle.

Thank you!

Sean.

Re: Loading data from ODBC oracle

Brett_Bleess — Tue, 11 Aug 2020 17:12:24 GMT

Hey Sean, may want to try enabling the QvConnect logging in this case, that might tell you something additional if it may be something on our side, or may provide something you cannot see currently too:

QvConnect Logging

Regards,
Brett