Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

h2 default DECIMAL precision performance

When I create DECIMAL column with default precision setting, in H2 web console this column is defined as DECIMAL(65535, 32767), command "show columns from ..." gives me DECIMAL(65535).

Decimal data type in H2 is mapped to BigDecimal (from H2 documentation), but I'm not sure how H2 deals with it.

Are there any performance gains, if I use smaller precision?

like image 590
user1520270 Avatar asked Apr 29 '13 09:04

user1520270


People also ask

Is H2 Database fast?

Performance Comparison. In many cases H2 is faster than other (open source and not open source) database engines. Please note this is mostly a single connection benchmark run on one computer, with many very simple operations running against the database. This benchmark does not include very complex queries.

What is precision decimal?

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

Is H2 a good database?

H2 is a terrific database engine. After we solved a problem not related to H2, we switched from PostgreSQL and MySQL (both still supported) to H2. H2 became our primary database choice.

Does H2 support CLOB?

Clob ( java. io. Reader is also supported).


2 Answers

To get to know more about the DECIMAL datatype of H2, inspect the org.h2.value.ValueDecimal class that comes with the h2-x.y.z.jar.

Taking a closer look you'll see the default values are as you mentioned:

/** The default precision for a decimal value. */
static final int DEFAULT_PRECISION = 65535;
/** The default scale for a decimal value.     */
static final int DEFAULT_SCALE = 32767;

Looking closer in ValueDecimal:

private final BigDecimal value;

and org.h2.store.Data:

public Value readValue() {
    ...
    case Value.DECIMAL: {
        int scale = readVarInt();
        int len = readVarInt();
        byte[] buff = DataUtils.newBytes(len);
        read(buff, 0, len);
        BigInteger b = new BigInteger(buff);
        return ValueDecimal.get(new BigDecimal(b, scale));
    }

You can see that a DECIMAL is nothing more than a BigDecimal. That is, all the performance issues you'll face with java.math.BigDecimal, you'll face with DECIMAL.

If you are really into it, you can study the class further and see what exact role the precision/scale play.

If we go to the docs, all H2 say about DECIMAL data types and performance is:

The DECIMAL/NUMERIC type is slower and requires more storage than the REAL and DOUBLE types.

So they say that is a fact.

But since you are talking about performance, we can cut to the chase and do some tests. The test class' code is below, let's get to the output/results:

TYPE              INSERT time    COUNT() time   .db Size (kb)  
DECIMAL(20,2)     6.978          0.488          27958.0        
DECIMAL(100,2)    4.879          0.407          25648.0        
DECIMAL(100,80)   8.794          0.868          90818.0        
DECIMAL(60000,2)  4.388          0.4            25104.0        
DECIMAL(1000,900) 112.905        6.549          1016534.0      
REAL              5.938          0.318          22608.0        
DOUBLE            6.985          0.416          25088.0    

As you can see, there is no noticeable change in the time or storage size when the precision changes (precision 20 takes roughly as much time/size as 60000!).

The thing is when you change the scale. This is what you should worry about; as you can see, DECIMAL(100,2) and DECIMAL(100,80) show a great increase in both time and storage.

DECIMAL(1000,900) takes more than 1 gigabyte(!!!) storing absolutely the same values.

Finally, in the test above, REAL and DOUBLE don't seem to be so much better than DECIMAL (they may even seem worse). But try changing the number of lines inserted (the for loop in the test method), the bigger the number, the better they seem to respond.

* DECIMAL(20,2) seems to be slower/larger than the rest. That is not real. Actually, whatever you choose to run first is going to be slightly slower/larger. Go figure...

public class Main {
    public static void main(String[] a) throws Exception {
        Class.forName("org.h2.Driver");
        System.out.format("%-18s%-15s%-15s%-15s", "TYPE", "INSERT time", "COUNT() time", ".db Size (kb)");
        System.out.println();
        testPerformance("TEST_DECIMAL_20_2",     "DECIMAL(20,2)");
        testPerformance("TEST_DECIMAL_100_2",    "DECIMAL(100,2)");
        testPerformance("TEST_DECIMAL_100_80",   "DECIMAL(100,80)");
        testPerformance("TEST_DECIMAL_60000_2",  "DECIMAL(60000,2)");
        testPerformance("TEST_DECIMAL_1000_900", "DECIMAL(1000,900)");
        testPerformance("TEST_REAL",             "REAL");
        testPerformance("TEST_DOUBLE",           "DOUBLE"); 
    }

    private static void testPerformance(String dbName, String type) throws SQLException {
        System.out.format("%-18s", type);
        Connection conn = DriverManager.getConnection("jdbc:h2:" + dbName, "sa", "");
        conn.createStatement().execute("DROP TABLE IF EXISTS TEST;");
        conn.createStatement().execute("CREATE TABLE TEST (DECTEST " + type +" )");
        long insertStartTime = System.currentTimeMillis();
        for (int i = 0; i < 1000000; i++) {
            conn.createStatement().execute("INSERT INTO TEST (DECTEST) VALUES (12345678901234.45)");    
        }
        double insertTime = ((double)(System.currentTimeMillis()-insertStartTime))/1000;
        System.out.format("%-15s", insertTime+"");
        long countStartTime = System.currentTimeMillis();
        conn.createStatement().executeQuery("select COUNT(DECTEST) from TEST");
        double countTime = ((double)(System.currentTimeMillis()-countStartTime))/1000;
        System.out.format("%-15s", countTime+"");
        conn.close();
        double fileSize = (double)new File(dbName+".h2.db").length() / 1024;
        System.out.format("%-15s", fileSize+"");
        System.out.println();
    }
}
like image 55
acdcjunior Avatar answered Oct 11 '22 18:10

acdcjunior


The precision and scale of DECIMAL is optional in H2. If you create the column without specifying precision / scale, then performance is not affected, storage is not affected, and you can insert any value and will get the same value back:

create table test(data decimal);
insert into test values(1.345);
select * from test;
> 1.345
like image 36
Thomas Mueller Avatar answered Oct 11 '22 17:10

Thomas Mueller