TUniLoader and duplicate keys

[tommy]

Hello,

I am trying to load a huge amount of data into an SQLite database. I have tested both using a transaction with INSERT queries andTUniLoader. TUniLoader is about 50% faster so it is the way I'd like to do it.

However, I ran into the problem that an error ("columns x,y,z are not unique") is thrown if I try to insert data with a duplicate key. I want these data to be updated and continue. How can I handle that?

[MaximG]

Unfortunately, TUniLoader doesn't support the described functionality. The main destination of this component — maximum fast data insert. This is mainly achieved by specificities of working with every supported database. This is why implementation of additional functions for processing inserted data within the scope of TUniLoader will inevitably lead to performance loss, and it will barely differ from using the TUniQuery component on insertion.

[tommy]

So what is your recommendation? I need to insert about 1'000'000 rows into a table. Currently, I'm using a transaction. Is there a better way?

[MaximG]

UniDAC provides several approaches to insert large amounts of data when working with SQLite. However, not depending on the used approach, logic of data preprocessing before insertion or processing of possible errors during insertion should be implemented separately in your application.

[ertank]

Just an Idea.

-Create a temp table which is identical to your main table except it won't have a primary key but just a regular index with duplicates.
-Import your data into that temp table using UniLoader.
-When finished, deal with duplicate keys you have.

[MaximG]

We are glad to see the problem resolved. Please contact us concerning UniDAC usage.

[tommy]

Hello,

unfortunately, the prolem is not finally solved yet. It took me some time, but meanwhile I have done some changes to my database so that I can use TUniLoader for speed reasons. Unfortunately, there are still some problems.

First: do I have to do some commit after calling Load? Currently, I use the following code:

Code: Select all

conn := TUniConnection.Create (NIL);
conn.ProviderName := 'SQLite';
conn.Database := ...
conn.SpecificOptions.Values['ForceCreateDataBase'] := 'True';
conn.SpecificOptions.Values['DateFormat'] := 'yyyy-mm-dd';
conn.SpecificOptions.Values['TimeFormat'] := 'hh24:mi:ss';
conn.SpecificOptions.Values['UseUnicode'] := 'True';
conn.SpecificOptions.Values['EnableSharedCache'] := 'True';
conn.AutoCommit := TRUE;
conn.Connect;
loader := TUniLoader.Create (NIL);
loader.Connection := conn;
loader.TableName := 'xyz';
loader.CreateColumns;
loader.OnPutData := @OnSaveSunPath; // data is inserted there
loader.Options.QuoteNames := TRUE;
loader.Load;
loader.Free;
conn.Free;

After closing the database, it does not contain all data, but only 269000 out of 269208. TUniLoader itself does not contain a commit method so do I need conn.Commit after loader.Load? It works, but I get other problems when I do that.

Second: My application is multithreaded. According to this document Sqlite should by default operate in serialized mode which is thread-safe without any restrictions. For speed reasons, I wanted to create only a single instance of TUniConnection and use it within all threads. However, when concurrent database writes occur, I get frequent errors "cannot commit - no transaction is active". This happens regardless of whether or not I use manual commits.

So my question is: do I have to create a new instance of TUniConnection in each thread?

It would be kind if I could get some hints about what I'm doing wrong. Thank you.

[MaximG]

Bug with the UniLoader component when AutoCommit is True is fixed (UniDAC version 6.4.14 - 08-Sep-16). UniDAC is thread-safe, but its limitation is that you can't use the same connection (TUniConnection component) in several threads, so you have to use a separate connection in each thread.