Monday, November 17, 2008

Comparison between SQLite and H2 Databases

I found some article that says SQLite JDBC is slower than other DBMS. According to this source, SQLite took more than 11s for 100 insertions. This result is quite strange for me, and I guess no tuning option for SQLite is used in that experiment.

So, I wrote a simple test code to confirm the performance between SQLite and H2 databases. To the best of my knowledge, H2 is the fastest DBMS in the ones purely written in Java. Here is my test code.  In short, this code is optimized for insertions; using no transactions (a set of insertions is wrapped in a single transaction) and no locks. 

Tuning options for disabling lock acquisition:
SQLiteJDBC: pragma synchronous=off
H2: SET LOCK_MODE 0

For 1,000,000 insertions of simple records, I got the following results:
SQLite JDBC: 198.7 sec.
H2:    365.6 sec. 

If the above tuning options are not set in SQLite, each insertion acquires a file lock on the database file and creates an journal (log) file, so the performance will be significantly slower than the above result.

Although I can confirm SQLite is not slow, I have to admit SQLite JDBC driver has some overhead due to the interaction between native C codes and Java through JNI. When I used the command-line client of SQLite, it only took almost 100 sec for the same number of insertions through the import command of SQLite.

Ah, well, ... it's the nature of Java, which cannot beat sophisticated C programs in terms of performance.

48 comments:

Kaylee Brown said...

I wonder how you became so capable to write such interesting and informative articles, any way you are one of the best bloggers in my eyes, Keep it up. computer network assignment help

Thomas More said...

Hmm, my younger brother is a computer science student. I think he will find this post helpful. I am sharing the link with him and then I have to look for dissertation presentation writing services for my marketing dissertation presentation. Ah, I wish I had taken computer science as well. This post looks interesting but sadly, I can’t understand it.

Dim4ksan said...

I spent a long time looking for a woman who would be supportive and helpful to me. But, sadly, I could not locate such a girl in my nation. It was a period when I truly needed a woman's help. Then I came upon an article regarding ukrainian women for marriage. I was interested in the prospect, so I started looking online. I didn't get very far, but with these guys, you may find a woman who will love and support you. I wish to suggest these gentlemen to everyone; they will not disappoint you and may perhaps alter your life.

tomassamanter said...

Posting a essay writer org reviews as a college application might be exciting, but it also needs a great deal of effort, focus, and study. This type of essay writing requires a lot of attention and time. A well-written college essay may leave a lasting impression on the admissions staff of the university to which you wish to apply.

Tom said...

This is soo cool! So dont be shy adn check thsi cool serivec for essyand hoewrmj and be rady for wins! Enjoy your day and be raedy for the english essay writer and have fun! Enjoy your day!

Roman Davis said...

These two types of tests so that we may be able to check the students' ability. I like this way of testing because we can easily read about mental health, and then the teachers will teach them according to their cognitive ability. dissertation writing service

Michael said...

You can customize your photos in #yearinreview2016 and choosing your friends by scrolling down. Assignment writing service UK.

Unknown said...

The Car paint protection shields against the effects of the suns UV rays; acid rain, sea and road salt and provide a high glossCar Paint Protection Dubai finish which retains its appearance wash after wash. With regular Conserver maintenance you may never need to polish your car again for the duration of the guarantee. Car Paint Protection Dubai

Mobile Car Wash Ottawa said...

Looking to get your car detailing done but don't know where to turn? Look no further than envirosteam! We are Ottawa's top car detailing company and we are here to take care of all your detailing needs. From car washesCar Detailing Ottawa to full car detailing, we have you covered. We also offer a free consultation to help you find the perfect solution for your specific needs. Contact us today and let us take care of everything!

jeeniehogwartz said...

Welcome to the one and only truffleers! We are a chocolate lover's paradise, where you can indulge in the finest chocolates from all over the world. Our selection includes truffles from some of the most famous and iconic chocolate manufacturers, such as Godiva, Lindt, Ferrero and more. We also have a wide range of seasonalBuy chocolate truffles Dammam flavors that are perfect for every occasion. Whether you're in the mood for a classic truffle or something new and exciting, we have you covered. So why wait? Come and explore our heavenly collection today!

Michael Alex said...

Because SQLite (by default) waits until the data is safely recorded on the disc surface before completing a transaction, transaction speed is restricted by disc drive speed. Philosophy Essay Writing Help.

Ramma Foundation Repair said...

If you're in need of professional Edmonton foundation repair , look no further than Rammafoundation. We offer the best in quality and service to make sure that your foundation is in the best possible condition for your daily life. With our team of experienced professionals, we'll take care of everything from removal and replacement to color correction and fine-tuning. Trust us to take care of your foundation - it's what we do best!

Real estate advisor said...

