My codes run VERY SLOW.....

I'm parsing a fixed length text file with around 1200 columns in length. The feed may contain up to 2500 records or sometimes more.

I'm facing a big problem with the processing time. My application run extremely slow for only getting the records into a table.

If I can put all the records into a table I still need to do more processing, such as checking each column for a correct value.

I've been trying many ways but I always stuck with a slow process, nothing I did seem to help my application to run faster.

I'm using CF 8, Sybase and Unix

Is there more efficient ways to parse fixed length text file and insert them into a table?

Here are what I just tried:

I'm looping each records to create structures. I tried both of this methods successfully (see attachment, I can only post 1 structure in this forum since there are too many elements within 1 structure)

Once I have them all in structures, then I insert each value to a table.

When the feed only contain a few hundred records, I'm OK but when the feed start to get bigger, for example 2500 records, my loop takes too long to create these structures. Sometimes I got a timed-out error. This is my 1st problem.

1. <cffile action="READ" file="#FilePath##FileName#" variable="FileLine">

    <cfset MyFile = listtoarray(FileLine,"#chr(13)##chr(10)#")>

   <cfset st_Rec=StructNew()>

   <cfloop from="1" to="#ArrayLen(MyFile)#" index="i">

     <cfset st_Rec["camp"]="#Trim(Left(MyFile,2))#">

     <cfset st_Rec["Id"]="#Trim(Mid(MyFile,3,2))#">

     etc.......

     <!--- do insert into table here --->

     calling a function inside a component to insert into table or calling a str proc to do insert into table

  </cfloop>

OR

2. <cfset st_Rec=StructNew()>

   <CFLOOP file="#FeedPath##FileName#" index="Line">

      <cfset st_Rec["camp"]="#Trim(Left(MyFile,2))#">

      <cfset st_Rec["Id"]="#Trim(Mid(MyFile,3,2))#">

      etc.......

     <!--- do insert into table here --->

   </CFLOOP>

As you can see from my structure (attached), there are many empty elements within my structures.

This empty elements may contribute to the lengthy looping process.

At first I thought I could do a collection loop within each structure to just get the non empty column and then process them, this will probably cut the proccessing time but I'm facing other problem and that is, the next feed may not have the same exact column with empty value. I'm not sure how can I write the insert into column names if the column are changing every  time.

3. <cfset st_Rec=StructNew()>

   <cfset stReqField=StructNew()>

   <CFLOOP file="#FeedPath##FileName#" index="Line">

      <cfset st_Rec["camp"]="#Trim(Left(MyFile,2))#">

      <cfset st_Rec["Id"]="#Trim(Mid(MyFile,3,2))#">

      etc.......

      <cfloop collection="#st_Rec#" item="KEY">

        </cfif>

      </cfloop>

      <!--- do insert here --->

   </CFLOOP>