[RESOLVED] Million Plus Records 36 fields, Delete-Insert

[jkmcgrath]

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;

    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

[brucevde]

Look into Bulk Insert and Format Files.

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

Not necessarily. In your case you are creating a new statement for each record. A Prepared Statement only works if you execute the same statement more than once. You achieve that by creating an Insert command with parameters

Command.CommandText = "Insert Into Table (Field1, Field2) Values (?,?)
Command.Parameters.Append Command.CreateParameter("Field1",advarchar,20)

[si_the_geek]

A prepared statement is not the equivalent of a stored procedure, as a SP has an execution plan (the servers choice of the 'best' method of running the SQL). A prepared statment (AFAIK) just basically says 'the syntax is valid, so dont bother checking'. However, for a straight Insert as you have this isn't really an issue.

One better method for inserting lots of records is to do a Bulk Insert. I haven't done this myself, but there should be a decent explanation in Books Online or the ADO help files (or from another forum member!).

Another option is to use the Import facilities of SQL Server, I believe there is a utility called BCP which does this, or you can use the "Import data" wizard in Enterprise Manager to set up a task to do it (this task can be scheduled later).

[jkmcgrath]

Guys thanks for the help!

I did use the DTS for the text file and it took 1 min 30 secs to insert into a clean table. From table to table it took 8 mins 30 secs, hard to believe the text file DTS is faster but oh well lol

This is a workable solution now.

Thanks again!

[szlamany]

The reason for the speed difference is partially due to the fact that some of the BULK INSERT, BCP and DTS options are not logged. The database engine talks directly to the data - not going through the log - this is important.

We always use BULK INSERT to process inserts of that many rows. We process them into staging tables and then process the data from the staging table into the production tables.

You can consider BULK INSERT of the data (DTS probably is using BULK INSERT) into a different database - so that you do not overwhelm the production DB while the data inserts...