Importing data to Oracle Table is too slow

[taranoberoi]

Hi All- Need your help. I am trying to read data from Oracle table and push it to different oracle schema table using SQL query which got joins.

Issue here is total number of records are around 255k and it is taking more than 4-5 hours

Any suggestion what shud I do.

Thanks

[SeanAdams]

Hey @taranoberoi

Without looking deeper at the config of your server; your tables; and the particular case, it's difficult to give a definitive answer - but here's a few areas you can look into that would have been my natural trouble-shooting process for this kind of issue:

Is the query taking 4-5 hours for the read, for the transport, or for the write?

If the read cycle is taking the majority of the time, then your best course of action is to index the tables that you're doing the query on.   I don't know the oracle version of this, but in SQL there is a query analyzer which allows you to auto index your query.   The purpose of this is to get your read down to a few seconds.   You can test this very easily by opening up a query window against your oracle DB and just running your read query and timing it (you can use things like Aqua Data Studio; or even Microsoft Excel to query your oracle DB).    You can also use your native oracle DB or even Aqua to look at the "Estimated Execution Plan" to see if there are table-scans etc.   Your target should be that the read cycle should take a few seconds or a minute or two for 200k rows (assuming that the overall data-volume is fairly small.   You can also test this by changing the select clause to "Select count(1) from ..." so that you can see how well your data is indexed.

if the transport is taking the majority of the time:

Then you have a few options:

- if you're doing this on the same server - just do it all in-database (Insert into schema2.table2 select * from schema1.table1).   This eliminates the round-trip to alteryx and back over the network

- Reduce the number of columns 

- reduce the size of the columns (by changing them to the smallest possible type)

- Use a native Oracle tool.   In Microsoft SQL, there are 3 ways to do this and I presume that there are analogies in Oracle:

     - If I'm doing this within the same SQL server - just do as a single sql query (as above) or as a stored procedure

     - If I'm doing this across servers I can use the SQL Server Integration Services (Microsoft's data-pump tools) to quickly pump this across

     - or finally you can use the bulk loader (in SQL this used to be called BCP, not sure if they've updated it) - this takes the target table offline and bulk copies data in)

If the insert is taking the majority of the time:

Then the problem is probably triggers or indecies.   Work with your DBA on options to temporarily disable or do a bulk insert (again, SQL server offers options as listed above, so I fully expect that Oracle will too, given that they're a world-leading database).

The issue here is that every time you insert a row into a database, triggers fire, and indices have to be updated, and individual rows need to be locked - so if you can disable these temporarily, you can add an order of magnitude on your insert speed.

Hope these help!

Sean

[taranoberoi]

Hi Sean - Thanks for all the info.  It is simple reading from one table and pushing into other table and it is not from the same database so can not use in database.

Second thing as u suggested reading is not taking time. it is reading all the records(256k) in 1 minutes since i used block until done and then noticed that after reading all the records from input in 1 minutes it is just inserting 2% of data in 20 minutes so i cancelled the workflow.additionally i tried by just pushing 100 records and it was again slow.

Now i again tried it with  output as Excel file and it was super duper fast.  Regarding the table insert i created this table on fly and can insert data from excel to the same table and it uploads 100k records in a minute using oracle tool.

So dont know what to do. kindly suggest.

[SeanAdams · Accepted solution]

:-) great job in diagnosing this @taranoberoi,

So, as you say, it's not the read cycle, and you can bring it down to your machine in seconds so it's not a slow network from the source to your machine.    You've also confirmed that you can pump records into a brand new table in seconds so it's probably not the network.

- When you did the insert using Oracle insert tool - did you insert the same data (the specific 255k rows with all the columns)?   This would be a useful test because some tables are pretty huge (data and column-wise) so you may get different results across the network using a test dataset and a realistic dataset.

      - If this is slow, then you may have a lag on your network

           You can check for the latter by doing a ping test (just ping your oracle DB with a packet size which is material)

           https://www.google.co.in/search?q=ping+test+with+packet+size&rlz=1C1PRFE_enUS671IN671&oq=ping+test+w...

           You can also check this by pumping the data back into a new table on your source server to see if this is faster.   This is obviously not a solution, but it does tell you if it's the network/server that's the problem

If this is still super-speedy then you may have a problem with your target table

- is your target table created on the fly when you insert?   If it's a brand new table then you should have no index or trigger problems

- are you doing an "Append" or an "Update/insert if new".   Append is always faster.

If none of these get you much more insight - you can always call the oracle insert tool using a command tool within Alteryx.    I don't know the syntax for calling the oracle insert tool from command line, but it definately seems possible:

https://www.google.co.in/search?q=ping+test+with+packet+size&rlz=1C1PRFE_enUS671IN671&oq=ping+test+w...