Multithread Inserts

Nov 1, 2014 at 11:49 PM
Edited Nov 1, 2014 at 11:50 PM
Hi there,

I'm trying to use DBreeze but I'm getting very slow inserts.
To speed up I tried to insert in a new thread since I don't care so much of when it completes.

I did something like the code below but I'm getting the error:

{"Database is not operable, please find out the problem and restart the engine!"}

Code:
public override bool Insert(string tablename, string key, string data, bool commit)
{
            new Thread(() =>
            {
                Thread.CurrentThread.IsBackground = true;

                int retriesallowed = 10;

                BeginPoint:

                try
                {
                    using (Transaction tran = dbreezeengine.GetTransaction(eTransactionTablesLockTypes.EXCLUSIVE, new string[] { tablename }))
                    {
                        tran.SynchronizeTables(new string[] { tablename });
                        tran.Insert(tablename, key, data);
                        if (commit) tran.Commit();
                    }
                }
                catch (Exception ex)
                {
                    Trace.WriteLine(this.GetType().Name + " Error: " + tablename + " -> " + key );
                    #if DEBUG
                      throw;
                    #endif

                    retriesallowed--;

                    if (retriesallowed > 0)
                        goto BeginPoint;
                }
                
            }).Start();
  
            return true; 
        }
the var retriesallowed is a tentative of solving without sucess...

Also, how can I have sure that all is commited?

Thanks
Ricardo
Coordinator
Nov 2, 2014 at 10:35 AM
What is this function for?
Universal function for bulk and single inserts designed to be used for every table?
If yes, why for bulk inserts do you open transaction every time?
If it's universal... does it mean that you want to insert only in one table per transaction?
Why do you run the body in the new thread and try to return boolean value?
Why do you use EXCLUSIVE lock?
Why do you use EXCLUSIVE lock with SynchronizeTables?

This code is not right from DBreeze point of view.
Right would be to create simple, not universal! functions for database concrete operations.
and call them from try catch. If Commit fails it throws an exception:

void Call()
{
try
{
InsertManyEntities(Dictionary);
}
catch()
{
//Insert failed
}
}


void InsertManyEntities(Dictionary<key,value> entityX)
{
using(var tran = engine.getTransaction())
{
tran.Synchronizetables(mytables);
tran.insert(1)
tran.insert(2)
//whatever concernig entityX
tran.Commit();
}
}
Nov 2, 2014 at 11:40 AM
Hi again,
Thanks for answering.

This is one kind of "interface" to DB providers. I'm trying to include DBreeze as a valid provider.

The app uses several tables so the function must be a generic one.

This short piece is an actual implementation to DBreeze provider and all providers must be 100% sucessful on DB tests. The tests include a 100 inserts one at time. Normal for app that uses a DB to actual stores data. The "solution" of insert without commit every insert doesn't follow app pattern and not safe.

The EXCLUSIVE & SynchronizeTables & retriesallowed was attempts of solving thread issue. Without the thread it works but only less that 1 insert / second.... very odd!
Since the reading of the DB happens very later than the insert, the speed of insert is not relevant but needs not block app (so I create a thread).

I forgot to mention that the key is a 4 chars string and the data size is 2000 chars.

My machine is a i7 920 with 12GB so not a machine constraint but the goal it's to work on plain PC.

thanks
Ricardo
Coordinator
Nov 2, 2014 at 6:42 PM
Edited Nov 2, 2014 at 8:13 PM
Hi,

In your code you open transaction and close transaction
Decision to commit or not to commit inside of this transaction is a parameter?!

When transaction is closed (by tran.Dispose or automatically in the end of using statement) DBreeze will automatically rollback all not committed operations. So your example should not work correctly and will call a mass of physical rollbacks chaining big processing time and incorrect results.

You can open transaction outside the Insert-Procedure and give it to Insert-Procedure as a parameter.

Different threads can work with different transactions who, in turn, can possibly work with the same table. Such case must be synchronized by tran.SynchronizeTable command. Transaction can't be supplied from one thread to another by DBreeze rules. All this can be found in docu.

Try to work with DBreeze yourself, before making DB provider based on it.
Approaches can be different in compare with other databases.

Dbreeze can be expressed very simply.

Either you insert batches or just one record into 1-N tables.
Use this pattern
try{
using(var tran = engine.getTransaction()) 
{ 
tran.SynchronizeTables(mytables); 
//Batch or simple inserts
tran.insert(1) 
tran.insert(2) 
//...other db operations

tran.Commit(); 
} 
}catch{}
use such simple scheme and DBreeze will help you in coding.

Good luck.