# Retrieve data from database and bulk index them-Sql Server

**URL:** <https://discuss.elastic.co/t/retrieve-data-from-database-and-bulk-index-them-sql-server/49023>\
**Category:** Elasticsearch\
**Created:** [May 3, 2016, 8:09am UTC](https://discuss.elastic.co/t/retrieve-data-from-database-and-bulk-index-them-sql-server/49023 "2016-05-03T08:09:56Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ASN](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/asn/32/10535_2.png) [@ASN](https://discuss.elastic.co/u/ASN)\
**Post date:** [May 3, 2016, 8:09am UTC](https://discuss.elastic.co/t/retrieve-data-from-database-and-bulk-index-them-sql-server/49023/1 "2016-05-03T08:09:56Z")

</div>

Hi all,

I have some records in SQL Server management studio and I want to index them to my ES index.  
At present I'm indexing them individually which is taking a long time. I would like to bulk index them. I tried using the Bulk Descriptor it is throwing an error saying \> the object to index, if id not set manually it will be inferred from the object

below is my code.

```
string connectionString = ConfigurationManager.ConnectionStrings["MyDbConn"].ToString();
                using (cn = new SqlConnection(connectionString))
                {
                    cmd = new SqlCommand();
                    cmd.CommandText = ConfigurationManager.AppSettings["dbName"];
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = cn;

                    cn.Open();
                    
                    //to index the students response into the ES 
                    try
                    {
                        client = ConfigSettings.connection();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message.ToString());
                        throw;
                    }

                    reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            var descriptor = new BulkDescriptor();
                            descriptor.Index<Class1>(b=>b.Document(new Class1
                            {
                                Id = Int32.Parse(reader[0].ToString()),
                                Title = reader[1].ToString(),
                                BodyContent = reader[2].ToString()  
                            }, z => z.Refresh()));
                        }

Client.Bulk(descriptor);

```

**Client settings**

```
        configvalue1 = ConfigurationManager.AppSettings["url"];
        var pool = new SingleNodeConnectionPool(new Uri(configvalue1));
        var defaultIndex = "dbtrial";

        settings = new ConnectionSettings(pool)
         .DefaultIndex(defaultIndex)
         .MapDefaultTypeNames(m => m.Add(typeof(Class1), "records"))

        client = new ElasticClient(settings);
        client.createIndex(defaultIndex);

```

Is there any way other than descriptor to index thousands of documents in a efficient way?

Thanks In Advance

---

<div class="post-metadata">

**Author:** ![ASN](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/asn/32/10535_2.png) [@ASN](https://discuss.elastic.co/u/ASN)\
**Post date:** [May 4, 2016, 2:41am UTC](https://discuss.elastic.co/t/retrieve-data-from-database-and-bulk-index-them-sql-server/49023/2 "2016-05-04T02:41:35Z")

</div>

I tried something like this and it is working fine for indexing(it is taking between 3300ms to 4500ms for indexing 1000 [documents.So](http://documents.So) I'm not sure if this can be reduced further using any other method). I'm not sure of the performance if there are some millions of records.

```
                   List<Class1> feedlist = new List<Class1>();                        
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        class1 c = new Class1
                        {
                            Id = Int32.Parse(reader[0].ToString()),
                            Title = reader[1].ToString(),
                            BodyContent = reader[2].ToString()  
                        };
                        feedlist.Add(c);
                    }
                    client.IndexMany(feedlist, "dbtrial", "records");

```

Any better methods for indexing millions on records can be suggested. TIA

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:54pm UTC](https://discuss.elastic.co/t/retrieve-data-from-database-and-bulk-index-them-sql-server/49023/3 "2017-07-05T22:54:09Z")

</div>