5 Replies Latest reply: Aug 18, 2012 11:25 AM by MarkPP RSS

    loading data with data workshop

    MarkPP
      Hello,
      We load lots of .csv files into apex using the Dataworkshop tool in Apex. Most .csv files are 60-75K rows of data. It used to take just minutes and was quick and easy way to load data. However, recently it has started taking much much longer (as in several hours vs. 5 or 6 minutes normally) to load data this way into production.

      Any ideas of why all of a sudden it takes much longer to load data this way? Is there anything that can be tuned?

      We are running Application Express 4.1.1.00.23 on RH 5 linux with 11.2.0.2 database.

      Thank you for any help you can provide.

      Thank you,
      Mark
        • 1. Re: loading data with data workshop
          Mehabub Sheikh
          An alternative way would be create a staging table and upload data there and once upload is complete. Call a DBMS job to transfer data from staging table to original table.
          And once the transfer is complete delete/truncate the table. Actually as the size of the table grows day by day then somewhere down the line you are forced to see performance problem.

          Hope this will resolve your issue.

          Cheers,
          Mehabub
          • 2. Re: loading data with data workshop
            MarkPP
            Mehabub,
            Thank you for responding. In a sense that is kind of what we are doing. We truncate the table and upload our .csv to this table and then process into our other tables.

            The table does not have a particularly large number of rows (usually 60,000 - 70,000 rows), but does have 112 columns.

            It all seems to work fine in our test environment, but there are not the number of users there as in production. We have been looking at the SGA parameters and seem to make headway, but then the customer tries to upload and it slows way way down in production.

            Our customers really like this functionality we have given them with apex, but I am running out of ideas of what to look at.

            Thank you,
            Mark
            • 3. Re: loading data with data workshop
              Mehabub Sheikh
              It it possible to debug?
              Go to application properties and enable debugging.
              Then login with developer role and click on debug.

              See which part is taking the maximum time.

              Thanks,
              Mehabub
              • 4. Re: loading data with data workshop
                sect55
                Mark,

                Does the table have indices?
                If it has a lot of indices, you may need to drop the indices, load the table, and recreate the indices or, better yet, just drop the indices totally.

                Robert
                http://apexjscss.blogspot.com
                • 5. Re: loading data with data workshop
                  MarkPP
                  Robert,
                  Thank you for responding. I am working through debug that Mehabub suggested. However, I do have three indexes that I will disable and see if that helps.

                  Thank you,
                  Mark