Hey all,

I have this situation where I am have to sFTP a 132mb file from one server(AIX) to a windows2k server. Takes about 50 seconds so not entirely bad.

Where I have to come up with a better solution is taking that 132mb flatfile, parsing it and placing it in a SQL2K DB.

Right now I am using a textstream reading the file by line, chuncking it up into strings and insert them into the db as such running a loop while the textstream is not eof;
Code:
    sqlINSERT = "INSERT INTO KCZ27M_MO(SOS_27, SHIP_INDIC_27, FSC_27, NIIN_27, EXTRA_27, PREF_FSC_27," & _
    "PREF_NIIN_27, STATUS_CODE_27, UNIT_ISSUE_27, QUANTITY_27, DODAAC_27, DATE_27, DOC_SER_NO_27," & _
    "DOC_IDENT_27, TRANS_CODE_27, DEMAND_CODE_27, SUFFIX_CODE_27, STORAGE_LOC_27, SIGNAL_CODE_27," & _
    "FUND_CODE_27, EIC_27, PROJECT_CODE_27, PRIORITY_DESIG_27, TRANS_DAY_27, ADVICE_CODE_27," & _
    "MED_STAT_CODE_27, RDD_27, CONDITION_CODE_27, REQUESTED_NSN_27, SARSS_A5A_27, SUPP_ADDR_CODE_27," & _
    "BEG_OH_QTY_27, END_OH_QTY_27, SYSTEM_DATE_X_27, JOB_NO_27)" & _
    "Values" & _
    "('" & strSOS_27 & "', '" & strSHIP_INDIC_27 & "', '" & strFSC_27 & "', '" & strNIIN_27 & "'," & _
    "'" & strEXTRA_27 & "', '" & strPREF_FSC_27 & "', '" & strPREF_NIIN_27 & "','" & strSTATUS_CODE_27 & "'," & _
    "'" & strUNIT_ISSUE_27 & "', '" & strQUANTITY_27 & "', '" & strDODAAC_27 & "', '" & strDATE_27 & "'," & _
    "'" & strDOC_SER_NO_27 & "','" & strDOC_IDENT_27 & "', '" & strTRANS_CODE_27 & "', '" & strDEMAND_CODE_27 & "'," & _
    "'" & strSUFFIX_CODE_27 & "', '" & strSTORAGE_LOC_27 & "','" & strSIGNAL_CODE_27 & "'," & _
    "'" & strFUND_CODE_27 & "', '" & strEIC_27 & "', '" & strPROJECT_CODE_27 & "'," & _
    "'" & strPRIORITY_DESIG_27 & "','" & strTRANS_DAY_27 & "', '" & strADVICE_CODE_27 & "'," & _
    "'" & strMED_STAT_CODE_27 & "', '" & strRDD_27 & "', '" & strCONDITION_CODE_27 & "'," & _
    "'" & strREQUESTED_NSN_27 & "', '" & strSARSS_A5A_27 & "', '" & strSUPP_ADDR_CODE_27 & "'," & _
    "'" & strBEG_OH_QTY_27 & "', '" & strEND_OH_QTY_27 & "'," & _
    "'" & strSYSTEM_DATE_X_27 & "', '" & strJOB_NO_27 & "')"
    Set objCMDIN.ActiveConnection = oCon
    objCMDIN.CommandText = sqlINSERT
    objCMDIN.CommandType = adCmdText
    objCMDIN.Prepared = True
    rsIN.CursorType = 1
    rsIN.LockType = 3
    Set rsIN = objCMDIN.Execute(, adAsyncExecute)
This takes forever to literaly go thru One million plus records! I need to be able to do this several times in a 8 hour work day. 2 plus hours is not acceptable and I have to find a better way.

My understanding is the prepared statement is the equivelent to a storedproc, is that correct? If so then creating a storedproc would not help.

My other thought was to take and make the file into smaller, 50k or so lines and then run multi recordsets to get it done faster.

Anyone offer any advice that may help?

Thanks
John