• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Which database is suitable for Java application which inserts at least 2500 records each day?

 
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I've been developing desktop application for my client ( a retail shop ) in Swing. So I don't want my client to install any extra software as I'm using MySQL now, where I have to install MySQL on clients system and all. I want my database should be packed with my application so though my client move application from one system to another he/ she need not to install any software for database access. Database should be stored in one file in a folder so If I move or run my app on other system I'll just run my app and database should be accessible.

I have to enter at least 2500 records per day. I need to keep 5 years records. so is there any database which can handle large database and can be packed with my application so I can use that rather than MySQL ?
 
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You didn't provide any details on the type of data you would be working with, whether you are only performing inserts, over what time period the 2500+ tranactions will occur, but you might want to consider using SQLite. It is a simple, embeddedable, no-configuration SQL database, with each database is contained in a single file.

The database files are portable across Linux, Windows, Android, MacOS, and many other operating systems, and there are libraries for a variety of programming languages/environments (including JDBC). With moderate desktop hardware, you should expect something 10 inserts per second, which is 100X what you stated (based on operation over an 8 hour period).

Update: Out of interest, I made some masurements on a Q6600-based Windows desktop using SQLite3 and 256 byte data to single column:
   7200RPM HDD: 11 inserts/sec with transaction rate = 1; 7,000 inserts/sec with transaction rate = 1000
   SSD: 300 inserts/sec with transaction rate = 1; 15,000 inserts/sec with transaction rate = 1000
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Other alternatives are H2, HSQLDB, and Apache Derby. And here you'll find a comparison of different popular embedded (lightweight) java databases.

Hope it helps!
Kind regards,
Roel
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'll perform 2500 to 3000 inserts operations everyday means within 12 hours and also perform select, update on those records. It is project for retail shop who has employees, who will deliver products to the customers so at the end of each month this application will calculate customer's bill and also salary of employees on the basis of how many product they delivered to the customers. Also have to generate reports to analysis shop sales in previous 5 years to understand whether sales growing or decreasing. Also have to calculate total product sales and their income of one year to show the income to their chartered accountant(It will be financial year i.e. April-March of any one year of previous 5 years).

Large records holding tables are only customersales_details and customersales_records. where customersales_details will have records of customer's delivered products on each day(will have each transaction details product wise) and in customersales_records total products delivered to customer.
These tables have cust_id varchar, prod_id float, prod_qty float del_date date and status boolean.

I really thank you so much for your help. The information you provided was really helpful but I'm not that good in choosing which database would be good for my app the information was technical, I'm not that good yet. I'm also going to develop web applications( Websites ) after few days for my clients does same condition apply to web application as desktop applications while choosing database ?. so can you suggest which one is good for above retail shop desktop project ? if any good link for learning the database which you will suggest, will be splendid.
thank you so much again Roel De Nijs
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
From a database perspective 3000 inserts each day is pretty low and way below the limits of SQLite.

This article compares a few different databases (MySQL, PostgreSql and SQLite) and lists a few advantages and disadvantages, so that might be useful.
 
Ron McLeod
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:From a database perspective 3000 inserts each day is pretty low and way beyond the limits of SQLite.


Did you mean way below rather than way beyond?
 
Ranch Hand
Posts: 624
9
BSD Java
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ganish Patil wrote:I'll perform 2500 to 3000 inserts operations everyday means within 12 hours and also perform select, update on those records. It is project for retail shop who has employees...

We have used Oracle, PostgreSql and MySql in Production.
Oracle is out of context here.
Both PostgreSql and MySql are performing well where more than 1,00,000 transactions per day are made in the application.
I have never used SQLite, so can not comment on that.

Well 2 things you need to think on.
1. Transactions are likely to increase in future.
2. You should have facility to purge data(regardless which database is used) otherwise performance will go down once there are huge amount of data in transaction tables.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
2500 records is a ridiculously small number of records that any database will be able to handle.
 
