Solved

Fast way to insert 21000 rows in a SQLite database with C#

Posted on 2009-05-05
9
655 Views
Last Modified: 2013-12-17
Hi all,

I need to import a text file in C# which contains 21000 rows. Each row contains 15 columns.

I tried different things, but it is extremely slow. This is the current code which is relevant:

SQLiteConnection _dbConnection = new SQLiteConnection("Data Source=smp.db;Version=3;New=True;Compress=True;");
_dbConnection.Open();
string[ ] lines = File.ReadAllLines(fileName);

foreach (string line in lines)
{
    string[ ] parts = line.Split(new string[ { ";" }, StringSplitOptions.None);

    sqlQuery = @"INSERT INTO testtable VALUES (" + parts[0] + "," +
                             parts[1] + "," +
                            "'" + parts[2] + "'," +
                            parts[3] + "," +
                            "'" + parts[4] + "'," +
                            parts[5] + "," +
                            "'" + parts[6] + "'," +
                            "'" + parts[7] + "'," +
                            parts[8] + "," +
                            "'" + parts[9] + "'," +
                            "'" + parts[10] + "'," +
                            "'" + parts[11] + "'," +
                            "'" + parts[12] + "'," +
                            parts[13] + "," +
                            "'" + parts[14] + "'); ";
    SQLiteCommand cmd = new SQLiteCommand();
    cmd.Connection = _dbConnectie;
    cmd.CommandText = sqlQuery;
    cmd.ExecuteNonQuery();
}

I have done the same thing before in vb.NET and with a msaccess database and that worked pretty fast.
if I leave out the execute part, it's done in about 1 second. Off course this is not surprising, but I just wanted to make sure it was an insert problem.

Does anyone have a suggestion?
0
Comment
Question by:4ice
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 1

Expert Comment

by:pras_gupta
ID: 24309035
You can try constructing all the insert statements into a script and then firing at once.
 http://www.vb-helper.com/howto_sql_script.html
 
0
 

Author Comment

by:4ice
ID: 24309244
I'm not sure if this is what you mean, but from the SQLite website I copied the following:

"
Multi-row INSERT a.k.a. compound INSERT not supported.
INSERT INTO table (col1, col2) VALUES ('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ...
"

I also tried:
    sqlQuery = sqlQuery + @"INSERT INTO testtable VALUES (" + parts[0] + "," +
                             parts[1] + "," +
                            "'" + parts[2] + "'," +
                            parts[3] + "," +
                            "'" + parts[4] + "'," +
                            parts[5] + "," +
                            "'" + parts[6] + "'," +
                            "'" + parts[7] + "'," +
                            parts[8] + "," +
                            "'" + parts[9] + "'," +
                            "'" + parts[10] + "'," +
                            "'" + parts[11] + "'," +
                            "'" + parts[12] + "'," +
                            parts[13] + "," +
                            "'" + parts[14] + "'); ";
But this hangs everything completely, and this is even without ExecuteNonQuery() (so only building the sqlQuery string)
0
 
LVL 9

Expert Comment

by:tculler
ID: 24309309
A fairly significant problem here is the modifiability of your code. Instead, using a StringBuilder in the System.Text namespace would probably be more efficient. Every String you make is stored in memory until the GC re-claims it, and due to the immutability of Strings, this can take a fairly long time. Obviously, inserting 21,000 rows can really become a long process. I'll give an example of my suggestion. Let me know if it doesn't work/is not what you're looking for.
static System.String GetInsertCommand( System.String myFilePath )

{

	const System.Char COMMA = ',';

	const System.Char APOS = '\'';

	const System.Char DELIMITER = ';';

	System.Text.StringBuilder bldr = new 

System.Text.StringBuilder("INSERT INTO testtable VALUES(");
 

         // Use a StreamReader instead of ReadAllLines;

         // ReadAllLines will eat up your memory pretty bad.

	using(System.IO.StreamReader reader =

		new System.IO.StreamReader

                  (System.IO.File.OpenRead(myFilePath)))

	{

		while(!reader.EndOfStream)

		{

			foreach(System.String str in

                                reader.ReadLine().Split(DELIMITER))

			{

				bldr.Append(APOS);

				bldr.Append(str);

				bldr.Append(APOS);

				bldr.Append(COMMA);

			}

		}

	}
 

	bldr.Append(");");

	return bldr.ToString();

}

Open in new window

0
 
LVL 1

Expert Comment

by:pras_gupta
ID: 24309340
I was taking about the second approach, instead of using + , try using StringBuilder.
http://msdn.microsoft.com/en-us/library/system.text.stringbuilder(VS.71).aspx
 
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:4ice
ID: 24309814
Although the StringBuilder is a better way to build my query, it seems to have no effect on the time.

The query itself is built in a split second, but after that I need to execute it and that's where it goes wrong.

An extra problem could be that my C# skills are below zero :)
0
 

Author Comment

by:4ice
ID: 24309890
Well my problem is solved.

I did not started a transaction before. Now I added a 'begin transaction' before the loop and a 'commit' after the loop and the entire 21000 rows are inserted in only 3 seconds!!

Thanks for your help anyway guys!
0
 
LVL 9

Expert Comment

by:tculler
ID: 24310054
Glad to hear it. I mainly suggest my method because it's much more manageable than the way I saw it otherwise implemented--the algorithm is dynamic. But, if you have need for it to not to be dynamic for whatever reason, my method would not be the wisest choice.

Anyways, hope we at least somewhat helped,

Nate
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 24515680
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Unrecognized Database Format 8 92
Error on Add method 1 38
Error in script 11 47
Vb. Net application freezes 9 30
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now