8 Replies Latest reply: Mar 11, 2016 1:25 AM by Nizam HM RSS

    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?

        • Re: ODBC Data load is slow
          Alessandro Saccone

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

          • Re: ODBC Data load is slow
            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.

            • Re: ODBC Data load is slow
              Jonathan Dienst

              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

              • Re: ODBC Data load is slow
                Philip Hand

                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

                • Re: ODBC Data load is slow
                  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.:

                    • Re: ODBC Data load is slow
                      Alistair Ross

                      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

                    • Re: ODBC Data load is slow
                      sujith madhavan

                      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

                      • Re: ODBC Data load is slow
                        Nizam HM

                        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