Bartender
Posts: 11497
19
Android Google Web Toolkit Mac Eclipse IDE Ubuntu Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:Other alternatives are H2, HSQLDB, and Apache Derby. And here you'll find a comparison of different popular embedded (lightweight) java databases.



Roel ++
Derby++ Embedded ++!
Apache Derby has been part of 'standard' java since 1.6. It is called Java DB
http://www.oracle.com/technetwork/java/javadb/overview/index.html
 
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here's the JavaDB (Derby) FAQ about limitations...there aren't really any worth worrying about for this project.

"
Java DB stores each base table and each index in a single file, so the data size limit is the file size limit of the JVM and OS on which it runs. Derby is coded against the Java 64 bit interfaces to access these files, so internally the maximum table size is approximately 2**64. The number of tables is only limited by the 64 bit length of table identifiers, for an approximate limit of 2**64 total tables. In practice, system resources such as disk space are a more likely source of size limitations. Java DB is tested on a database using over 350 GB of raw disk space with tables containing over 500 million tuples.
"
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Whichever database you choose, you also need to think about managing this data in the longer term. 2500 records per day * 5 years = around 4.5 million records after 5 years. So you will need to index your tables appropriately to support common queries, and you should probably think about when/how you want to archive data e.g. do you really want to be searching through transactions from 5 years ago when you only need to see yesterday's?
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ron McLeod wrote:

Roel De Nijs wrote:From a database perspective 3000 inserts each day is pretty low and way beyond the limits of SQLite.


Did you mean way below rather than way beyond?


Of course! Late night typo

I fixed it in the original post to avoid confusion. Thanks for mentioning Ron!
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you're looking for a SQL database that can be packaged as part of a Java JAR application or something similar, one option is Apache Derby. I use it as a local test in place of DB2 for a fairly complex system.

Since I'm more into hard-core DevOps these days, I might simply make a Docker image with the app and a full-blown MySQL/MariaDB or PostgreSQL server, assuming clients can run containers (or run in a container cloud like Amazon EBS). But it depends on the workload.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@ Tapas Chand yes I was asked to keep records of previous 5 years. This is my first project where I need to keep this much years records. I'm thinking to delete or copy and delete oldest year's records (If 2011 to 2015 years completed means if year 2016 started then delete or copy and delete records of year 2014) in some other backup database's tables. Thank you for your help
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Bear Bibeault I really had no experience of this, so was glad to come to know it can handle lot more than I needed.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maneesh Godbole I just knew Oracle, MySQL and I think Ms access (I used this in BCA projects ) but now at least I came to know names of this much Databases. Thank you for adding another one.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@ chris webster

do you really want to be searching through transactions from 5 years ago when you only need to see yesterday's?


No, (Assume application has records of year 2011 to 2015 ) If I want to calculate the total products delivered to a customer in a month of 2015 then It should not start searching from records of that customer of year 2011 to year 2015, rather It should start searching directly from year 2015 records. Do I need to use Index here ? If yes then, should I use Index on delivery date or customer Id ? I've never used Index before.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tim Holloway thank you, will read about Apache Derby
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I just used sqlite for small login program. It is nice I can move my project to any system by keeping it in my pen drive. Right now I'm using MySQL as database and will finish project in few days as 80 % is finished but will also try same project with sqlite.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bear Bibeault wrote:2500 records is a ridiculously small number of records that any database will be able to handle.



In fact, unless your database design is a total catastrophe, it should be possible to insert 2500 records in a few seconds.
 
Tapas Chand
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ganish Patil wrote:@ chris webster

do you really want to be searching through transactions from 5 years ago when you only need to see yesterday's?


No, (Assume application has records of year 2011 to 2015 ) If I want to calculate the total products delivered to a customer in a month of 2015 then It should not start searching from records of that customer of year 2011 to year 2015, rather It should start searching directly from year 2015 records. Do I need to use Index here ? If yes then, should I use Index on delivery date or customer Id ? I've never used Index before.



