×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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,

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

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

Jason Thomas
AssetCenter Consultant
Jason Thomas Consultancy Limited
[rolf]

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

(OP)
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
    }
  }
}

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

Tech9Merge

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


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

    Jason Thomas
    AssetCenter Consultant
    Jason Thomas Consultancy Limited
    [rolf]

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

    (OP)
    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

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

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

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

    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]

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

    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]

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

    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.

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
    The Tek-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Tek-Tips forums is a member-only feature.

    Click Here to join Tek-Tips and talk with other members! Already a Member? Login

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close