Update: I have removed the duplicate connections, which reduced the time down to 18 seconds. Other improvements would be appreciated as well!
I feel like I have some massive oversight that is causing it to take exponentially longer than it should for the very few items I have in the database. Right now, there are only about 85 entities in my database, but running my Java program takes 51 seconds. For 40 entities, it took 35 seconds, so clearly I'm doing something wrong. This database will eventually have millions of entities in it, which would take days to complete a single run. Can anyone tell me what optimizations I should do? Please note I am a total beginner when it comes to MySQL and especially Java MySQL implementation, so please go easy on me.
Here is the problem code:
public static void SQL() {
int handleqty = -1;
String url = "jdbc:mysql://MYURL:3306/MYDB";
String username = "username";
String password = "password";
try{
Connection conn1 = DriverManager.getConnection(url, username, password);
for(Entry<String, String> entry: passToSQL.entrySet()) {
//System.out.println(entry);
String cardinfo = entry.getValue();
String Handle = cardinfo.split(",")[0];
String Title = cardinfo.split(",")[1];
String Vendor = cardinfo.split(",")[2];
String Tags = cardinfo.split(",")[3];
String Published = cardinfo.split(",")[4];
String Option1Name = cardinfo.split(",")[5];
String Option1Value = cardinfo.split(",")[6];
int VariantGrams = Integer.parseInt(cardinfo.split(",")[7]);
String VariantInventoryTracker = cardinfo.split(",")[8];
String VariantInventoryQty = cardinfo.split(",")[9];
String VariantInventoryPolicy = cardinfo.split(",")[10];
String VariantFufillmentService = cardinfo.split(",")[11];
float VariantPrice = Float.parseFloat(cardinfo.split(",")[12]);
String VariantRequiresShipping = cardinfo.split(",")[13];
String VariantTaxable = cardinfo.split(",")[14];
String ImgSrcfront = cardinfo.split(",")[15];
String ImgSrcback = cardinfo.split(",")[16];
String GiftCard = cardinfo.split(",")[17];
String Status = cardinfo.split(",")[18];
String Description = cardinfo.split(",")[19];
String checkexists = "SELECT EXISTS(SELECT 1 FROM MasterCardRecord WHERE Handle = ? LIMIT 1)";
PreparedStatement statement1 = conn1.prepareStatement(checkexists);
statement1.setString(1, Handle);
ResultSet exists = statement1.executeQuery();
exists.next();
//System.out.println(exists.getString(1));
boolean handleexists = false;
if(exists.getString(1).equals("1")) {
handleexists = true;
//REMOVED: Connection conn2 = DriverManager.getConnection(url, username, password);
String sql2 = "SELECT `Variant Inventory Qty` FROM MasterCardRecord WHERE Handle = ? LIMIT 1";
PreparedStatement getqty = conn1.prepareStatement(sql2);
getqty.setString(1, Handle);
ResultSet handleqtyrs = getqty.executeQuery();
handleqtyrs.next();
handleqty = handleqtyrs.getInt(1);
}
if(exists.getString(1).equals("0")) {
handleexists = false;
}
statement1.close();
// REMOVED: conn1.close();
//NEW CODE --------------------------------------------------------------------------------
if (handleexists == true) {
//REMOVED: Connection connInvUpdate= DriverManager.getConnection(url, username, password);
String InvUpdate = "INSERT INTO InventoryUpdate (Handle, `Variant Inventory Qty`) VALUES (?,?) ON DUPLICATE KEY UPDATE `Variant Inventory Qty` = ?";
PreparedStatement InvStatement = conn1.prepareStatement(InvUpdate);
InvStatement.setString(1,Handle);
InvStatement.setInt(2, handleqty 1);
InvStatement.setInt(3, handleqty 1);
int updatedInv = InvStatement.executeUpdate();
//REMOVED: connInvUpdate.close();
InvStatement.close();
}
//-----------------------------------------------------------------------------------------
if(handleexists == false) {
// Handle doesnt exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "INSERT INTO MasterCardRecord (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement statement2 = conn1.prepareStatement(sql);
statement2.setString(1, Handle);
statement2.setString(2, Title);
statement2.setString(3, Vendor);
statement2.setString(4, Tags);
statement2.setString(5, Published);
statement2.setString(6, Option1Name);
statement2.setString(7, Option1Value);
statement2.setInt(8, VariantGrams);
statement2.setString(9, VariantInventoryTracker);
statement2.setString(10, VariantInventoryQty);
statement2.setString(11, VariantInventoryPolicy);
statement2.setString(12, VariantFufillmentService);
statement2.setFloat(13, VariantPrice);
statement2.setString(14, VariantRequiresShipping);
statement2.setString(15, VariantTaxable);
statement2.setString(16, ImgSrcfront);
statement2.setString(17, ImgSrcback);
statement2.setString(18, GiftCard);
statement2.setString(19, Status);
statement2.setString(20, Description);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Item Added: " Handle);
}
statement2.close();
//REMOVED: conn3.close();
// Put into AddNewCards as well, for export and adding ONLY those cards that dont exist yet.
Connection AddNewCardConn = DriverManager.getConnection(url, username, password);
String AddNewCardSQL = "INSERT INTO AddNewCards (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement AddNewCardStatement = conn1.prepareStatement(AddNewCardSQL);
AddNewCardStatement.setString(1, Handle);
AddNewCardStatement.setString(2, Title);
AddNewCardStatement.setString(3, Vendor);
AddNewCardStatement.setString(4, Tags);
AddNewCardStatement.setString(5, Published);
AddNewCardStatement.setString(6, Option1Name);
AddNewCardStatement.setString(7, Option1Value);
AddNewCardStatement.setInt(8, VariantGrams);
AddNewCardStatement.setString(9, VariantInventoryTracker);
AddNewCardStatement.setString(10, VariantInventoryQty);
AddNewCardStatement.setString(11, VariantInventoryPolicy);
AddNewCardStatement.setString(12, VariantFufillmentService);
AddNewCardStatement.setFloat(13, VariantPrice);
AddNewCardStatement.setString(14, VariantRequiresShipping);
AddNewCardStatement.setString(15, VariantTaxable);
AddNewCardStatement.setString(16, ImgSrcfront);
AddNewCardStatement.setString(17, ImgSrcback);
AddNewCardStatement.setString(18, GiftCard);
AddNewCardStatement.setString(19, Status);
AddNewCardStatement.setString(20, Description);
int AddCardRows = AddNewCardStatement.executeUpdate();
AddNewCardStatement.close();
//REMOVED: AddNewCardConn.close();
}
if (handleexists == true) {
// Handle DOES exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "UPDATE MasterCardRecord SET `Variant Inventory Qty` = ? WHERE Handle = ?";
PreparedStatement statement2 = conn3.prepareStatement(sql);
statement2.setInt(1,handleqty 1);
statement2.setString(2, Handle);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Inventory of: " Handle "has been updated from " handleqty " to %d".formatted(handleqty 1));
}
statement2.close();
//REMOVED: conn3.close();
}
}
}
catch(Exception e){
System.out.println("Database connection failed!\n" e);
}
}
//ADDED:
conn1.close();
}
The purpose of this code is to take in some data in a map that was gathered from a CSV file, then go through that map and input the data into 3 different tables.
- MasterCardRecord : This table holds every card and its overall qty and other values
- AddNewCards : This table contains the information for new cards only, to allow for import into another website of ONLY cards new to the database
- Inventory Update: This table contains the handle and qty of cards that are already in the database, to allow for quick quantity updates of those cards
I tried to be clear with the problem, but if more information is needed, here is a pastebin of the full code: https://pastebin.com/TQ54f6gB
Images of database structure:
- https://i.imgur.com/YJKHEz7.png
- https://i.imgur.com/OmIYSh0.png
- https://i.imgur.com/iqbWHbc.png
- https://i.imgur.com/tv2gKMP.png
As requested, here is the text format of the photos:
CREATE TABLE `AddNewCards` (
`Handle` varchar(255) NOT NULL,
`Title` varchar(255) NOT NULL,
`Vendor` varchar(45) DEFAULT 'the10thinningva.com',
`Tags` varchar(255) DEFAULT NULL,
`Published` varchar(5) DEFAULT 'FALSE',
`Option1Name` varchar(45) DEFAULT 'Title',
`Option1Value` varchar(45) DEFAULT 'Default Title',
`VariantGrams` decimal(4,0) DEFAULT '45',
`Variant Inventory Tracker` varchar(45) DEFAULT 'shopify',
`Variant Inventory Qty` int DEFAULT '1',
`Variant Inventory Policy` varchar(45) DEFAULT 'deny',
`Variant Fufillment Service` varchar(45) DEFAULT 'manual',
`Variant Price` decimal(5,2) NOT NULL,
`Variant Requires Shipping` varchar(5) DEFAULT 'TRUE',
`Variant Taxable` varchar(5) DEFAULT 'TRUE',
`Img Src(front)` varchar(255) DEFAULT NULL,
`Img Src(back)` varchar(255) DEFAULT NULL,
`Gift Card` varchar(5) DEFAULT 'FALSE',
`Status` varchar(45) DEFAULT 'active',
`Description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Handle`),
UNIQUE KEY `handle_UNIQUE` (`Handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `InventoryUpdate` (
`Handle` varchar(255) NOT NULL,
`Variant Inventory Qty` int NOT NULL,
PRIMARY KEY (`Handle`),
UNIQUE KEY `Handle_UNIQUE` (`Handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `MasterCardRecord` (
`Handle` varchar(255) NOT NULL,
`Title` varchar(255) NOT NULL,
`Vendor` varchar(45) DEFAULT 'the10thinningva.com',
`Tags` varchar(255) DEFAULT NULL,
`Published` varchar(5) DEFAULT 'FALSE',
`Option1Name` varchar(45) DEFAULT 'Title',
`Option1Value` varchar(45) DEFAULT 'Default Title',
`VariantGrams` decimal(4,0) DEFAULT '45',
`Variant Inventory Tracker` varchar(45) DEFAULT 'shopify',
`Variant Inventory Qty` int DEFAULT '1',
`Variant Inventory Policy` varchar(45) DEFAULT 'deny',
`Variant Fufillment Service` varchar(45) DEFAULT 'manual',
`Variant Price` decimal(5,2) NOT NULL,
`Variant Requires Shipping` varchar(5) DEFAULT 'TRUE',
`Variant Taxable` varchar(5) DEFAULT 'TRUE',
`Img Src(front)` varchar(255) DEFAULT NULL,
`Img Src(back)` varchar(255) DEFAULT NULL,
`Gift Card` varchar(5) DEFAULT 'FALSE',
`Status` varchar(45) DEFAULT 'active',
`Description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Handle`),
UNIQUE KEY `handle_UNIQUE` (`Handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
An example of an entry is as follows:
'1989-fleer-#nno-california-angels-team-stickers-(one-logo)', '1989 Fleer #NNO California Angels Team Stickers (One Logo)', 'websitelink.com', 'collectable', 'TRUE', 'Title', 'Default Title', '45', 'shopify', '12', 'deny', 'manual', '2.00', 'TRUE', 'TRUE', 'https://linktoajpg_front.jpg', 'https://linktoajpg_back.jpg', 'FALSE', 'active', '1989 Fleer #NNO California Angels Team Stickers (One Logo)'
CodePudding user response：
Try using one single connection, opening a lot of connection is resource consuming see this post stack post, at least close the opened connections.
Also try to separate your code into functions, example update, retrieve, add must have their own functions.
EDIT:
public static void SQL() {
int handleqty = -1;
String url = "jdbc:mysql://MYURL:3306/MYDB";
String username = "username";
String password = "password";
try{
Connection conn1 = DriverManager.getConnection(url, username, password);
String cardinfo = "";
String Handle = "";
String Title = "";
String Vendor = "";
String Tags = "";
String Published = "";
String Option1Name = "";
String Option1Value = "";
int VariantGrams = "";
String VariantInventoryTracker = "";
String VariantInventoryQty = "";
String VariantInventoryPolicy = "";
String VariantFufillmentService = "";
float VariantPrice = "";
String VariantRequiresShipping = "";
String VariantTaxable = "";
String ImgSrcfront = "";
String ImgSrcback = "";
String GiftCard = "";
String Status = "";
String Description = "";
for(Entry<String, String> entry: passToSQL.entrySet()) {
//System.out.println(entry);
cardinfo = entry.getValue();
Handle = cardinfo.split(",")[0];
Title = cardinfo.split(",")[1];
Vendor = cardinfo.split(",")[2];
Tags = cardinfo.split(",")[3];
Published = cardinfo.split(",")[4];
Option1Name = cardinfo.split(",")[5];
Option1Value = cardinfo.split(",")[6];
VariantGrams = Integer.parseInt(cardinfo.split(",")[7]);
VariantInventoryTracker = cardinfo.split(",")[8];
VariantInventoryQty = cardinfo.split(",")[9];
VariantInventoryPolicy = cardinfo.split(",")[10];
VariantFufillmentService = cardinfo.split(",")[11];
VariantPrice = Float.parseFloat(cardinfo.split(",")[12]);
VariantRequiresShipping = cardinfo.split(",")[13];
VariantTaxable = cardinfo.split(",")[14];
ImgSrcfront = cardinfo.split(",")[15];
ImgSrcback = cardinfo.split(",")[16];
GiftCard = cardinfo.split(",")[17];
Status = cardinfo.split(",")[18];
Description = cardinfo.split(",")[19];
String checkexists = "SELECT EXISTS(SELECT 1 FROM MasterCardRecord WHERE Handle = ? LIMIT 1)";
PreparedStatement statement1 = conn1.prepareStatement(checkexists);
statement1.setString(1, Handle);
ResultSet exists = statement1.executeQuery();
exists.next();
//System.out.println(exists.getString(1));
boolean handleexists = false;
if(exists.getString(1).equals("1")) {
handleexists = true;
//REMOVED: Connection conn2 = DriverManager.getConnection(url, username, password);
String sql2 = "SELECT `Variant Inventory Qty` FROM MasterCardRecord WHERE Handle = ? LIMIT 1";
PreparedStatement getqty = conn1.prepareStatement(sql2);
getqty.setString(1, Handle);
ResultSet handleqtyrs = getqty.executeQuery();
handleqtyrs.next();
handleqty = handleqtyrs.getInt(1);
}
if(exists.getString(1).equals("0")) {
handleexists = false;
}
statement1.close();
// REMOVED: conn1.close();
//NEW CODE --------------------------------------------------------------------------------
if (handleexists == true) {
//REMOVED: Connection connInvUpdate= DriverManager.getConnection(url, username, password);
String InvUpdate = "INSERT INTO InventoryUpdate (Handle, `Variant Inventory Qty`) VALUES (?,?) ON DUPLICATE KEY UPDATE `Variant Inventory Qty` = ?";
PreparedStatement InvStatement = conn1.prepareStatement(InvUpdate);
InvStatement.setString(1,Handle);
InvStatement.setInt(2, handleqty 1);
InvStatement.setInt(3, handleqty 1);
int updatedInv = InvStatement.executeUpdate();
//REMOVED: connInvUpdate.close();
InvStatement.close();
}
//-----------------------------------------------------------------------------------------
if(handleexists == false) {
// Handle doesnt exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "INSERT INTO MasterCardRecord (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement statement2 = conn1.prepareStatement(sql);
statement2.setString(1, Handle);
statement2.setString(2, Title);
statement2.setString(3, Vendor);
statement2.setString(4, Tags);
statement2.setString(5, Published);
statement2.setString(6, Option1Name);
statement2.setString(7, Option1Value);
statement2.setInt(8, VariantGrams);
statement2.setString(9, VariantInventoryTracker);
statement2.setString(10, VariantInventoryQty);
statement2.setString(11, VariantInventoryPolicy);
statement2.setString(12, VariantFufillmentService);
statement2.setFloat(13, VariantPrice);
statement2.setString(14, VariantRequiresShipping);
statement2.setString(15, VariantTaxable);
statement2.setString(16, ImgSrcfront);
statement2.setString(17, ImgSrcback);
statement2.setString(18, GiftCard);
statement2.setString(19, Status);
statement2.setString(20, Description);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Item Added: " Handle);
}
statement2.close();
//REMOVED: conn3.close();
// Put into AddNewCards as well, for export and adding ONLY those cards that dont exist yet.
//-----> remove thisConnection AddNewCardConn = DriverManager.getConnection(url, username, password);
String AddNewCardSQL = "INSERT INTO AddNewCards (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement AddNewCardStatement = conn1.prepareStatement(AddNewCardSQL);
AddNewCardStatement.setString(1, Handle);
AddNewCardStatement.setString(2, Title);
AddNewCardStatement.setString(3, Vendor);
AddNewCardStatement.setString(4, Tags);
AddNewCardStatement.setString(5, Published);
AddNewCardStatement.setString(6, Option1Name);
AddNewCardStatement.setString(7, Option1Value);
AddNewCardStatement.setInt(8, VariantGrams);
AddNewCardStatement.setString(9, VariantInventoryTracker);
AddNewCardStatement.setString(10, VariantInventoryQty);
AddNewCardStatement.setString(11, VariantInventoryPolicy);
AddNewCardStatement.setString(12, VariantFufillmentService);
AddNewCardStatement.setFloat(13, VariantPrice);
AddNewCardStatement.setString(14, VariantRequiresShipping);
AddNewCardStatement.setString(15, VariantTaxable);
AddNewCardStatement.setString(16, ImgSrcfront);
AddNewCardStatement.setString(17, ImgSrcback);
AddNewCardStatement.setString(18, GiftCard);
AddNewCardStatement.setString(19, Status);
AddNewCardStatement.setString(20, Description);
int AddCardRows = AddNewCardStatement.executeUpdate();
AddNewCardStatement.close();
//REMOVED: AddNewCardConn.close();
}
if (handleexists == true) {
// Handle DOES exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "UPDATE MasterCardRecord SET `Variant Inventory Qty` = ? WHERE Handle = ?";
PreparedStatement statement2 = conn3.prepareStatement(sql);
statement2.setInt(1,handleqty 1);
statement2.setString(2, Handle);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Inventory of: " Handle "has been updated from " handleqty " to %d".formatted(handleqty 1));
}
statement2.close();
//REMOVED: conn3.close();
}
}
}
catch(Exception e){
System.out.println("Database connection failed!\n" e);
}
}
//ADDED:
conn1.close();
}
CodePudding user response：
PRIMARY KEY(HANDLE) provides a Unique Index on HANDLE, so drop
UNIQUE KEY `handle_UNIQUE` (`Handle`)
Looking at
SELECT EXISTS(SELECT 1 FROM MasterCardRecord WHERE Handle = ? LIMIT 1)
Get rid of LIMIT 1. EXISTS stops when it gets success.
Looking at
SELECT `Variant Inventory Qty` FROM MasterCardRecord WHERE Handle = ? LIMIT 1
This could serve as an existence check. That is, see if you can get rid of the previous query.
A LIMIT without an ORDER BY leads to getting some random result. If you can't have multiple rows, then the LIMIT is misleading; if you care which of many rows you get, then consider adding an ORDER BY.
SQL is much better at doing many rows at once than going back and forth from the app. Can your SELECT fetch all the relevant rows? Better yet, can the IODKU act on all the necessary rows at once?
If you want more help, try to boil down the code to one screen full. I don't need to see all the app code building and checking values -- unless the sluggishness is really in that code!