lundi 29 juin 2015

INSERT from memory to SQL inefficient

So I have been trying to optimize a situation. Essentially, I have a WEB API service that queries a SQL Server Database and then inserts it into a SQLite database on a client machine.

After running some tests. I found that it took 0.50% of the time to load the data from SQL Server into an array of objects in C# and the insert to all of the other time.

Is my insert incredibly inefficient?

Here is my method:

public void addDataToTable()
{
    using (SQLiteConnection SQLconn = new SQLiteConnection(SQLiteConnectionString))
    {
        SQLconn.Open();
        SQLiteCommand cmd = SQLconn.CreateCommand();
        foreach (Step row in allSteps)
        {
            cmd.CommandText = "INSERT INTO Step (StepID, EWPID, StepNum, Description, MTOQty, ToDateQty, CraftCode, OriginPkg, SF01, SF10, Updated) VALUES " + 
                "(@param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param9, @param10, @param11);";
            cmd.Parameters.Add(new SQLiteParameter("@param1", row.StepID));
            cmd.Parameters.Add(new SQLiteParameter("@param2", row.EWPID));
            cmd.Parameters.Add(new SQLiteParameter("@param3", row.StepNum));
            cmd.Parameters.Add(new SQLiteParameter("@param4", row.Description));
            cmd.Parameters.Add(new SQLiteParameter("@param5", row.MTOQty));
            cmd.Parameters.Add(new SQLiteParameter("@param6", row.ToDateQty));
            cmd.Parameters.Add(new SQLiteParameter("@param7", row.CraftCode));
            cmd.Parameters.Add(new SQLiteParameter("@param8", row.OriginPkg));
            cmd.Parameters.Add(new SQLiteParameter("@param9", row.SF01));
            cmd.Parameters.Add(new SQLiteParameter("@param10", row.SF10));
            cmd.Parameters.Add(new SQLiteParameter("@param11", row.Updated));
            cmd.ExecuteNonQuery();
        }
    }
}

allSteps is an array of objects that coincides with the columns from SQL I'm getting:

public class Step
{
    public int StepID { get; set; }
    public int EWPID { get; set; }
    public int StepNum { get; set; }
    public string Description { get; set; }
    public double MTOQty { get; set; }
    public double ToDateQty { get; set; }
    public string CraftCode { get; set; }
    public string OriginPkg { get; set; }
    public string SF01 { get; set; }
    public string SF10 { get; set; }
    public string Updated { get; set; }
}

Is there a way to dump an entire Select statement into a SQLite table without the looping?

Aucun commentaire:

Enregistrer un commentaire