What Chris said was absolutely right.
Let us assume your table contains records from 2011 to 2015.
If I want to see yesterday's data, it will search through the whole 5 years' records to find yesterday's data.

As you said, it should not start searching from records from 2011 to 2015, it should search directly from year 2015.
Databases do not work like that (unless proper indexing is done). How do you expect your DB to know at which point 2015 records starts?

Index is a good thing which will enable your SELECT queries to avoid a FULL TABLE SCAN.
But creating index will require the clear understanding of the complete schema.

NOTE: Bad index can horribly lower the performance of database.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tapas Chand wrote:NOTE: Bad index can horribly lower the performance of database.



I don't think I've seen that (unless the index stats are seriously out of date, but that's a DBA issue). If the stats are up to date, then the generated plan should not use the index if it's worse than a full table scan.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is the table membersales_records structure and it's link link of table structure image ( I can't upload image in this post though I gave url of this image by clicking on img button here in menu )
which will have records of products delivered to the customers from assumed years 2011 to 2015, on which I'll perform search operations to calculate total products delivered to particular customer in certain period( From and To dates will be selected to search delivered products between these dates). Should I use Single column index on memb_id ? because I'll frequently use memb_id in select's where clause but this video tutorial says don't use index if rows don't have unique values. Or should I create index on membsales_transid which is unique in this table and auto incremented ? and will create a table which keeps membsales_transid range for year 2011, 2012... upto 2015 so I'll use that table to get starting and ending membsales_transid of years 2015 ( for example if I search for records of year 2015 ) so for database it will not have to search from 2011 as it has starting & ending membsales_transid, a column whose index it has, I can use membsales_transid in where clause which is unique & will also be used frequently to serach records. I dunno what I'm in now just trying to figure it out so need guidance...
This is video tutorial I refferred for Indexing

 
Tapas Chand
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:I don't think I've seen that (unless the index stats are seriously out of date, but that's a DBA issue). If the stats are up to date, then the generated plan should not use the index if it's worse than a full table scan.


I have few experiences of bad index in past.
By bad index I mean duplicate index or multiple index for same columns (1 index for 2 columns and 1 index for again for those 2 columns+1 more column)

Bad index will not affect performance of SELECT queries, but it will definitely affect performance of DMLs, because for each DML, database has to touch each index of that TABLE.
And sometimes it can really lead to a deadlock.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tapas Chand wrote:Bad index will not affect performance of SELECT queries, but it will definitely affect performance of DMLs, because for each DML, database has to touch each index of that TABLE.
And sometimes it can really lead to a deadlock.



Ah, I was only thinking in terms of SELECT performance.
Yes, pointless duplication of indexes, or indexes that simply aren't used, can cause performance issues.

I will say, a DML will only update an index if it involves a change to a column that forms part of that index. Obviously this is the case with any INSERT or DELETE, but not necessarily an UPDATE.
 
Tapas Chand
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:I will say, a DML will only update an index if it involves a change to a column that forms part of that index. Obviously this is the case with any INSERT or DELETE, but not necessarily an UPDATE.


Oh yes, you are absolutely right.
 
Tapas Chand
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ganish Patil wrote:This is the table membersales_records structure and it's link link of table structure image


From this, it looks MEMBSALES_DATE is a good candidate for index.

Well I am not sure how MySql works, but in Oracle a "date column without time" is a better candidate for index than a "date column with time".

You got a DATE type which does not store timestamp, only YYYY-MM-DD. So I think it will be good creating an index on that.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Eclipse IDE MySQL Database Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tapas Chand yes I'll have to write code to enter 46 lacks records and will try using that index on date and will check it's performance and will let you know the result and ask for help If faces any problem as it is my first real project for my first client. I want it to produce 100% accurate output with good performance. thank you
 
reply
    Bookmark Topic Watch Topic
  • New Topic