Thanks for your article! My friend recommended using one website to get writing tips on how to write reflective essay outline perfectly and I decided to visit this. I didn't regret it as I got very quality writing support with my paper.

Real estate advisor said...

Well-written article! Get unique quality writing support from our american essay writing service in a very short time. Make sure it will be the right choice by reading a feedback page.

jeeniehogwartz said...

Looking for a delectable chocolate treat that will tantalize your taste buds? Look no further than our truffleers! Our truffleers handcraft some of the most tantalizing chocolates in Jeddah, from dark and indulgent truffles to Buy chocolate gift Riyadhdelicate white chocolate ones. Whether you're in the mood for a sweet snack or a special treat, our truffles are sure to please. Come and sample our delicious chocolates today!

Michael Wade said...

H2 database is highly scalable and can handle large volume of data efficiently whereas SQLite tends to lose its performance with increase.My friend who wrote international commercial law dissertation topics, he is also wrote on "Comparison between SQLite and H2 Databases.

James William said...

People who are into web development or coding work can easily understand what is being said in this blog and how you can take my online class for me on any subject.

johnsmith said...


If you cancel a monthly Amazon Prime membership,we will refund your full membership fee only if you and your account Please check more information : -
Amazon Prime refund

dry bulk trucking said...

for earthquake resistance. In order to treatCEMENT TREATED BASE your base and make sure it complies with all current codes and standards, we use the most up-to-date technology and equipment. Make an appointment

JacobHarman said...
This comment has been removed by the author.
Essien said...

I am impressed; I rarely come across information. You definitely hit the nail on the head with your content. Thanks so much for sharing. is gombe state poly post utme form still on sale

Laurie A. Williams said...

Their test results provide valuable insight into the performance of SQLite JDBC and H2, and it is clear that results depend on specific use cases and optimization options.
Overall, your blog post is a great resource for anyone interested in SQLite JDBC and H2 performance, and I look forward to reading more from you in the future.help with my online class

Kadine Alves said...

I'm curious how you developed the ability to write such interesting and informative articles; in any case, you're one of the best bloggers in my opinion; keep it up. assistance with computer network assignment. Pay someone to do my online class

Anonymous said...

Checking text errors manually is not productive right now, it will be much faster and more useful to use this free online service that will do your job in minutes. Just text copy and paste, click a button, and you're done. Quick result and you are satisfied because you saved your time. Read it and want to try it, then get started, everything you need is already here.

DarenTill said...

Not so long ago I started using the sentence type identifier in practice and I can tell you that I am very pleased with the results of his work! This tool helps to identify various errors. These can be incorrectly used punctuation marks, grammatical errors, word errors and many others. Plus, the tool is completely free! Good luck!

Anonymous said...

Looking for a quality active voice generator? Then I strongly recommend that you visit this site, where you are offered a high-quality word corrector to correct errors and more, it also detects all types of defects in the structures of numerous types of sentences, you can see all the advantages of the corrector right on the same site!

WaldoEffertz said...

Hello! AI is often used to work with the recognition of human emotions and further work with this information. In order for your AI to have the online sentiment analysis function as correctly as possible, our specialists in describing the necessary information for training will be able to build the learning process according to your requirements and best practices

Carmelia B. Wood said...

SQLite JDBC is a popular database management system used by many developers and businesses. However, its performance when it comes to insertions is slower than that of the H2 database. This can be attributed to the fact that SQLite JDBC does not have the same level of optimization as H2 and thus takes longer to execute insertion queries.

This can be a problem for developers who are looking for fast and efficient ways of inserting data into their databases. Fortunately, there are ways to improve the performance of SQLite JDBC when it comes to insertions. For example, using prepared statements or batching multiple queries together can help speed up the process significantly. Additionally, making sure that indexes are properly configured and optimized can also help Do My Course Online For Me improve insertion speeds.

NathanPass said...

Hello! If your company needs professional help in text annotation and you want to hire a reliable specialist, then I recommend you contact our outsourcing company! Our Mobilunity-BPO agency has over 10 years of experience with document annotation. Partnering with us guarantees you fast results!

hellatwinkle said...

SQLite may not be the fastest DBMS in certain scenarios, it can still be a suitable choice for smaller projects or applications. Additionally, using high-quality hardware and optimizing the database design can also improve performance. And, for your information, legrand products abu dhabioffers a wide range of electrical and digital solutions in Abu Dhabi.

DmytroPortnoy said...

Is your business experiencing difficulties with annotations that are hindering growth? An online data labeling service that offers a team of top-tier experts may be worth considering. You can tailor your team to your specific needs and ensure that they are proven performers. The service like computer vision labeling includes various services, such as data tagging and image tagging.

Alex Hales said...

