Multithread + C# + Performance + SQLite

Question

I have Windows Form application.
(C# with visual studio 2010, Framework : 4.0)

Database: .db (file database), connection object through SQLite

Thread th = new Thread(() => database(node, xNodetcname, xNodesqlquery1, xNodeHint, e, inXml));
th.Name = thread;
th.Start();

Above code create each thread and processing parallel on database() function.
Each thread having one SQL Query which fetching data from database.

While I not use Multithreading the performance is better but when I use Multithreading the performance is down.

Example:

Without Multithreading 3 query processing time = 1.5 minutes
With Multithreading 3 query processing time = 1.9 minutes.

My aim is to reduce the processing time of query.

TomTom · Accepted Answer · 2014-06-03 08:30:02Z

6

Then generally stay away from threads.

Some basic edutation: In most cases database performance is limited by IO, not CPU. IO can partialyl be mitigated by using a lot of memory as buffers, hence large database servers have TONS of memory.

You run a small leightweight databasde. It likely is not running on a database server level hardware or a SSD - so it has a IO problem. Perforamcne will be limited by IO.

Now multiple threads make sure that the IO side (hard disc) is inefficieent, especialyl because a winform app is not running normally on a high end IO subsystem.

Ergo: if you want a faster query then:

Optimize the query. MIssing index?
Get proper hardware and / or upgrade to a heaver setup. A SSD is a great help here.

Do not use multi threading - try to solve it on SQL level but accept that this may not be possible. THere is a reason companies still actually use real database servers to handle large data amounts. And SQLite may not be a good option - no chance saying it is or not as you totally ignore that side in your information.

answered Jun 3, 2014 at 8:30

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andras Zoltan Over a year ago

+1 - or potentially look at doing more work in the app to pre-process the data all on multiple threads, then have one thread only that is repsonsible for adding to the database.

Mario Stoilov · Accepted Answer · 2014-06-03 08:33:56Z

2

There are a few things you have to take into consideration here:

Is your db connection in Multi-Threading mode(As described in this document)?
Is the database engine suitable for multithreading (hint, SQLite is not, see @TomTom answer)
Are you using a thread pool(you are not) or are you initializing a new thread every time (which is rather slow)

answered Jun 3, 2014 at 8:33

Mario Stoilov

3,4475 gold badges34 silver badges51 bronze badges

1 Comment

Hardik Over a year ago

Thanks for providing link

Collectives™ on Stack Overflow

Multithread + C# + Performance + SQLite

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related