Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

ODBC Data load is slow

im just loading a datamart from our database via ODBC Connector. It's running since 45 minutes and only 200.000 data sets were loaded. Is there any chance to speed up the load?

9 Replies

Re: ODBC Data load is slow

Install a new odbc driver ... maybe it is faster

Partner
Partner

Re: ODBC Data load is slow

Couple of quick points you can look at:

Are you able to use a 64bit driver or 32bit?

Newer ODBC driver?

Maybe an OLE DB driver would be quicker?

How are you loading the data, is there alot of manipulation or is that just a straight table load

Any performance problems of the database server

Connection speed to the database server.

As you say 200,000 rows in 45 mins is quite slow.

MVP
MVP

Re: ODBC Data load is slow

The load speed is dependent on the load on the server, the speed of the driver and the load on the network . You would need to analyse these to see what is limiting the spreed.

Unless you are doing something really unusual, 200,000 rows is a small data set and it is probably not your machine or load script causing the slow load.

What is the ODBC data source? Non-normalised data sources like Universe can be very slow over ODBC as the ODBC driver has to normalise the data first.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: ODBC Data load is slow

As a last resort:

Keep an eye on the script execution window, is the row count extracted each time increasing in chunks exactly the same size each time after a delay?

If so ask you DBA what the Session data unit (SDU) is on the DB (default 8k 11gr2 Oracle) then try running the command 'netsh interface ipv4 show subinterfaces' on the QlikView server and make sure the Maximum Transmission Unit (MTU) is below this value. I have investigated this before and ended up tweaking our driver settings to allow more throughput to QlikView.

Thanks

herbert_beck
New Contributor III

Re: ODBC Data load is slow

Dear Andre,

improve the performance of the ODBC connection by increasing the FETCH BUFFER SIZE of the ORACLE-ODBC-driver to 20 000 000 Bytes or more (from 64 000 bytes).

Kind regards,

Herbert

P.S.:

Partner
Partner

Re: ODBC Data load is slow

Hi Herbert,

I am having a similar problem, and decided to change the fetch buffer size. However part way through the run the setting reverts. Are you aware of any other way that this setting could be changed, or do I need to change it in a different file, or even the loadscript itself?

Let me know if you need any other details about my system.

Thanks

Alistair

Partner
Partner

Re: ODBC Data load is slow

How much time does it take on any other tool like Toad, sql developer, or sqlplus

if you could provide you sql, I can take a look

nizamsha
Valued Contributor II

Re: ODBC Data load is slow

some of the possibility is there

check synthetic key is there if its  not

check  your disk space do u have enough space 

instead of ODBC u can go for OLEDB

or install a new ODBC

leandro_job
New Contributor III

Re: ODBC Data load is slow

What is your OS?

I have a similar issue, and was looking here in QlikView community the answer, but was unsucessful.

Hoply, I found this article in Microsoft Forum who solved my problem. Maybe this could be yours to. The ODBC tracing option.


My solution to Access being slow with ODBC - Microsoft Community

I believe because i use Windows 10, and it has HD usage overloaded problems.

When I disabled the "when to trace" option, the disk usage don't go to 99%, and instantly the database load increased.

I hope this could be useful to someone.