How to increase speed for insert bulk data in to table ?

Question

0.00/5 (No votes)

See more:

Hi,
We insert more than 500000 rows from datatable to sql table, before using for loop it take more time, and we chnage bulk insert still slow and also throws session expiry.

So any other way to insert in one shot to table ?

Before i use this for loop, it takes 30 mins and above

VB

For Each objDR As DataRow In datas.Rows
           sqlcmd.CommandText = "insert into [RRights]([RoleId],[Database],[Item Type],[Email],[Print],[Design Profile],[Check In],[Design File],[Delete/Rename File],[File Output],[Create Version],[View Version],[Reports],[Management],[ShareFile],[Annotation],[RenameAll]) values ('" & stringRoleID & "',N'" & objDR("Path") & "',N'Folder','False','False','False','False','False','False','False','False','False','False','False','False','False','False')"
          sqlcmd.ExecuteNonQuery()
     Next

What I have tried:

VB

cmd.CommandText = "select RoleId ,Path as [Database] ,'Folder' as [Item Type]   from  DBFlow where ([Path] LIKE N'" & stringCabName & "\%') ORDER BY [Path] ASC"
        cmd.Connection = sqlcon
        da.SelectCommand = cmd
        da.Fill(datas)
        Dim sqlcmd As New SqlCommand
        sqlcmd.Connection = sqlcon
        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("FTConnectionString").ToString())
            cn.Open()
            Using copy As New SqlBulkCopy(cn)

                copy.ColumnMappings.Add("RoleId", "RoleId")
                copy.ColumnMappings.Add("Database", "Database")
                copy.ColumnMappings.Add("Item Type", "Item Type")

                copy.DestinationTableName = "RRights"
                copy.WriteToServer(datas)
            End Using
            cn.Close()
        End Using

Posted 22-Mar-18 21:55pm

Aravindba

Updated 25-Mar-18 17:19pm

v4

Add a Solution

Comments

F-ES Sitecore 23-Mar-18 5:41am

You can speed up your code by not using string concatenation, using sqlparams would probably be just as slow though so maybe try building your SQL using a StingBuilder that you initialise with a size big enough to accommodate the text.

Other than that you can remove any indexes, references, constraints etc on the table before you insert and add them back after. But ultimately what you're doing is going to be slow, there is no magic bullet beyond buy a better server.

Aravindba 23-Mar-18 5:58am

hi, same code work with 100 gb database, but now got 800 gb in that database. so where the problem ? in code or sql or server configuration ?

Ram Nunna 26-Mar-18 6:25am

You can pass datatable directly into database.
refer below link:
https://stackoverflow.com/questions/9075159/how-to-insert-a-data-table-into-sql-server-database-table

Aravindba 26-Mar-18 6:56am

pls read my question before reply, i tried both which i mentioned already, using bulk query and single line by line also.Both i got slowness.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2018-03-22T23:02:00

Building 500000 times the sql string command is a slow process and it is dangerous too.
You should Google about "SQL bulk insert", there is only 1200000 answers, you should find something useful.
-----

VB

sqlcmd.CommandText = "insert into [RRights]([RoleId],[Database],[Item Type],[Email],[Print],[Design Profile],[Check In],[Design File],[Delete/Rename File],[File Output],[Create Version],[View Version],[Reports],[Management],[ShareFile],[Annotation],[RenameAll]) values ('" & stringRoleID & "',N'" & objDR("Path") & "',N'Folder','False','False','False','False','False','False','False','False','False','False','False','False','False','False')"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]