ODBC Data load is slow

[#1]

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?

[alexandros17]

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

[peter_turner]

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.

[jonathandienst]

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

[#5]

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]

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.:

[alistairross]

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

[sujith1011]

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]

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

[Anonymous]

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.