Script speed....

[Tori Mitchell]

Oookay, so this should be an interesting first posting...

I've got a script that I'm using for a data migration from an old, fairly poorly designed DB with no normalization and I've got a simple script to pull apart the multiple phone entries into seperate records for the usual reasons. Problem is, though it's a fairly simple script, it really slows down the process, almost tripling the time that this operation takes without said script. I was wondering if there was anyone who could find something stupid that I'm doing with it or if this is about as 'optimal' as I can make the code.

Thanks in advance.

Set Variable [ $email; Value:##Contact_Management_Import::Email ] 

Set Variable [ $additional; Value:##Contact_Management_Import::Phone Addtl ] 

Set Variable [ $fax; Value:##Contact_Management_Import::Phone Fax ] 

Set Variable [ $home; Value:##Contact_Management_Import::Phone Home ] 

Set Variable [ $main; Value:##Contact_Management_Import::Phone Main ] 

Set Variable [ $main_extension; Value:##Contact_Management_Import::Phone Main Extension ] 

Set Variable [ $mobile; Value:##Contact_Management_Import::Phone Mobile ] 

Set Variable [ $phone_other; Value:##Contact_Management_Import::Phone Other ] 

Set Variable [ $current_record; Value:##Import_Contacts_to_Contact_Management::ContactCompanyID_kf ] 

Go to Layout [ “ImportPhoneEmailFax” (##Import_Contacts_to_PhoneFaxEmail) ] 

If [ Length(Trim($email)) > 2 ] 

	New Record/Request 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneContactID_kf; $current_record ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneClass_k; "Email" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneNumber; $email ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneType; "Main" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

End If 

If [ Length(Trim($fax)) > 2 ] 

	New Record/Request 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneContactID_kf; $current_record ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneClass_k; "Fax" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneNumber; $fax ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneType; "Fax" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

End If 

If [ Length(Trim($main)) > 2 ] 

	New Record/Request 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneContactID_kf; $current_record ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneClass_k; "Phone" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneNumber; $main & If(Length(Trim($main_extension))≥1;" " & $main_extension;"") ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneType; "Main" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

	Set Variable [ $main_set; Value:1 ] 

End If 

	If [ Length(Trim($home)) > 2 ] 

	New Record/Request 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneContactID_kf; $current_record ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneClass_k; "Phone" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneNumber; $home ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneType; "Home" ] 

	If [ not $main_set = 1 ] 

		Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

		Set Variable [ $main_set; Value:1 ] 

	End If 

End If 

If [ Length(Trim($mobile)) > 2 ] 

	New Record/Request 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneContactID_kf; $current_record ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneClass_k; "Phone" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneNumber; $mobile ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneType; "Cell" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

	If [ not $main_set = 1 ] 

		Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

		Set Variable [ $main_set; Value:1 ] 

	End If 

End If 

If [ Length(Trim($phone_other)) > 2 ] 

	New Record/Request 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneContactID_kf; $current_record ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneClass_k; "Phone" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneNumber; $phone_other ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneType; "Other" ] 

	If [ not $main_set = 1 ] 

		Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

		Set Variable [ $main_set; Value:1 ] 

	End If 

End If 

If [ Length(Trim($additional)) > 2 ] 

	New Record/Request 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneContactID_kf; $current_record ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneClass_k; "Phone" ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneNumber; $additional ] 

	Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneType; "Additional" ] 

	If [ not $main_set = 1 ] 

		Set Field [ ##Import_Contacts_to_PhoneFaxEmail::PhoneFlagMain_k; 1 ] 

		Set Variable [ $main_set; Value:1 ] 

	End If 

End If 

Set Variable [ $main_section; Value:Get ( CurrentTimeStamp ) ] 

Set Variable [ $counter; Value:$counter + 1 ] 

Go to Layout [ original layout ] 

Go to Record/Request/Page  [ Next; Exit after last ]

[comment]

Option 1:

Make your script import the data 9 times - one phone field (and the common fields, of course) at a time. Use auto-entered variables for the type, etc.

Option 2:

Use repeating calc fields where each repetition is one phone/type. Import the repeating fields while breaking them into separate records.

[Tori Mitchell]

That's essentially what I'm doing there. The data is passed into script variables at the initial record and then I go to a layout for the table that I'm importing it into and, for every type of phone record in there, I break out a a new record and type it according to the originating field... which is why I really can't use the calculated values thing.

What's really irritating is that this script isn't really that complex in terms of what it's doing... just that it takes about 30 Sec/100 Records to process.

[comment]

That's essentially what I'm doing there.

Yes, but I suggested you do it not essentially, but actually - i.e. use REAL import instead of looping. Importing is the fastest way to create new records.

break out a a new record and type it according to the originating field... which is why I really can't use the calculated values thing.

Lost me there.

[Tori Mitchell]

I got ya now. Problem is with importing that there are holes in the data and now, because of the way that the new solution is structured, there's a bit of logic in there but I get ya. I suppose that I can just work it from a different angle and then go back and clean the data after the import. I wonder if that will actually speed anything up though.

For what I was talking about earlier, in the script, it looks at where the data came from and decides the type if entry from that and then goes and flags the primary of that type via a cascading set of requirements eg:

Phone - Main (Primary)

Phone - Mobile (Primary if Main is not in existence)

Phone - Office (etc...)

Phone - Home

I suppose that I can do this with searches and deletions. It'll definately be an... interesting way to do things.

[comment]

You could do the cascading flag thing in a repeating calculation field, as I mentioned in option 2. Keep in mind that empty repetitions are not imported, so if the calc is right, there should be no holes.

I wonder if that will actually speed anything up though.

See here.