Increase data insert performance

111 views Asked by At

We have created a code that loops in to CosmosDb records and inserts values to Sql db. But in the process, the data insertion perfomance is very slow. please help with suggestions, here is the code. This process is taking more than 4 hours for inserting a data of 4000 rows.

namespace PushNotificationUserInsert
{
    class Program
    {
        static void Main(string[] args)
        {
            Task.Run(async () =>
            {
                #region Variables
                var endpoint = ConfigurationManager.AppSettings["DocDbEndpoint"];
                var masterKey = ConfigurationManager.AppSettings["DocDbMasterKey"];
                var connetionString = ConfigurationManager.AppSettings["SQL_connetionString"].ToString();
               // var useremail = "\"\"";
                string path = @"xxxx.txt";              
                string[] useremails = File.ReadAllLines(path);
                List<string> It = useremails.ToList();
                var newIt = It.Distinct().ToList();
                var channel = "\"msteam\"";
                var cnn = new SqlConnection(connetionString);
                var cnnInsert = new SqlConnection(connetionString);
                string query = null;
                cnn.Open();
                foreach (var email in newIt)
                {
                    query = "SELECT * FROM c where c.document.bot_js.mail = \"" + email + "\" AND CONTAINS(c.id," + channel + ", true)";
                    dynamic responses = "";
                    JSONModel.Rootobject records = null;
                    string conversationId = "";
                    //string email = "";
                    #endregion
                    #region Reading data from SQL
                   // cnn.Open();
                    SqlDataReader dataReader;
                    String Output = "";
                    SqlCommand command = new SqlCommand(@"SELECT [ConversatonReferenceJson] FROM [dbo].[ConversationReferences] where emailid like ''", cnn);
                    //command = new SqlCommand(sql, cnn);
                    dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        Output = dataReader.GetValue(0).ToString();
                        records = JsonConvert.DeserializeObject<JSONModel.Rootobject>(Output);
                    }
                    dataReader.Close();
                    dataReader.Dispose();
                    #endregion
                    string[] readText = File.ReadAllLines(path);

            //List<string> It = readText.ToList();
            //var newIt = It.Distinct().ToList();           
            //foreach (string email in newIt)
            //{
                Console.WriteLine(email);
                try
                {
                    #region reading data from Consmos DB
                    using (var client = new DocumentClient(new Uri(endpoint), masterKey))
                    {
                        responses = client.CreateDocumentQuery(UriFactory.CreateDocumentCollectionUri("Cosmosdbname", "cosmosdbtablebame"), query).ToList();
                    }
                    #endregion
                    #region Looping throught each cosmos DB records and insert value in SQL
                    foreach (var response in responses)
                    {
                        conversationId = response.id.Remove(0, 26);
                        records.conversation.id = conversationId;
                        cnnInsert.Open();
                        SqlCommand commInsert = new SqlCommand("INSERT INTO [ConversationReferences] VALUES " +
                        "(@ChannelId, @UserId, @ConverJson, @ID, @EmailID)", cnnInsert);
                        commInsert.Parameters.AddWithValue("@ChannelId", "xxxx");
                        commInsert.Parameters.AddWithValue("@UserId", "tobedeleted");
                        commInsert.Parameters.AddWithValue("@ConverJson", JsonConvert.SerializeObject(records));
                        commInsert.Parameters.AddWithValue("@ID", "tobedeleted");
                        commInsert.Parameters.AddWithValue("@EmailID", email);                          
                        commInsert.ExecuteNonQuery();
                        cnnInsert.Close();
                        Console.WriteLine("records updated for " + email);
                    }
                    #endregion
                }
                catch (Exception ex)
                {
                    cnnInsert.Close();
                    throw;
                }
                finally
                { cnnInsert.Close(); }
                Console.ReadKey();
            }
        }).Wait();
    }
}
 
0

There are 0 answers