Topic
  • 9 replies
  • Latest Post - ‏2013-02-22T01:44:00Z by sbjerry
OptimDev
OptimDev
54 Posts

Pinned topic Optim Insert Request very slow.(Updating the table)

‏2012-06-08T14:42:51Z |
Hi All,
I am Inserting data into an Oracle database via Optim.I have selected insert\update option in the insert request.Now optim is running the request but table update is very slow.The Table has approximately 2 million rows and it took 16 hours to load a million rows.The table has a composite and when i created the extract file, i have selected the indexes in the objects tab.Please let me know any pointers to fix the issue.
Commit frequency is set to 2000 rows.
disable Triggers and constraints are set to never

Thanks
Updated on 2013-02-22T01:44:00Z at 2013-02-22T01:44:00Z by sbjerry
  • Ducatiman
    Ducatiman
    58 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-08T15:47:48Z  
    How long does it take outside of Optim to do an Insert?
    Did you truncate the table beforehand?
    Is there a reason why you can't do a load request instead?
    Can you delete the table beforehand and perform a straight insert?

    I had an issue like this going against Siebel tables with 2 Billion rows.
    We had to get very creative.

    Thanks
    • Duc
  • OptimDev
    OptimDev
    54 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-08T17:16:04Z  
    • Ducatiman
    • ‏2012-06-08T15:47:48Z
    How long does it take outside of Optim to do an Insert?
    Did you truncate the table beforehand?
    Is there a reason why you can't do a load request instead?
    Can you delete the table beforehand and perform a straight insert?

    I had an issue like this going against Siebel tables with 2 Billion rows.
    We had to get very creative.

    Thanks
    • Duc
    Thanks a lot for such a quick response.Please see below:
    How long does it take outside of Optim to do an Insert?
    I Have not tried outside of Optim

    Did you truncate the table beforehand?
    I will not be able to truncate the table as the table is referenced by many other tables.

    Is there a reason why you can't do a load request instead?
    Load to the best of my knowledge can be run in two modes i.e.
    insert mode-----this cannot be used in this case as the table already has data and i am updating with masked data.

    Can you delete the table beforehand and perform a straight insert?
    I cannot delete the table .

    I had an issue like this going against Siebel tables with 2 Billion rows.
    We had to get very creative.
    if possible please let me know how you did it with siebel as this is also similar i.e.oracle Apps.

    Thanks...
  • Ducatiman
    Ducatiman
    58 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-08T17:35:22Z  
    • OptimDev
    • ‏2012-06-08T17:16:04Z
    Thanks a lot for such a quick response.Please see below:
    How long does it take outside of Optim to do an Insert?
    I Have not tried outside of Optim

    Did you truncate the table beforehand?
    I will not be able to truncate the table as the table is referenced by many other tables.

    Is there a reason why you can't do a load request instead?
    Load to the best of my knowledge can be run in two modes i.e.
    insert mode-----this cannot be used in this case as the table already has data and i am updating with masked data.

    Can you delete the table beforehand and perform a straight insert?
    I cannot delete the table .

    I had an issue like this going against Siebel tables with 2 Billion rows.
    We had to get very creative.
    if possible please let me know how you did it with siebel as this is also similar i.e.oracle Apps.

    Thanks...
    Hey there.
    So you're not masking every record?

    One way to consider:

    perform a load or insert into new table using Optim.
    Update original table based upon contents of new table (outside of Optim Within database)).
    Delete new table.

    something like:

    UPDATE suppliers
    SET supplier_name = ( SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id)
    WHERE EXISTS
    ( SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id);

    • Duc
  • OptimDev
    OptimDev
    54 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-08T18:00:28Z  
    • Ducatiman
    • ‏2012-06-08T17:35:22Z
    Hey there.
    So you're not masking every record?

    One way to consider:

    perform a load or insert into new table using Optim.
    Update original table based upon contents of new table (outside of Optim Within database)).
    Delete new table.

    something like:

    UPDATE suppliers
    SET supplier_name = ( SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id)
    WHERE EXISTS
    ( SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id);

    • Duc
    Sorry for not explaining it in a better way.
    I am masking every record in the table but i cannot really use truncate as this has inter-related data i.e. with foreign key relationships for many other tables.As said Load request's insert option will fail as the table already has data.The other option in load is Replace which will delete all the records in that table and replace with that in .xf file.I was wondering how this will work if the table has inter-related data i.e. with foreign key relationships for many other tables???
    Also thanks for Outside optim suggestion i will try it out.But sooner or later i will have to fasten it from optim itself .This is because i will like to automate the whole extraction,conversion and insertion process.
    Thanks.
  • OptimDev
    OptimDev
    54 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-08T18:07:07Z  
    • OptimDev
    • ‏2012-06-08T18:00:28Z
    Sorry for not explaining it in a better way.
    I am masking every record in the table but i cannot really use truncate as this has inter-related data i.e. with foreign key relationships for many other tables.As said Load request's insert option will fail as the table already has data.The other option in load is Replace which will delete all the records in that table and replace with that in .xf file.I was wondering how this will work if the table has inter-related data i.e. with foreign key relationships for many other tables???
    Also thanks for Outside optim suggestion i will try it out.But sooner or later i will have to fasten it from optim itself .This is because i will like to automate the whole extraction,conversion and insertion process.
    Thanks.
    To add on my source and destination database are same.Also I definitely think delete is not an option here as that will require disabling constraints if i am right.Please Let me know if you have any thoughts on this.
    Again thanks a lot :)
  • Ducatiman
    Ducatiman
    58 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-08T18:08:23Z  
    • OptimDev
    • ‏2012-06-08T18:00:28Z
    Sorry for not explaining it in a better way.
    I am masking every record in the table but i cannot really use truncate as this has inter-related data i.e. with foreign key relationships for many other tables.As said Load request's insert option will fail as the table already has data.The other option in load is Replace which will delete all the records in that table and replace with that in .xf file.I was wondering how this will work if the table has inter-related data i.e. with foreign key relationships for many other tables???
    Also thanks for Outside optim suggestion i will try it out.But sooner or later i will have to fasten it from optim itself .This is because i will like to automate the whole extraction,conversion and insertion process.
    Thanks.
    Use the Truncate and Load in Optim.
    When Optim performs the truncate it runs a stored procedure to disable the relationships (not remove them).
    The truncate is done then load and finally Optim stored procedure reenables the relatiosnhips. Oracle will perform a validation to verify...

    Feel free to email me directly at Allan@abmartin.com
  • OptimDev
    OptimDev
    54 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-08T18:44:10Z  
    • Ducatiman
    • ‏2012-06-08T18:08:23Z
    Use the Truncate and Load in Optim.
    When Optim performs the truncate it runs a stored procedure to disable the relationships (not remove them).
    The truncate is done then load and finally Optim stored procedure reenables the relatiosnhips. Oracle will perform a validation to verify...

    Feel free to email me directly at Allan@abmartin.com
    Thanks a lot for all the help extended!!!
  • OptimDev
    OptimDev
    54 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2012-06-28T19:28:55Z  
    • Ducatiman
    • ‏2012-06-08T18:08:23Z
    Use the Truncate and Load in Optim.
    When Optim performs the truncate it runs a stored procedure to disable the relationships (not remove them).
    The truncate is done then load and finally Optim stored procedure reenables the relatiosnhips. Oracle will perform a validation to verify...

    Feel free to email me directly at Allan@abmartin.com
    Hello,
    I am trying to load with the Truncate option,Disable triggers set to never and disable constraints set to yes.Will this disable my triggers too???because i have seen the status in the load request saying that disabling constraints and triggers.Please let me know your inputs
    Thanks
  • sbjerry
    sbjerry
    23 Posts

    Re: Optim Insert Request very slow.(Updating the table)

    ‏2013-02-22T01:44:00Z  
    • OptimDev
    • ‏2012-06-28T19:28:55Z
    Hello,
    I am trying to load with the Truncate option,Disable triggers set to never and disable constraints set to yes.Will this disable my triggers too???because i have seen the status in the load request saying that disabling constraints and triggers.Please let me know your inputs
    Thanks
    Uh, I met with the same scenario, and what's going on with your project? Seems the discuss stoped here