AC import script take much longer to import 2000 records in 1 hour

[tech9merge]

I have weekly jobs which basically validates the csv file provided using a vbscript and then generates a validated text file. Then the AC import script imports the data from this delimited text file to AC 4.3.2 , but the upload takes much longer time , 2000 records only takes 1hour 10 mins which is slower by any means. Could someone let me know what could be the reason for this dead slow processing by AssetCenter. Could somebody please suggest a way so that the import will happen much faster. this is urgent.


thanks,

 

[Jasonic]

Can you provide a sample of the validated file and the import script

Jason Thomas
AssetCenter Consultant
Jason Thomas Consultancy Limited
[rolf]

 

[tech9merge]

the following is the ac import script. its just a txt file that's getting imported.


{ Ver-1.1 Import_Script
RecordsPerTransac=100
ImpType=1
LogErrorsInFile
NoDbbConf
SkipEmptySrcFields=1
LogFile="C:\\testfolder\\testone.log"
{ DataBaseSrc
DbEngine=Txt
DbUser=
DbIdent=testfile.scr
Engineoptions=AutoCommit=1;DateAsTimestamp=1
DbbFile=
AutoDescribe
{ Table Valid_Info
{ File "..\\Vated\\vtext.txt"
FixedSeparation=0
FileType=ANSI
StartIndex=0
FLTitle=1
ColumnSeparators="\t,"
AssumeSepsAsUnique=0
StringSeparators=
{ Column SoftName
ColName=SoftName
ColSize=12
ColType=6
}
{ Column UID
ColName=UID
ColSize=10
ColType=6
}
{ Column reqDt
ColName=reqDt
ColSize=21
ColType=6
}
{ Column SlNo
ColName=SlNo
ColSize=10
ColType=6
}
{ Column wstation
ColName=wStation
ColSize=15
ColType=6
}

{ Column FacCode
ColName=FacCode
ColSize=12
ColType=6
}
}
}
{ CalculatedFields
{ Valid_Info CalcFldAssignment
RetVal=0
}
{ Valid_Info CalcDate
RetVal=AmDate()
}
{ Valid_Info CalcNature
RetVal = "Info"
}
}
}
{ Table
Source=Valid_Info
Destination=amSoftInstall
{ Field
Source=SlNo
Destination=Xfield
}
{ Field
Source=WStation
Destination=Yfield
}
{ Ver-1.1 Import_Script
RecordsPerTransac=100
ImpType=1
LogErrorsInFile
NoDbbConf
SkipEmptySrcFields=1
LogFile="C:\\testfolder\\testone.log"
{ DataBaseSrc
DbEngine=Txt
DbUser=
DbIdent=testfile.scr
Engineoptions=AutoCommit=1;DateAsTimestamp=1
DbbFile=
AutoDescribe
{ Table Valid_Info
{ File "..\\Vated\\vtext.txt"
FixedSeparation=0
FileType=ANSI
StartIndex=0
FLTitle=1
ColumnSeparators="\t,"
AssumeSepsAsUnique=0
StringSeparators=
{ Column SoftName
ColName=SoftName
ColSize=12
ColType=6
}
{ Column UID
ColName=UID
ColSize=10
ColType=6
}
{ Column reqDt
ColName=reqDt
ColSize=21
ColType=6
}
{ Column SlNo
ColName=SlNo
ColSize=10
ColType=6
}
{ Column wstation
ColName=wStation
ColSize=15
ColType=6
}
{ Column AssetTag
ColName=AssetTag
ColSize=14
ColType=6
}
{ Column FacCode
ColName=FacCode
ColSize=12
ColType=6
}
}
}
{ CalculatedFields
{ Valid_Info CalcFldAssignment
RetVal=0
}
{ Valid_Info CalcDate
RetVal=AmDate()
}
{ Valid_Info CalcNature
RetVal = "Info"
}
}
}
{ Table
Source=Valid_Info
Destination=amSoftInstall
{ Field
Source=SlNo
Destination=Xfield
}
{ Field
Source=WStation
Destination=Yfield
}
{ Link
PrimaryIdent
Destination=PortfolioItem
LinkedRecordsOnly
}
{ Field
Source=CalcFldAssignment
Destination=PortfolioItem.bEnStock
}
{ Field
Source=CalcDate
Destination=PortfolioItem.dAssignment
}
{ Field
Source=AssetTag
Destination=PortfolioItem.CodeInt
}
{ Link
Destination=PortfolioItem.Localisation
NotCreateIfNotFound
}
{ Field
PrimaryIdent
Source=FacilityCode
Destination=PortfolioItem.Localisation.CustomFldFacilityCode
}
{ Link
Destination=PortfolioItem.Utilisateur
NotCreateIfNotFound
}
{ Field
PrimaryIdent
Source=UID
Destination=PortfolioItem.Utilisateur.Champ1
}
{ Link
Destination=PortfolioItem.Model
}
{ Field
PrimaryIdent
Source=SoftName
Destination=PortfolioItem.Model.Name
}
}
}

{ Link
PrimaryIdent
Destination=PortfolioItem
LinkedRecordsOnly
}
{ Field
Source=CalcFldAssignment
Destination=PortfolioItem.bEnStock
}
{ Field
Source=CalcDate
Destination=PortfolioItem.dAssignment
}
{ Field
Source=AssetTag
Destination=PortfolioItem.CodeInt
}
{ Link
Destination=PortfolioItem.Localisation
NotCreateIfNotFound
}
{ Field
PrimaryIdent
Source=FacilityCode
Destination=PortfolioItem.Localisation.CustomFldFacilityCode
}
{ Link
Destination=PortfolioItem.Utilisateur
NotCreateIfNotFound
}
{ Field
PrimaryIdent
Source=UID
Destination=PortfolioItem.Utilisateur.Champ1
}
{ Link
Destination=PortfolioItem.Model
}
{ Field
PrimaryIdent
Source=SoftName
Destination=PortfolioItem.Model.Name
}
}
}

 

