为什么Google Cloud SQL（使用JDBC）花费较长时间从Google App Engine插入记录比从我的个人计算机中删除记录？ [英] Why does Google Cloud SQL (using JDBC) take longer to insert records from Google App Engine than from my personal computer?
本文介绍了为什么Google Cloud SQL（使用JDBC）花费较长时间从Google App Engine插入记录比从我的个人计算机中删除记录？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我可以在3.5秒内从我的电脑插入2000条记录，GAE需要14.0秒才能完成同样的工作。我看不到如何让Google Driver使用rewriteBatchedStatements设置。
这是我的相关Java代码：
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）;
保护static final String USERNAME =xxxx;
protected static final String PASSWORD =xxxxx;
protected static String INSTANCE_CONNECTION_NAME
=xxxx：us-central1：xxxxx;
保护static final String DB_NAME =xxxxxx;
protected static final String IP_ADDRESS =xxx.1xx.2xx.4x;
保护static final String SQL_INSERT
=INSERT到Forms1099B（orderNumber，acctId，qty，secDesc，dateAcq，dateSold，salesPrice，cost，basisAdj，washAdj，nomineeAdj，otherAdj，term，basisRep ，？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？ ，？，？）;
//是否从Google App Engine连接
private boolean fromGAE = false;
// JDBC连接
private连接conn = null;
public Form89492016TableInserter（boolean fromGAE）{
this.fromGAE = fromGAE;
}
public void insertBatch（List< Forms1099BRecordBean> records）{
int batchSize = 500;
insertBatchUsingSize（records，batchSize）;
$ b $ public void insertBatchUsingSize（List< Forms1099BRecordBean> records，
int batchSize）{
try {
initializeConnection （）;
doInsertions（records，batchSize）;
closeConnection（）;
$ b $ catch（SQLException e）{
log.severe（e.getMessage（））;
$ public void closeConnection（）throws SQLException {
if（conn！= null）{
conn.close（）;
$ b public void initializeConnection（）throws SQLException {
String driverName
=（fromGAE）？ com.mysql.jdbc.GoogleDriver：
com.mysql.jdbc.Driver;
尝试{
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）;
$ b $ else {
String url = String.format（jdbc：mysql：//％s：3306 /％s，
IP_ADDRESS，
DB_NAME）;
属性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（）;
//每N个项目执行一次。
if（（i + 1）％batchSize == 0）{
stmt.executeBatch（）;
}
}
stmt.executeBatch（）;
}
}
}
<通常，将本地机器的性能与GAE的性能进行比较没有多大意义，因为它不是苹果与苹果的比较：
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.
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( );
}
}
}
解决方案
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:
- 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插入记录比从我的个人计算机中删除记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
