Patch to speed up table creation

This message was edited 1 time. Last update was at 23 Feb 2010 14:32:38

The code below reduces the time needed to create a table, like the following, by an order of magnitude:
create table testpartitioning_partitioned (da varchar(40) NOT NULL) partitioning key da on all;

It is especially true if the coordinator is run on a node without any database (as in my testing environment).

When creating a table, 256 queries of the following type are currently run:
INSERT INTO xsystabparthash (parthashid, tableid, dbid, hashValue, nodeid) VALUES (254, 2, 1, 253, 2)
INSERT INTO xsystabparthash (parthashid, tableid, dbid, hashValue, nodeid) VALUES (255, 2, 1, 254, 3)
INSERT INTO xsystabparthash (parthashid, tableid, dbid, hashValue, nodeid) VALUES (256, 2, 1, 255, 1)

The code below merges this into a single query like the following:
INSERT INTO xsystabparthash (parthashid, tableid, dbid, hashValue, nodeid) VALUES (254, 2, 1, 253, 2), (255, 2, 1, 254, 3), (256, 2, 1, 255, 1)

The following is the only method, which needs to change. It is located in the HashPartitionMap class:

/**
     * 
     * @param metadata 
     * @param database 
     * @param tableID 
     *
     * @throws java.sql.SQLException 
     */
    public void storeMapToMetadataDB(MetaData metadata, SysDatabase database,
            int tableID) throws SQLException {
        int parthashid = 0;
        String query = "SELECT max(parthashid) FROM xsystabparthash";
        ResultSet rs = metadata.executeQuery(query);
        try {
            rs.next();
            parthashid = rs.getInt(1) + 1;
        } finally {
            rs.close();
        }
        String insert = "";
        for (int i = 0; i < mappingTable.length; i++) {
            if (i == 0) insert = "INSERT INTO xsystabparthash "
				+ "(parthashid, tableid, dbid, hashValue, nodeid) "
				+ "VALUES (" + parthashid++ + ", " + tableID + ", "
				+ database.getDbid() + ", " + i + ", " + mappingTable[i]
				+ ")";
            else insert += ", (" + parthashid++ + ", " + tableID + ", "
	            + database.getDbid() + ", " + i + ", " + mappingTable[i]
	            + ")";
        }
        metadata.executeUpdate(insert);
    }

Thanks, David.

I appreciate you submitting a change.

I think we will hold off on adding this one for now since it may not be portable to other underlying databases. If we do add it, we would enable it by default and allow it to be turned off via the gridsql.conf file.

BTW, the reason why we bother inserting those rows is to make future repartitioning easier in the future, where we could move a subset of data to a new node, but that has not been implemented.

Regards,

Mason

This message was edited 2 times. Last update was at 25 Feb 2010 16:45:54

Hi Mason,

Thanks for the answer. I understand your reason for not adding the patch. For the record though, I will submit another one, which further increases the speed of creating a table, especially if the table has a lot of columns like mine. In my case it reduces 30 queries (one for each of my 30 columns) to one query. The only method to be changed is addTableColumns in MetaUtils.java.

One question, Mason. Is there any easier way for me to suggest changes to the code other than making a post here? I do not expect to be able to directly committ changes to the code, but being able to in some way make suggested changes to the code could be useful. Especially changes that affect more than one method or class.

/**
     * Utility function to add column definitions to the MetatData DB tables
     * 
     * This is used from the CREATE TABLE Also used for ALTER TABLE ADD COLUMN.
     * 
     * It maintains the Restrictions imposed on execute() from IMetaDataUpdate.
     */
    static public int[] addTableColumns(int begColSeq,
            List<SqlCreateTableColumn> columnDefinitions, int tableid)
            throws Exception {
        int[] colIDs = new int[columnDefinitions.size()];
        SqlCreateTableColumn aSqlCreateTableColumn;
        String sqlStatement;
        String xsyscolumnsBaseStr = "INSERT INTO xsyscolumns" + "(colid, "
                + "tableid, " + "colseq," + "colname, " + "coltype, "
                + "collength, " + "colscale, " + "colprecision,"
                + "isnullable," + "isserial," + "DEFAULTEXPR, " + "CHECKEXPR, "
                + "nativecoldef)" + " values ";
        sqlStatement = "SELECT max(colid) FROM xsyscolumns";
        ResultSet rs = MetaData.getMetaData().executeQuery(sqlStatement);
        int colid;
        try {
            rs.next();
            colid = rs.getInt(1) + 1;
        } finally {
            rs.close();
        }
        sqlStatement = "";
        for (int i = 0, seq = begColSeq + 1; i < columnDefinitions.size(); i++, seq++) {
            aSqlCreateTableColumn = columnDefinitions.get(i);
            colIDs[i] = colid++;
            if (i > 0) sqlStatement += ", ";
            sqlStatement += "("
                    + colIDs[i]
                    + ","
                    + tableid
                    + ","
                    + seq
                    + ","
                    + "'"
                    + aSqlCreateTableColumn.columnName
                    + "',"
                    + aSqlCreateTableColumn.getColumnType()
                    + ","
                    + (aSqlCreateTableColumn.getColumnLength() > -1 ? aSqlCreateTableColumn
                            .getColumnLength()
                            + ","
                            : "null,")
                    + (aSqlCreateTableColumn.getColumnScale() > -1 ? aSqlCreateTableColumn
                            .getColumnScale()
                            + ","
                            : "null,")
                    + (aSqlCreateTableColumn.getColumnPrecision() > -1 ? aSqlCreateTableColumn
                            .getColumnPrecision()
                            + ","
                            : "null,") + aSqlCreateTableColumn.isnullable + ","
                    + (aSqlCreateTableColumn.isSerial() ? "1" : "0") + ",";

String defaultValue = aSqlCreateTableColumn.getDefaultValue();
            if (defaultValue != null) {
                defaultValue = "'" + defaultValue.replaceAll("'", "''") + "'";
            }
            sqlStatement += defaultValue + ", ";

String checkString = aSqlCreateTableColumn
                    .getcheckConditionString();
            if (checkString != null) {
                checkString = checkString.replaceAll("'", "''");
            }
            sqlStatement += "'" + checkString + "' ,";

sqlStatement += "'"
                    + aSqlCreateTableColumn.rebuildString().replaceAll("'",
                            "''") + "')";
           
        }
        sqlStatement = xsyscolumnsBaseStr + sqlStatement;
        MetaData.getMetaData().executeUpdate(sqlStatement);
        return colIDs;
    }

Hi David,

Please feel free to post any patches here.

If you prefer, you may also first privately send me a message.

Regards,

Mason