[Jasonic]

Tech9Merge

I have a few ideas/Questions.
[li]Firstly, does the import involve spanning several tables, if so can the import be done in more than 1 pass of the txt file?[/li]
[li]Secondly are you importing and commiting 1 at a time, maybe changing this to commit every 100 records.[/li]
[li]Thirdly, Are you using appropriate keys within AC, do the fields have indexes on?[/li]

Sorry but I couldn't veiw the import script :-(

Jason Thomas
AssetCenter Consultant
Jason Thomas Consultancy Limited
[rolf]

 

[tech9merge]

thanks jason, the code that you saw above was the sample of the entire import script that gets created when i import the text file via assetcenter import. the text file has records that looks like below.the import script was created as tab separated. also which fields you think should be indexed. Its only updating two tables.

MSExcel,RVECMM30,3/4/2005 4:30:00 PM,99GDYW2,MJGABASLKECMM30,99GDYW2-msexcel18,HOME
MsOffice,PGHJAM25,3/7/2005 10:39:00 AM,KA9TC97,MJGSGLVW63548,KA9TCH8-msoffice19,GLVT
MSWindows,SHLJM21,3/8/2005 3:15:00 PM,786LCCF,MJGFLLKELJM32,786LCCF-mswindows,NFLD
MSWord,WKTLAJ02,3/4/2005 10:02:00 AM,99GFHG2,MJHNAENWLKERAJ02,99GFHG2-msword,ENGW
MSXP,XBTDAW7,3/7/2005 12:06:00 PM,78MMCVR,MJGUSMADL46270,78MMCVR-Acro23,MADC

 

[tech9merge]

Could Jason or some expert please reply for the above post.????????????

 

[Jasonic]

I only have AC4.4 on my machine at the moment. I was unable to open your import script in AC4.4.

The field CustomFldFacilityCode is it being used as a key if so does this field have an index on it.

Before importing ask the DBA team to check whats happening to check if any full table scans are occuring, if so make sure that which ever field you are using as the Key has an index on it, to avoid the full table scan.

Also, your only importing into two tables, but are you spanning multiple links?

Jason Thomas
AssetCenter Consultant
Jason Thomas Consultancy Limited
[rolf]

 

[Jasonic]

Tech9Merge

I notice that no-one at Peregrine has replied to your request. Also remembering that the 18th and 19th equals a weekend, I'm sure you understand that I don't work weekends unless I have to.

Maybe you should ask Russell Parker for some help. He seems to be the expert on the Peregrine Discussion Forums.

Jason Thomas
AssetCenter Consultant
Jason Thomas Consultancy Limited
[rolf]

 

[ORettinger]

Just a few ideas:

- Is your server module "update table statistics" runned on a schedule (like once a day)? updating these statistics will speed up imports a lot

- Can you map your dAssignment to another field (text-field), so AC doesn't need to convert String to Date, which is quite time-intensive sometimes

- As mentioned above, try to split your import into more than one import-script (Import A = import just flat data for populating, import B = create the necessary links)

- Any workflows are triggered through your import and executed immediately? If yes try to change their behavior to "run by server"

- Any "keep history"-configurations to your fields? If yes, remember to update table statistics.