Entity Framework performance after SqlBulkCopy

entity-framework performance sqlbulkcopy sql-server

Question

For certain of my tasks, I require some performance. Here is my code for accomplishing that task, however it actually takes too long to import data from Excel into my SQL Server database. Would you please provide some guidance for me?

   [WebMethod]
    public static string VerileriAktar(string alanlar, string gruplar, string shit)
    {
        ArtiDBEntities entity = new ArtiDBEntities();
        string[] eslesmeler = alanlar.Split(',');
        string[] grplar = gruplar.Split(',');

        DataSet ds = (DataSet)HttpContext.Current.Session["ExcelVerileri"];
        DataTable dt = ds.Tables["" + shit + ""];
        MembershipUser gelen = (MembershipUser)HttpContext.Current.Session["kimo"];
        Guid aa = (Guid)gelen.ProviderUserKey;


        List<tbl_AltMusteriler> bulkliste = new List<tbl_AltMusteriler>();
        List<tbl_AltMusteriler> ilkkontrol = entity.tbl_AltMusteriler.Where(o => o.UserId == aa).ToList();
        List<tbl_AltMusteriler> grupicin = new List<tbl_AltMusteriler>();
        List<tbl_OzelAlanlar> ensonatilacakalan = new List<tbl_OzelAlanlar>();
        List<tbl_OzelTarihler> ensonalicaktarih = new List<tbl_OzelTarihler>();

        // Datatable mın Kolon isimlerini değiştirdim.
        foreach (string item_col_name in eslesmeler)
        {
            string alan = item_col_name.Split('=')[0].Split('_')[1];
            string degisecek = item_col_name.Split('=')[1];
            if (degisecek == "")
                continue;
            dt.Columns[degisecek].ColumnName = alan;
        }



        #region verilerde

        foreach (DataRow dr in dt.Rows)
        {

            tbl_AltMusteriler yeni = new tbl_AltMusteriler();


            foreach (DataColumn dtclm in dt.Columns)
            {
                string gsm1 = "";
                if (dtclm.ColumnName == "gsm1")
                    gsm1 = dr["gsm1"].ToString();

                string gsm2 = "";
                if (dtclm.ColumnName == "gsm2")
                    gsm2 = dr["gsm2"].ToString();
                string ad = "";
                if (dtclm.ColumnName == "ad")
                    ad = dr["ad"].ToString();
                string soyad = "";
                if (dtclm.ColumnName == "soyad")
                    soyad = dr["soyad"].ToString();

                if (gsm1 != "")
                {
                    if (Tools.isNumber(gsm1) == false)
                        continue;
                    else
                    {
                        if (gsm1.Length > 10)
                            gsm1 = gsm1.Substring(1, 10);
                        yeni.Gsm1 = gsm1;

                    }
                }

                if (gsm2 != "")
                {
                    if (Tools.isNumber(gsm2) == false)
                        continue;
                    else
                    {
                        if (gsm2.Length > 10)
                            gsm2 = gsm2.Substring(1, 10);
                        yeni.Gsm2 = gsm2;
                    }
                }

                if (ad != "")
                    yeni.Ad = ad;
                if (soyad != "")
                    yeni.Soyad = soyad;
            }

            yeni.UserId = new Guid(aa.ToString());

            if (yeni.Gsm1 != "")
                grupicin.Add(yeni);               

        }
        #endregion



        bulkliste = grupicin.GroupBy(cust => cust.Gsm1).Select(grp => grp.First()).ToList();          

        List<tbl_AltMusteriler> yokartikin = bulkliste.Where(o => !ilkkontrol.Any(p => o.Gsm1 == p.Gsm1)).ToList();

        int saybakim = yokartikin.Count();

        DataTable bulkdt = new DataTable();
        if (yokartikin.Count > 0)
        {
            Type listType = yokartikin.ElementAt(0).GetType();             
            PropertyInfo[] properties = listType.GetProperties();
            foreach (PropertyInfo property in properties)
                if (property.Name == "UserId")
                    bulkdt.Columns.Add(new DataColumn() { ColumnName = property.Name, DataType = typeof(Guid) });
                else
                    bulkdt.Columns.Add(new DataColumn() { ColumnName = property.Name });
            foreach (object itembulk in yokartikin)
            {
                DataRow drbk = bulkdt.NewRow();
                foreach (DataColumn col in bulkdt.Columns)
                    drbk[col] = listType.GetProperty(col.ColumnName).GetValue(itembulk, null);
                bulkdt.Rows.Add(drbk);
            }
        }


        //var rowsOnlyInDt1 = bulkdt.AsEnumerable().Where(r => !bulkdt44.AsEnumerable()
        //        .Any(r2 => r["gsm1"].ToString() == r2["gsm1"].ToString()));

        //DataTable result = rowsOnlyInDt1.CopyToDataTable();//The third table

        if (bulkdt.Rows.Count > 0)
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ArtiDBMemberShip"].ConnectionString))
            {
                SqlTransaction transaction = null;
                connection.Open();
                try
                {
                    transaction = connection.BeginTransaction();
                    using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
                    {
                        sqlBulkCopy.BulkCopyTimeout = 240;
                        sqlBulkCopy.DestinationTableName = "tbl_AltMusteriler";
                        sqlBulkCopy.ColumnMappings.Add("UserId", "UserId");
                        sqlBulkCopy.ColumnMappings.Add("Ad", "Ad");
                        sqlBulkCopy.ColumnMappings.Add("Soyad", "Soyad");
                        sqlBulkCopy.ColumnMappings.Add("Adres", "Adres");
                        sqlBulkCopy.ColumnMappings.Add("Gsm1", "Gsm1");
                        sqlBulkCopy.ColumnMappings.Add("Gsm2", "Gsm2");
                        sqlBulkCopy.ColumnMappings.Add("Faks", "Faks");
                        sqlBulkCopy.ColumnMappings.Add("Telefonis", "Telefonis");
                        sqlBulkCopy.ColumnMappings.Add("Telefonev", "Telefonev");
                        sqlBulkCopy.ColumnMappings.Add("Eposta", "Eposta");
                        sqlBulkCopy.ColumnMappings.Add("DogumTarihi", "DogumTarihi");
                        sqlBulkCopy.ColumnMappings.Add("EvlilikTar", "EvlilikTar");
                        sqlBulkCopy.ColumnMappings.Add("TcNo", "TcNo");
                        //sqlBulkCopy.ColumnMappings.Add("Deleted", "Deleted");
                        sqlBulkCopy.WriteToServer(bulkdt);

                    }
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                }

            }

            entity.SaveChanges();
        }

        if (grplar.Length > 0)
        {
            List<tbl_AltMusteriler> guncelliste = entity.tbl_AltMusteriler.Where(o => o.UserId == aa).ToList();
            List<tbl_KisiGrup> kisigruplari = new List<tbl_KisiGrup>();
            foreach (tbl_AltMusteriler itemblkliste in bulkliste)
            {
                long AltMusteriIDsi = guncelliste.Where(o => o.Gsm1 == itemblkliste.Gsm1).FirstOrDefault().AltMusteriID;

                // Seçili Gruplara kişileri ekleme
                #region Gruplara ekleme


                if (grplar.Length > 0)
                {
                    foreach (string item_gruplar in grplar)
                    {
                        if (item_gruplar == "chkall")
                            continue;
                        if (item_gruplar == "")
                            continue;
                        if (item_gruplar == null)
                            continue;

                        tbl_KisiGrup yeni_kisi_grup = new tbl_KisiGrup()
                        {
                            AltMusteriID = AltMusteriIDsi,
                            GrupID = int.Parse(item_gruplar)
                        };
                        kisigruplari.Add(yeni_kisi_grup);
                    }
                }
                #endregion
            }

            List<tbl_KisiGrup> guncel_grup = entity.tbl_KisiGrup.Where(o => o.tbl_AltMusteriler.UserId == aa).ToList();

            List<tbl_KisiGrup> kisi_grup_kaydet = kisigruplari.Where(o => !guncel_grup.Any(p => o.AltMusteriID == p.AltMusteriID && o.GrupID == p.GrupID)).ToList();

            // Grupları Datatable çevirme
            #region Grupları Datatable le çevirme
            DataTable bulkdt2 = new DataTable();



            if (kisi_grup_kaydet.Count > 0)
            {
                Type listType = kisi_grup_kaydet.ElementAt(0).GetType();
                //Get element properties and add datatable columns  
                PropertyInfo[] properties = listType.GetProperties();
                foreach (PropertyInfo property in properties)
                    bulkdt2.Columns.Add(new DataColumn() { ColumnName = property.Name });
                foreach (object itembulk in kisi_grup_kaydet)
                {
                    DataRow drbk = bulkdt2.NewRow();
                    foreach (DataColumn col in bulkdt2.Columns)
                        drbk[col] = listType.GetProperty(col.ColumnName).GetValue(itembulk, null);
                    bulkdt2.Rows.Add(drbk);
                }
            }
            #endregion


            //Burada bulk insert işlemini gerçekleştiriyoruz...
            #region Grup Verileri BulkCopy ile birkerede yazdık

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ArtiDBMemberShip"].ConnectionString))
            {
                SqlTransaction transaction = null;
                connection.Open();
                try
                {
                    transaction = connection.BeginTransaction();
                    using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
                    {
                        sqlBulkCopy.BulkCopyTimeout = 240;
                        sqlBulkCopy.DestinationTableName = "tbl_KisiGrup";
                        sqlBulkCopy.ColumnMappings.Add("AltMusteriID", "AltMusteriID");
                        sqlBulkCopy.ColumnMappings.Add("GrupID", "GrupID");
                        sqlBulkCopy.WriteToServer(bulkdt2);
                    }
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                }

            }

            entity.SaveChanges();
            #endregion


        }

        return "ok";

    }

EDIT

The codeblock genuinely takes time if there are 70.000 or more rows of data.

List<tbl_AltMusteriler> yokartikin = bulkliste.Where(o => !ilkkontrol.Any(p => o.Gsm1 == p.Gsm1)).ToList();

My primary issue, I believe, is when I'm just inputting data usingsqlbulkcopy . After that, I was unable to get the identification ids; as a result, I obtained data to a generic list, tried to identify any new ids, and created a new list of groups. andsqlbulkcopy again. I need to import 65.000 rows, which takes around 10 minutes. Is there any method to do this?

1
0
3/22/2013 11:00:32 PM

Popular Answer

Your inquiry is somewhat general. I'd advise reading implications for EF performance. Also bear in mind that while EF transfers all data from the database to the client, it is not particularly designed for bulk tasks. If you want to do this for many entities and don't need or want to process them on the client, it adds a lot of overhead. (Note: Your code is too complex for me to have properly looked at it.)

0
3/22/2013 9:55:00 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow