How to reduce time(increase performance) for multiple insertion.

[Chandan Kr Sah]

Currently I have approx 35000 records and for populating those records in Report I have to insert those records in a temprorary table through loop. But for this operation(insertion in temp table) its taking more than 8 min.currently I am using DAO connection.So how can I reduce the time for 1-2 min.The code is given below:-

Dim Dbs As Database
Dim rst As DAO.Recordset
Set Dbs = CurrentDb
Set rst = Dbs.OpenRecordset(ssql) 
 
    While Not rst.EOF
        If IsNull(rst("Advisor Name")) Then
            tmpAdvisorName = ""
        Else
            tmpAdvisorName = rst("Advisor Name")
        End If
        If IsNull(rst("Customer Name")) Then
            tmpCustomerName = ""
        Else
            'tmpCustomerName = rst("Customer Name")
            tmpCustomerName = Replace$(rst("Customer Name"), DoubleQuote, CHRDouble)
        End If
        If IsNull(rst("Country")) Then
            tmpCountry = ""
        Else
            tmpCountry = rst("Country")
        End If
        If IsNull(rst("State")) Then
            tmpState = ""
        Else
            tmpState = rst("State")
        End If
        If IsNull(rst("City")) Then
            tmpCity = ""
        Else
            tmpCity = rst("City")
        End If
 
        If IsNull(rst("Job Title")) Then
            tmpJobTitle = ""
        Else
            tmpJobTitle = rst("Job Title")
        End If
        If IsNull(rst("Unit Type")) Then
            tmpUnitType = ""
        Else
            tmpUnitType = rst("Unit Type")
        End If
        If IsNull(rst("Activity")) Then
            tmpActivity = ""
        Else
            tmpActivity = rst("Activity")
        End If
        If IsNull(rst("Arrival Date")) Then
            tmpArrivalDate = ""
        Else
            tmpArrivalDate = rst("Arrival Date")
        End If
        If IsNull(rst("Depart Date")) Then
            tmpDepartDate = ""
        Else
            tmpDepartDate = rst("Depart Date")
        End If
        If IsNull(rst("Unit Type2")) Then
            tmpUnitType2 = ""
        Else
            tmpUnitType2 = rst("Unit Type2")
        End If
        If IsNull(rst("Unit Type3")) Then
            tmpUnitType3 = ""
        Else
            tmpUnitType3 = rst("Unit Type3")
        End If
        If IsNull(rst("Unit Type4")) Then
            tmpUnitType4 = ""
        Else
            tmpUnitType4 = rst("Unit Type4")
        End If
        If IsNull(rst("Unit Type5")) Then
            tmpUnitType5 = ""
        Else
            tmpUnitType5 = rst("Unit Type5")
        End If       
                ssql = "insert into [tblAdvanceReport_Temp] ([Advisor name], [Customer Name], Country, State, City, [Job title], [Unit Type], Activity,[Arrival Date],[Depart Date],"
        ssql = ssql & " [Unit Type2], [Unit Type3], [Unit Type4], [Unit Type5]"
        ssql = ssql & ") values "
        ssql = ssql & "(" & Chr(34) & tmpAdvisorName & Chr(34) & ", " & Chr(34) & tmpCustomerName
        ssql = ssql & Chr(34) & ", " & Chr(34) & tmpCountry & Chr(34) & ", " & Chr(34) & tmpState
        ssql = ssql & Chr(34) & ", " & Chr(34) & tmpCity & Chr(34) & ", " & Chr(34) & tmpJobTitle
        ssql = ssql & Chr(34) & ", " & Chr(34) & tmpUnitType & Chr(34) & ", " & Chr(34) & tmpActivity
        ssql = ssql & Chr(34) & ", " & Chr(34) & tmpArrivalDate & Chr(34) & ", " & Chr(34) & tmpDepartDate
        ssql = ssql & Chr(34) & ", " & Chr(34) & tmpUnitType2 & Chr(34) & ", " & Chr(34) & tmpUnitType3
        ssql = ssql & Chr(34) & ", " & Chr(34) & tmpUnitType4 & Chr(34) & ", " & Chr(34) & tmpUnitType5 & Chr(34) & ")"
 
        DoCmd.SetWarnings False
        DoCmd.RunSQL (ssql)
        DoCmd.SetWarnings True
 
        rst.MoveNext
    Wend
 
    rst.Close

[TheSmileyCoder]

Why do you even loop through the recordset to begin with?

Why not simply do it in SQL? Example below:

ssql = "Insert Into [tblAdvanceReport_Temp] ([Advisor name], [Customer Name], Country, State, City, [Job title], [Unit Type], Activity,[Arrival Date],[Depart Date],"
        ssql = ssql & " [Unit Type2], [Unit Type3], [Unit Type4], [Unit Type5])"
        ssql = ssql & " SELECT [Advisor Name], [Customer Name] ......"
        ssql = ssql & " FROM tbl_Main"
        ssql = ssql & " WHERE ....."
 

Replace the .... with whatever is appropriate for you.

Another thing i notice, is all your if-statements. A simpler way of writing teh same is:

tmpCountry = nz(rst("Country"),"")

or

tmpCountry = rst("Country") & ""

I think the last of these two approaches may be the fastest, but I haven't actually tested.

A third approach can be to open a recordset to your temp table for examle:

Dim rstTemp as dao.Recordset
rstTemp=CurrentDb.Openrecordset(SELECT * FROM tblAdvanceReport_Temp",dbOpenDynaset)
 
Do While not rst.Eof
   rstTemp.AddNew
   rstTemp![Customer Name]=rst![Customer Name]
   rstTemp![Advisor Name]=rst![Advisor Name]
   rstTemp.Update
   rst.MoveNext
Loop
set rstTemp=Nothing

Finally if your temporary table is indexed, and your inserts happen one at a time (as shown in your code) you might get a performance increase by removing the index in code, running the inserts, and then readding the index when the inserts are complete.

With all I've said here, I would still go with the first SQL option, unless that for some reason not shown here is not feasable.

[Chandan Kr Sah]

Thanks a lot...I got the solution...

I modified the query as you suggested in the first case and now it is executing with in 1.15 min.