lordess / benchmarksql_for_mysql Goto Github PK
View Code? Open in Web Editor NEWA variant of benchmarksql 4.1 . Works well with MySQL and MyCat.
A variant of benchmarksql 4.1 . Works well with MySQL and MyCat.
orderStatusTransaction() has the SQL belows. MyCat (1.6.5 BETA) can not parse it well, because it is a JOIN with aggregate function COUNT(). Also we cannot just wait before MyCat fix it or do somethings.
.prepareStatement("SELECT COUNT(DISTINCT (s_i_id)) AS stock_count"
+ " FROM order_line, stock"
+ " WHERE ol_w_id = ?"
+ " AND ol_d_id = ?"
+ " AND ol_o_id < ?"
+ " AND ol_o_id >= ? - 20"
+ " AND s_w_id = ?"
+ " AND s_i_id = ol_i_id"
+ " AND s_quantity < ?");
It seems that the current MyCat is not good at subquery, so it is suggested to split the query into 2-step simple CRUD query like belows:
.prepareStatement("SELECT ol_i_id"
+ " FROM order_line"
+ " WHERE ol_w_id = ?"
+ " AND ol_d_id = ?"
+ " AND ol_o_id < ?"
+ " AND ol_o_id >= ? - 20");
// concate ol_i_id with comma, like this: "ol_i_id1, ol_i_id2, ..."
.prepareStatement("SELECT COUNT(DISTINCT (s_i_id)) AS stock_count"
+ " FROM stock"
+ " WHERE s_w_id = ?"
+ " AND s_quantity < ?"
+ " AND s_i_id IN (" + in_list + ")");
When step in stmtUpdateStock.executeBatch() of newOrderTransaction() in jTPCCTerminal.java, MyCat alarms that it cannot handle such command( say, unkown command
).
Replace java.sql.preparedStatement.executeBatch() with java.sql.preparedStatement.executeUpdate().
newOrderTransaction() has JOIN SQL belows. MyCat( 1.6.5 BETA ) cannot identify columns without explicitly assigned table name. Better to work around than just waiting MyCat's fix.
.prepareStatement("SELECT c_discount, c_last, c_credit, w_tax"
+ " FROM customer, warehouse"
+ " WHERE w_id = ? AND w_id = c_w_id"
+ " AND c_d_id = ? AND c_id = ?");
Make columns having full qualified name with alias syntax in SQL.
.prepareStatement("SELECT c.c_discount, c.c_last, c.c_credit, w.w_tax"
+ " FROM customer AS c, warehouse AS w"
+ " WHERE w.w_id = ? AND w.w_id = c.c_w_id"
+ " AND c.c_d_id = ? AND c.c_id = ?");
When MyCat(1.6.5) passes SQLs( example belows ) from front-end clients to back-end MySQLs, it will not drop or change the schema_name of the SQL.
SELECT ... FROM schema_name.table_name ...
If the virtual schema on MyCat is not as same as the physical schema on MySQL, there would be an error.
It surely is bug or missing point of MyCat, but we should do a workaround on our benchmarksql, instead of just waiting it to be fixed.
get rid of all schema_name from SQL text
newOrderTransaction() has a fixed(issue #3) JOIN SQL belows. But MyCat( 1.6.5 BETA ) cannot handle it properly, too.. Have to work around than just waiting MyCat's fix. Again.
.prepareStatement("SELECT c.c_discount, c.c_last, c.c_credit, w.w_tax"
+ " FROM customer AS c, warehouse AS w"
+ " WHERE w.w_id = ? AND w.w_id = c.c_w_id"
+ " AND c.c_d_id = ? AND c.c_id = ?");
Split the JOIN into an equivalent two step SELECT sequence.
Firstly, get the w_id, w_tax from warehouse. Because w_id is the
primary key of warehouse, no more than one record will be returned. So we can use basic variables to cahce it.
.prepareStatement("SELECT w_id, w_tax"
+ " FROM warehouse"
+ " WHERE w_id = ? ");
// store w_id of the result set in Java variable w_w_id as a middle data
Secondly, try to get other fields from customer by cached w_id.
Java .prepareStatement("SELECT c_discount, c_last, c_credit" + " FROM customer" + " WHERE c_w_id = "+ w_w_id +" AND" + " c_d_id = ? AND c_id = ?");
Finally, return w_tax from step one, c_discount, c_last and c_credit from step two.
#phenonmenon
try..catch here does not print call stack. It is not friendly for debug.
catch (SQLException ex) {
log .error("--- Unexpected SQLException caught in NEW-ORDER Txn ---");
while (ex != null) {
log.error(ex.getMessage());
ex = ex.getNextException();
}
add Exception.printStackTrace() to it.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.