为什么 Google Cloud SQL(使用 JDBC)从 Google App Engine 插入记录的时间比从我的个人计算机插入记录的时间长?

问题描述

我可以在 3.5 秒内从我的计算机插入 2000 条记录，而 GAE 需要 14.0 秒来做同样的事情.我看不到如何让 Google 驱动程序使用 rewriteBatchedStatements 设置.

I can insert 2000 records in 3.5 seconds from my computer, GAE take 14.0 seconds to do the same thing. I cannot see how I can get the Google Driver to use the rewriteBatchedStatements setting.

这是我的相关 Java 代码:

Here is my relevant Java code:

package year2016.tax8949.database2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;

public class Form89492016TableInserter {

private static final Logger log
    = Logger.getLogger( "Form89492016TableInserter" );

protected static final String USERNAME = "xxxx";

protected static final String PASSWORD = "xxxxx";

protected static final String INSTANCE_CONNECTION_NAME
    = "xxxx:us-central1:xxxxx";

protected static final String DB_NAME = "xxxxxx";

protected static final String IP_ADDRESS = "xxx.1xx.2xx.4x";

protected static final String SQL_INSERT
    = "INSERT into Forms1099B ( orderNumber,acctId,qty,secDesc,dateAcq,dateSold,salesPrice,cost,basisAdj,washAdj,nomineeAdj,otherAdj,term,basisRep,rep1099B,tranType,dateAcqVar,covered,symbol,expired ) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )";


// Whether connecting from Google App Engine
private boolean fromGAE = false;

// JDBC connection
private Connection conn = null;

public Form89492016TableInserter( boolean fromGAE ) {
    this.fromGAE = fromGAE;
}

public void insertBatch( List<Forms1099BRecordBean> records ) {

    int batchSize = 500;

    insertBatchUsingSize( records, batchSize );
}

public void insertBatchUsingSize( List<Forms1099BRecordBean> records,
                                  int batchSize ) {

    try {

        initializeConnection( );

        doInsertions( records, batchSize );

        closeConnection( );

    }
    catch( SQLException e ) {
        log.severe( e.getMessage( ) );
    }

}

public void closeConnection( ) throws SQLException {

    if ( conn != null ) {
        conn.close( );
    }

}

public void initializeConnection( ) throws SQLException {

    String driverName
        = ( fromGAE ) ? "com.mysql.jdbc.GoogleDriver" :
                        "com.mysql.jdbc.Driver";

    try {
        Class.forName( driverName );
    }
    catch( ClassNotFoundException e ) {
        log.severe( e.getMessage( ) );
        return;
    }

    if ( fromGAE ) {

        String connectionString
            = String.format(
                 "jdbc:google:mysql://%s/%s?user=root&password=%s",
                 INSTANCE_CONNECTION_NAME,
                 DB_NAME,
                 PASSWORD );

        conn = DriverManager.getConnection( connectionString );

    }
    else {

        String url = String.format( "jdbc:mysql://%s:3306/%s",
                                    IP_ADDRESS,
                                    DB_NAME );

        Properties props = new Properties();
        props.setProperty( "user", USERNAME );
        props.setProperty( "password", PASSWORD );
        props.setProperty( "rewriteBatchedStatements", "true" );

        conn = DriverManager.getConnection( url, props );

    }

}

private void doInsertions( List<Forms1099BRecordBean> records,
                           int batchSize ) throws SQLException {

    try ( PreparedStatement stmt = conn.prepareStatement( SQL_INSERT ) ) {

        for (int i = 0; i < records.size( ); i++) {

            Forms1099BRecordBean item = records.get( i );

            stmt.setString( 1, item.getOrderNumber() );
            stmt.setString( 2, item.getAcctId() );
            stmt.setString( 3, item.getQty() );
            stmt.setString( 4, item.getSecDesc() );
            stmt.setDate( 5, item.getDateAcq() );
            stmt.setDate( 6, item.getDateSold() );
            stmt.setBigDecimal( 7, item.getSalesPrice() );
            stmt.setBigDecimal( 8, item.getCost() );
            stmt.setBigDecimal( 9, item.getBasisAdj() );
            stmt.setBigDecimal( 10, item.getWashAdj() );
            stmt.setBigDecimal( 11, item.getNomineeAdj() );
            stmt.setBigDecimal( 12, item.getOtherAdj() );
            stmt.setString( 13, item.getTerm() );
            stmt.setString( 14, item.getBasisRep() );
            stmt.setString( 15, item.getRep1099B() );
            stmt.setString( 16, item.getTranType() );
            stmt.setString( 17, item.getDateAcqVar() );
            stmt.setString( 18, item.getCovered() );
            stmt.setString( 19, item.getSymbol() );
            stmt.setString( 20, item.getExpired() );

            stmt.addBatch( );

            // Execute every N items.
            if ( (i + 1) % batchSize == 0 ) {
                stmt.executeBatch( );
            }

        }

        stmt.executeBatch( );

    }

}

}

推荐答案

一般来说，将本地机器上的性能与 GAE 上的性能进行比较没有多大意义，因为这不是苹果与苹果的比较:

In general it doesn't make much sense to compare the performance on your local machine with the performance on GAE, as it's not an apples vs apples comparison:

• 如今，大多数本地计算机都比 GAE 强大得多实例类机器
• 本地机器可能运行与 GAE 不同的操作系统(不要，如果这意味着更快或更慢)
• 本地机器通常在裸机平台上运行操作系统，GAE 实例在容器或虚拟机上运行
• SDK 只是 GAE 基础代码功能(一部分)的模拟器，而不是实际 GAE 基础代码
• 大多数(如果不是全部)GAE 服务实际上并未在您应用的实例上运行，而是通过 RPC 访问；内部 GAE 网络非常快，但仍然比本地机器内部的等效 SDK 模拟慢很多

• most local machines these days are significantly more powerful than GAE instance class machines
• local machines are likely running a different OS that GAE (donno if this means faster or slower)
• local machines typically run OSes on bare metal platforms, GAE instances run on containers or VMs
• the SDK is just an emulator of (a portion of) the GAE infra code functionality, not the actual GAE infra code
• most (if not all) GAE services are not actually running on your app's instances, but accessed via RPCs; the internal GAE network is very fast, but still a lot slower than the equivalent SDK emulation which is all internal to the local machine

这篇关于为什么 Google Cloud SQL(使用 JDBC)从 Google App Engine 插入记录的时间比从我的个人计算机插入记录的时间长?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！