›
Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.

SRP_COM

ColinRuleColinRule
in SRP Utilities
Hi, I am doing a large database transfer from OI to SQL Server using SRP_COM.
I have the CREATE, and all the calls to export, then at the end I do a RELEASE.
My issue however is that I am getting out of memory issues during the export.

Is there a preferred way to do the exports?

I am thinking a create to connect, export several tables, then release. This however is still giving me issues.

Any idea please.

Colin

Comments

  • ColinRuleColinRule
    Sorry, should have said, this is in a loop.
    Ie currently, CREATE, EXPORT... LOOP, RELEASE.
    Tried in a loop, CREATE, EXPORT, RELEASE.
    Both ways give out of memory and OI crash.
  • DonBakkeDonBakke
    This definitely sounds like a memory leak. We've done enough exports to SQL to be assured that SRP_COM itself does not have a memory leak, so this might be a problem with your code or the library you are communicating through. How "large" is large"? Are you using ODBC or ADO to export the data? Finally, have you considered using BULK INSERT? This will give you the best performance with the least amount of memory usage.
  • ColinRuleColinRule
    Don,
    Yes, I thought a memory leak as well.

    Using ADO via:
    ConnectionString=Get_Property(@WINDOW:".CONNECTION_STRING","TEXT")
    Result=SRP_Com(objConnection, 'CREATE','ADODB.Connection')

    The connection string is
    Provider=SQLOLEDB.1;Server=(local)\SQLEXPRESS;Persist Security Info=False;User ID=EES-SW;Password=EES-SW;Initial Catalog=SW_DataMigration0.6

    Not tried BULK INSERT, all this is new to me.
    Not sure this is possible as the table has an incremental record key which I grab after the insert so that I can use it on the other lower level tables.

    I am also getting "Operation is not allowed when the object is closed." on the insert which is strange as it returns that error, but the command runs and the database is properly updated. This would indicate some memory leak as well, in that the connection object is gone prematurely.

    Colin
  • DonBakkeDonBakke
    Colin - Before we start chasing shadows, have you read through this article on ADO?
  • KevinFournierKevinFournier
    Without code of the entire loop (at least the SRP_COM calls), I can't advise on the use of SRP_COM. Consider releasing objects that are returned from other objects as well. I know the documentation tells you to do that with objects you specifically CREATE, but COM objects all have their own reference counters, which SRP_COM has to increment for every object it touches. The RELEASE service decrements that counter. Once a counter is 0, Windows COM removes it from memory.

    So, if you use GET to return another object, make sure you RELEASE it when you are done with it.
  • DonBakkeDonBakke
    To add to Kevin's comment, you can see examples of this in the code snippets in the aforementioned blog article. New objects are generated from the CALL method.
  • ColinRuleColinRule
    Thanks Don/Kevin
    I did not realise I also had to release objRecordSet, I was only releasing the connection object.
    It now goes a lot further, I presume it will run to the end, but I have such a lot of data I have not let it complete yet... 50gb in OI which will be who knows how much in SQL.

    I will be pushing this hard, so will advise if anything arises, but looking good now. Phew.

    Colin
  • ColinRuleColinRule
    Ran my export last night, got 50% through before I cancelled it in the morning.
    It ran 890,000 calls to SRP_COM passing a SQL insert, and all fine, no crashing/memory leaks.
    Thanks guys for a great tool.

    Colin
  • DonBakkeDonBakke
    Awesome!
Sign In or Register to comment.