Cricut machines are DIY cutting machines that help ease your work. Various machines are produced by Cricut, such as Cricut Explore, Cricut Maker, and so on. Cricut also produces a heat press to transfer the designs onto T-shirts, hats, caps, etc. However, you still need a Cricut cutting machine to create your design. It allows you to cut hundreds of materials, including vinyl, cardstock, iron-on, and fabrics. Start using the Cricut machine today by going to cricut.com/setup.

Lina Harshley said...

When comparing SQLite and H2 databases, it's important to consider not only their features but also the affordable hosting options. Both databases offer powerful functionalities, but choosing a cheap dedicated server hosting solution can significantly impact your overall expenses.

Chris Ray said...

The wallet group unveiled the widely used and efficient web browser plugin MetaMask Chrome. MetaMask Chrome

menzonee said...

The discussion about SQLite JDBC's speed is worth exploring in the world of databases. Just as you delve into the intricacies of technology, take a moment to explore Facial Services For Men In Mississauga. It's about balancing the need for speed with the importance of personal grooming, ensuring you excel both in code and appearance.


harperpaul said...

It's always fascinating to hear from the voice of a seasoned Xerial developer. The Xerial project has been a game-changer in the field of data management and storage.
divorce laws in virginia

combinedpump said...

The assertion that SQLite JDBC is slower than other DBMS sparks a conversation on database performance trade-offs. In Dammam, where industrial efficiency is paramount, consider the role of transfer pumps for fluid handling precision. Just as the debate on database speed unfolds, reliable transfer pumps in Dammamensure optimal fluid transfer rates, contributing to seamless operations in various industries.

HelanHelan said...

Discovering an article claiming that SQLite JDBC is slower than other DBMS sparks an interesting conversation about database performance. While the debate continues, let's add a touch of energy to the discussion with the aromatic richness of coffee beans dubai Just as the tech world debates the merits of different systems, a well-brewed coffee blend adds a layer of stimulation to our contemplation. Here's to the complexities of database management and the aromatic pleasures of quality coffee, creating a perfect blend of analytical thinking and sensory delight for a more invigorating tech discourse.





Micheal Jackson said...

Grado Inspired's affrimation cards
are a game-changer in fostering a positive mindset. The website, www.gradoinspired.com, offers a seamless shopping experience, making it easy for individuals to access these impactful cards and integrate them into their lives.

hastencatering said...

Surprised to learn that SQLite took more than 11 seconds for just 100 insertions, highlighting the importance of optimizing database operations for efficiency! And for those Fork and knife catering services in Monahans Texas ensure your events run smoother than database transactions with Fork and Knife Catering services. Delightful bites that are as efficient as they are delicious! #DatabaseEfficiency #MonahansCatering #TechAndTaste #OptimizeOperations





Micheal Jackson said...

Traveling smart means considering Stone Well Financial for their budget-friendly 미국 의료 보험
options.

Micheal Jackson said...

Experience healthcare excellence with Vesta Care
, your trusted partner for a range of services. From STD tests to lab tests at home, Vesta Care's commitment to confidentiality and accuracy makes them the go-to destination for quality healthcare.

toniaa said...

Philly sports shirts are a timeless classic that never goes out of style. Whether you're rocking a retro design from decades past or sporting the latest gear from the current season, these shirts are a wardrobe essential for any Philadelphia sports enthusiast. With their bold colors and iconic logos, they're sure to make a statement wherever you go. phillysportsshirts.com/

toniaa said...

Tech enthusiasts, lend me your ears as I share my latest tech triumph – a refurbished gadget procured with meticulous planning and managed smartly. This device, though pre-owned, epitomizes excellence in functionality and serves as a testament to the rewards of strategic decision-making in our tech endeavors. Let's navigate the tech landscape with wisdom and a dedication to choosing smartly. https://smartimobile.com

Laverne R. Atwood said...

When establishing an Auto Parts Store UK, choosing the right database is crucial for efficient operations. SQLite and H2 are both popular choices, each with distinct advantages. SQLite is renowned for its simplicity, perfect for small-scale applications due to its serverless architecture and zero configuration. Conversely, H2 offers more advanced features like in-memory databases and built-in clustering, ideal for larger-scale operations demanding higher performance and scalability. Evaluating factors such as size, concurrency, and performance needs will help determine which database suits the unique requirements of an Auto Parts Store in the UK effectively.

toniaa said...

Had my car's paint protection film done by BAY AREA PAINT PROTECTION FILM, and it turned out great. The service was quick, and the staff was very friendly.

John Fei said...

This article effectively breaks down the differences between web app vs mobile app, discussing their respective features, benefits, and use cases. The comparison is clear and concise, making it easy for readers to understand which type of app would suit their needs best. Overall, a helpful resource for those looking to develop an app.

toniaa said...

Say goodbye to credit check worries with MyPayvantage! Get the latest iOS and Android devices with fast approval and no-credit payments. Shop now and enjoy your new mobile phone without any stress.