Large external datasets

intra

Hi,

Just wondering if anyone has any experience with large datasets?

I have an external database with close to 300,000 rows, i'd like to sync data from the external database into my own AwareIM BO but only transfer the delta between both tables.

I'm thinking i'll need a process to look similar to the following

FIND ALL External_Table
FIND ALL AwareIM_BO Where External_Table.Date <> AwareIM_BO.Date
CREATE AwareIM_BO FOR EACH ThisAwareIM_BO WITH AwareIM.Date=External_Table?

Am i on the right track?

pbrad

I'm not sure why it would make sense to have an internal AwareIM BO that is identical to an external AwareIM BO however, assuming that there is a logical reason for it I think that I would do an initial population of the internal BO to match all of the external BO records via process, export/import or through the use of a db sync tool such as Navicat.

I would then add a rule to the external BO that would run if the BO was new to create an internal BO to match the new external BO. Your server would not need to run nearly as hard with this approach.

cheers,
Pete

intra

The logical reason is that the external table belongs to a third party, the internal AwareIM BO will mirror the data for keep sake and record transactions used to generate an invoice based off those third party transactions.

intra

Ok, well after playing around with large external database views and tables. I have figured out what works well for me and my circumstances.

Tip 1:

Have small batches of data and use the CREATE FOR EACH action.

Database rows : 9455

Example:

FIND ALL External_BO IN BATCHES OF 1
CREATE Internal_BO WITH .......

Time Taken: 40 Minutes

Alternative:

FIND ALL External_BO IN BATCHES OF 100
CREATE Internal_BO FOR EACH External_BI WITH .......

Time Taken: 11 Minutes

Alternative2:

FIND ALL External_BO
CREATE Internal_BO FOR EACH External_BI WITH .......

Time Taken: 23 Minutes

rbross

I am having to deal with the same issue. Syncing up large amounts of data. Some tables with over 100k rows and lots of fields. Currently I am just building them on the fly as needed. For example if they want to see orders for a part# I read the external DB table oe_line and match it up the the current part# they are looking at. Then I do a create on the AIM order Line BO and add just the records I need to and just the fields i need to. This does not take long for the process to execute and seems to be serving the purpose for now. I think when I have to create a report I may need to create a mirror of the entire file so I can take advantage of the AIM relationships. It looks like you have played with the IN BATCHES OF and found the best number to use.

When you are connecting to your external DB are the parameters the same for test and production? For example I have a copy of the MS SQL DB on my local machine. The machine name is localhost and the DB name is databasenameTest. with a SQL User name and PW. However in production the machine name is different and the database name is different and the username and PW are different. How to do manage these changed when you export your bsv and put it in the AUTO_DEPLOYMENT folder or publish it from the configurator.

Any suggestions?

Roger