0

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();
    }
}
 
Richard
  • 106,783
  • 21
  • 203
  • 265
  • Suggestion: refactor your code into multiple functions, currently block of code is too hard to understand (`#region` is not a good way to structure code... it is just about the worst way). – Richard Nov 07 '20 at 15:55
  • Welcome to SO. Please do debug/profile your code first to narrow down the problem. Obviously no one but you can see if it's slow select, slow insert, a blocking issue, etc. Also, do remove all the fluff code not relevant to your actual question, see: https://stackoverflow.com/help/minimal-reproducible-example – Imre Pühvel Nov 26 '20 at 08:44

0 Answers0