• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

So I rewrote a PHP script into a java class...

T

TheDude05

Limp Gawd
Joined
Jan 27, 2005
Messages
393
and the Java version is slower..... MUCH slower. The php version running on our webserver (p4 3ghz, 1 gig ram, gentoo linux, apache) runs in about 11 seconds compared to the java version on my machine (amd64 3500+, 2gig ram, winXP) which can take upwards of a minute.

I'm running the Java class out of eclispe so I'm hoping thats partially why, but still, I expected a little better performance than this. Its essentially the same logic but using some java datatypes like HashMaps instead of PHP arrays figuring this would speed up the process exponentially.

When I go home for lunch I'll get the java code and post them both. The php version does have the advantage of having the MySQL database on the same host but I'm not sure that would make that big of a difference because queries are only ran at the beginning and end of both files. Is java really THAT slow?
 
I don't code in Java, but yes, it is my experience as an end user that it's incredibly slow.
 
TheDude05 said:
When I go home for lunch I'll get the java code and post them both. The php version does have the advantage of having the MySQL database on the same host but I'm not sure that would make that big of a difference because queries are only ran at the beginning and end of both files. Is java really THAT slow?
Click to expand...

I doubt it. Something else is going on.
 
definetely need the code to say for sure. shouldnt be that much of a difference though
 
It has always been my experience that java runs significantly slower thanks to various variables. Consumers see the slow down because it has to run in a runtime environment no top of the operating system instead of using the actual operating system classes. This is good in the fact that java isnt partial to any operating system and can in turn run on any one as long as the runtime environment is available.....bad because you see the slowdown.
 
i think if the code was posted it would be apparent that the issue lies in the subtleties of the languages involved rather than the performance of the jvm
 
While Java can be slow at times, it has come a long way in recent years, so it should not be almost 10 times slower than a language such as PHP. There is definitely something else going on there.
 
i know Java5 improved the speeds a lot, but Java is a client side app so depending on the function you gave it, it more then likly will run slower because it has to send things to your machine to work on.

personally i prefer to keep as much as possible on the server side because end users dont like to have to navigate their way to dl a JRE, or in most cases here i'm guessing the JDK

java does have bennefits from a programming point of view, but not to many that apply to web sites that i'm aware of
 
PHP isn't terribly fast itself. If it was C++ then 10x slower might be believable, but I think here it is something else.
 
Wow I thought the thread was dead. Sorry about that. I never posted code because I never got a chance to adequately trace/profile the java code which I felt was unfair. Heres all the associated classes... But before you flame, there are parts of my Java code that I know are really inefficient (Like writing a file, closing it, then re-opening it) and are just there for time being

PHP
Code: 
<?php

function numbersArray ($array,$prefix_arr) {
	$ret_arr = array();
	foreach ($array as $foo) {
		$ret_arr[$foo['id']] = $prefix_arr[$foo['prefix_id']] . "-" . $foo['number'];
	}
	return $ret_arr;
}

function trunksArray ($array,$prefix_arr) {
	$ret_arr = array();
	foreach ($array as $foo) {
		$ret_arr[$foo['id']] = $prefix_arr[$foo['prefix_id']] . "-" . $foo['number'];
	}
	return $ret_arr;
}

function callgrpArray ($array) {
	$ret_arr = array();
	foreach ($array as $foo) {
		$ret_arr[$foo['id']] = $foo['callgrp'];
	}
	return $ret_arr;
}

function prefixArray ($array) {
	$ret_arr = array();
	foreach ($array as $foo) {
		$ret_arr[$foo['id']] = $foo['area_code'] . " " . $foo['prefix'];
	}
	return $ret_arr;
}



require("db_connect.php");

ini_set('max_execution_time', '250');

$uploaddir = './uploads/';
$uploadfile = $uploaddir . basename($_FILES['userfile']['name'] . time());

$hash = md5_file($_FILES['userfile']['tmp_name']);

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)) {
	$fp = fopen($uploadfile, "r");
	//get the header so we know what columns each item is in
	$header = fgets($fp, 4096);
	//$header = trim(strtolower($header),"\x00..\x1F");
	$header = trim(strtolower(str_replace("\"","",$header)));
	$header_arr = explode(",", $header);


	//print_r($header_arr);

//Find where each item is located

	if (($foo = array_search("nprojcode",$header_arr)) !== false ) {
		$nprojcode = $foo;
		$foo = null;
	} else die("<b>nprojcode</b> column not found!! Make sure the file has header!!");

	if (($foo = array_search("cauthcode",$header_arr)) !== false ) {
		$cauthcode = $foo;
		$foo = null;
	} else die("<b>cauthcode</b> column not found!! Make sure the file has header!!");

	if (($foo = array_search("dcall",$header_arr)) !== false ) {
		$dcall = $foo;
		$foo = null;
	} else die("<b>dcall</b> column not found!! Make sure the file has header!!");

	if (($foo = array_search("ctime",$header_arr)) !== false ) {
		$ctime = $foo;
		$foo = null;
	} else die("<b>ctime</b> column not found!! Make sure the file has header!!");

	if (($foo = array_search("nmin",$header_arr)) !== false ) {
		$nmin = $foo;
		$foo = null;
	} else die("<b>nmin</b> column not found!! Make sure the file has header!!");

	if (($foo = array_search("namt",$header_arr)) !== false ) {
		$namt = $foo;
		$foo = null;
	} else die("<b>namt</b> column not found!! Make sure the file has header!!");

	if (($foo = array_search("ccallgrp",$header_arr)) !== false ) {
		$ccallgrp = $foo;
		$foo = null;
	} else die("<b>ccallgrp</b> column not found!! Make sure the file has header!!");

	if (!($foo = array_search("nbr",$header_arr)) === false ) {
		$nbr = $foo;
		$foo = null;
	} else die("<b>nbr</b> column not found!! Make sure the file has header!!");

	if (($foo = array_search("special",$header_arr)) !== false ) {
		$special = $foo;
		$foo = null;
	} else die("<b>special</b> column not found!! Make sure the file has header!!");




	$db->sql_query("SELECT * FROM call_group");
	$result = $db->sql_fetchrowset();
	$callgrp_arr = callgrpArray($result);

	$result = null;
	//print_r($callgrp_arr);

	$db->sql_query("SELECT * FROM prefixes");
	$result = $db->sql_fetchrowset();
	$prefix_arr = prefixArray($result);

	$result = null;
	//print_r($prefix_arr);

	$db->sql_query("SELECT id, prefix_id, number FROM numbers");
	$result = $db->sql_fetchrowset();
	$num_arr = numbersArray($result,$prefix_arr);

	$result = null;
	//print_r($num_arr);

	$db->sql_query("SELECT id, prefix_id, number FROM outlines");
	$result = $db->sql_fetchrowset();
	$trunks_arr = trunksArray($result,$prefix_arr);

	unset($result);
	//print_r($trunks_arr);

	//start reading through file
	$a=0;$skip=0;
	while(!feof($fp))
	{
		$data .= fgets($fp, 4096);
		//removes all " from the file
		$data = str_replace("\"","",$data);
		//trims off white space and control chars
		$data = trim($data);
		//Check empty line
		if (!$data) {
			break;
		}

		$arr = explode(",", $data);

/*checks full row
		if (count($arr) < 9) {
			$skip++;
			break;
		}
*/
		//checks for valid data in 8/9 columns (not using special)
		if ($arr[$nprojcode] == "" || $arr[$cauthcode] == "" || $arr[$dcall] == "" || $arr[$ctime] == ""
				|| $arr[$nmin] == "" || $arr[$namt] == "" || $arr[$ccallgrp] == "" || $arr[$nbr] == "") {
					$skip++;
					break;
			}

		//checks for non standard phone numbers (international, etc)
		if (count(explode(" ",$arr[$nbr])) < 2) {
			echo "Non standard call on line " . ($a+2) . ". ";
			if (strlen(trim($arr[$nbr])) >= 11 || $arr[$ccallgrp] == "Overseas") {
				echo "The call appears to be International.<br /<br />";
			} else {
				echo "Could not determine the type of call.<br /<br />";
			}
			break;
		}

		//Convert dcall and ctime variable to a unix timestamp
		$unix_time = strtotime($arr[$dcall] . " " . $arr[$ctime]);

		//fall back for incorrect data from sheet
		if (!$unix_time) {
			$unix_time = strtotime($arr[$dcall]);
		}


		// strip out non needed info in cauthcode column
		$cauth = explode(" ",$arr[$cauthcode]);
		$m = count($cauth);
		$arr[$cauthcode] = $cauth[$m-2] . " " . $cauth[$m-1];
		unset($cauth);

		//Find cauthcode(trunk) in current database, if not create new entry
		$trunk_id = array_search($arr[$cauthcode],$trunks_arr);
		if ($trunk_id === false) {
			//check for new prefix
			$trunk_id = null;
			$temp = str_ireplace("-"," ",$arr[$cauthcode]);
			$temp_arr = explode(" ",$temp);
			//print_r($temp_arr);
			$prefix_id = array_search($temp_arr[0] . " " . $temp_arr[1], $prefix_arr);
			if ($prefix_id === false) {
				$db->sql_query("INSERT INTO prefixes (area_code, prefix) VALUES (" . $temp_arr[0] . "," . $temp_arr[1] . ")") or die($db->sql_error_str() . "<br /><br />prefix insert : trunk : " . "INSERT INTO prefixes (area_code, prefix) VALUES (" . $temp_arr[0] . "," . $temp_arr[1] . ")" . print_r($arr));
				$prefix_id = $db->sql_insert_id();
				// Add newly inserted value into our array
				$prefix_arr[$prefix_id] = $temp_arr[0] . " " . $temp_arr[1];
			}

			$temp = null;

			//add code for new trunk insertion

			$db->sql_query("INSERT INTO outlines (prefix_id,number) VALUES (" . $prefix_id . "," . $temp_arr[2] . ")") or die($db->sql_error_str() . "<br /><br />Trunk insert");
			$trunk_id = $db->sql_insert_id();
			$trunks_arr[$trunk_id] = $temp_arr[0] . " " . $temp_arr[1] . "-" . $temp_arr[2];
		}

		$temp_arr = null;



		//Find number in current database, if not create new entry
		$number_id = array_search($arr[$nbr],$num_arr);
		if ($number_id === false) {
			//check for new prefix
			$number_id = null;
			$temp = str_ireplace("-"," ",$arr[$nbr]);
			$temp_arr = explode(" ",$temp);
			if (strlen($temp_arr[2]) < 4) {
				break;
			}
			$prefix_id = array_search($temp_arr[0] . " " . $temp_arr[1], $prefix_arr);

			if ($prefix_id === false) {
				$db->sql_query("INSERT INTO prefixes (area_code, prefix) VALUES (" . $temp_arr[0] . "," . $temp_arr[1] . ")") or die($db->sql_error_str() . "<br /><br />Prefix insert : Numbers ");
				$prefix_id = $db->sql_insert_id();
				// Add newly inserted value into our array
				$prefix_arr[$prefix_id] = $temp_arr[0] . " " . $temp_arr[1];
			}

			$temp = null;

			$sql = "INSERT INTO numbers (prefix_id,number) VALUES (" . $prefix_id . "," . $temp_arr[2] . ")";
			$db->sql_query($sql)or die($db->sql_error_str() . "<br /><br />$sql<br />Numbers insert");
			$sql = null;
			$number_id = $db->sql_insert_id();
			$num_arr[$number_id] = $temp_arr[0] . " " . $temp_arr[1] . "-" . $temp_arr[2];
		}

		unset($temp_arr); unset($temp);

		//Find callgrp in current database, if not create new entry
		$callgrp_id = array_search($arr[$ccallgrp],$callgrp_arr);
		if ($callgrp_id === false) {
			$sql = "INSERT INTO call_group (callgrp) VALUES ('" . $arr[$ccallgrp] . "')";
			$db->sql_query($sql) or die($db->sql_error_str() . "<br /><br />Call Group insert<br />\n $sql");
			$sql = null;
			$callgrp_id = $db->sql_insert_id();
			$callgrp_arr[$callgrp_id] = $arr['ccallgrp'];
		}
/*
		if (!$arr[$special]) {
			$arr[$special] = "\"\"";
		}

*/
		$sql_statement .= "\n (" . $arr[$nprojcode] . "," . $trunk_id . "," . $unix_time . "," . $arr[$nmin] . ","
							. $arr[$namt] . "," . $callgrp_id . "," . $number_id . ",'" . $arr[$special] . "'),";



		$data = null;
		$arr = null;
		$number_id = null; $trunk_id = null; $callgrp_id = null;
	$a++;
	//echo $a . "\n";
	}



	if ($sql_statement) {
		$db->sql_query("INSERT INTO statements (nprojcode,outline_id,call_date,minutes,amount,callgrp_id,number_id,special) VALUES" .  rtrim($sql_statement,"\x2C")) or die($db->sql_error_str() . "<br /><br />Final");
		$db->sql_query("INSERT INTO uploads (date,file_name,hash) VALUES (" . time() . ",'" . $_FILES['userfile']['name'] . "','$hash')");
		echo "Upload was sucessful. $a calls were added.<br />\n";
	} else {
		echo "Insert did not run<br /><br />\n\n";
		echo "INSERT INTO statements (nprojcode,outline_id,call_date,minutes,amount,callgrp_id,number_id,special) VALUES" .  rtrim($sql_statement,"\x2C");
	}

	if ($skip > 0) {
		echo "$skip row(s) were skipped during the process";
	}

	echo "<br /><br /><a href=\"index.php\">Go Back</a>";
} else {
   echo "Error with upload!!\n";
}


?>

Java code to follow...
 
Heres my Java Import class

Code: 
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Scanner;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Import {
	private File currFile;
	private Database database;
	private Scanner input;
	private String finalSQL;
	private final int insertsPerLine = 500;
	// Header indexes
	private int NPROJCODE; 
	private int CAUTHCODE; 
	private int DCALL;
	private int CTIME;
	private int NMIN;
	private int NAMT;
	private int CCALLGRP;  
	private int NBR;
	private int SPECIAL;
	
	public enum DatabaseTables {
		TrunkLines, Numbers, CallGroups, Prefixes
	}
	
	
	/*
	public Import() {
		super();
	}
	*/
	
	public Import (File file, Database database) throws FileNotFoundException {
		this.currFile = file;
		this.finalSQL = "";
		this.input = new Scanner(file);
		this.database = database;
	}
	
	public int startImport() {
		String headerLn = input.nextLine();
		String line; String item; 
		String[] lineArr;
		
		if (!this.parseHeader(headerLn)) {
			// ERROR
		} else {
			/* process the rest of the file
			 * 
			 * First thing we need to do is build our HashSets of current database data. This is used to reduce call to the SQL server
			 */
			try {
				HashMap<String, Integer> trunks = this.createHashMap(DatabaseTables.TrunkLines);
				HashMap<String, Integer> prefixes = this.createHashMap(DatabaseTables.Prefixes);
				HashMap<String, Integer> callgroups = this.createHashMap(DatabaseTables.CallGroups);
				HashMap<String, Integer> numbers = this.createHashMap(DatabaseTables.Numbers);
				int lines = 0, curLine = 0;
				
				while (input.hasNextLine()) {
					int number_id, trunk_id, callgrp_id;
					long callDate; Date date;
					SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy k:m");
					// Read in the line and remove all the quotes
					line = input.nextLine().replace("\"", "").trim();
					// Split it into an array
					lineArr = line.split(",");
					
					
					// *************
					// CALL DATE
					// *************
					try {
						date = dateFormat.parse(lineArr[DCALL] + " " + lineArr[CTIME]);
						callDate = date.getTime() / 1000;
					} catch (ParseException ex) {
						ex.printStackTrace();
						callDate = 0;
					}
					
					// *************
					// CALL GROUPS
					// *************
					item = lineArr[CCALLGRP];
					// See if it exists already
					if (!callgroups.containsKey(item)) {
						// New call group -- Insert it into database
						callgrp_id = database.insertCallGroup(item);
						// and add it to our HashMap
						callgroups.put(item,callgrp_id);
					} else {
						callgrp_id = callgroups.get(item);
					}
					
					// *************
					// TRUNK LINES
					// *************
					item = lineArr[CAUTHCODE];
					// Cut off any text before the number
					item = item.substring(item.length()-12, item.length()).trim();
					// See if it exists already
					if (!trunks.containsKey(item)) {
						// This whole new phone number does not exist in our database
						// We first need to check if the prefix exists in our database
						String temp;
						int prefix_id;
						// Remove the ending number and the dash before it
						temp = item.substring(0, item.length() - 5);
						if (!prefixes.containsKey(temp)) {
							// Create the prefix
							String[] tempArr = temp.split(" ");
							// remove `-` on the end of 2nd prefix
							tempArr[1] = tempArr[1].substring(0, 3);
							prefix_id = database.insertPrefix(tempArr[0], tempArr[1]);
							// Now add our new prefix to our HashMap
							prefixes.put(temp, prefix_id);
						} else {
							prefix_id = prefixes.get(temp);
						}
						
						// Now that we have prefix_id, create outline number
						trunk_id = database.insertTrunk(prefix_id, item.substring(item.length() - 4, item.length()));
						// and add it our HashMap
						trunks.put(item, trunk_id);
					} else {
						trunk_id = trunks.get(item);
					}
					
					// *************
					// DIALED NUMBERS
					// *************
					item = lineArr[NBR];
					// See if it exists already
					if (!numbers.containsKey(item)) {
						// This whole new phone number does not exist in our database
						// We first need to check if the prefix exists in our database
						String temp;
						int prefix_id;
						// Remove the ending number and the dash before it
						temp = item.substring(0, item.length() - 5);
						if (!prefixes.containsKey(temp)) {
							// Create the prefix
							String[] tempArr = temp.split(" ");
							prefix_id = database.insertPrefix(tempArr[0], tempArr[1]);
							// Now add our new prefix to our HashMap
							prefixes.put(temp, prefix_id);
						} else {
							prefix_id = prefixes.get(temp);
						}
						
						// Now that we have prefix_id, create phone number
						number_id = database.insertNumber(prefix_id, item.substring(item.length() - 4, item.length()));
						// and add it our HashMap
						numbers.put(item, number_id);
					} else {
						number_id = numbers.get(item);
					}
					
					// *************
					// CREATE SQL
					// *************
					
					// Now we done all our data mining, lets create a statement
					
					if (curLine == 0) {
						// first line
						this.finalSQL += "INSERT INTO statements (nprojcode,outline_id,call_date,minutes,amount,callgrp_id,number_id) VALUES ";
						this.finalSQL += "(" + lineArr[NPROJCODE] + "," + trunk_id + "," + callDate + "," +
							lineArr[NMIN] + "," + lineArr[NAMT] + "," + callgrp_id + "," + number_id + "), ";
						curLine++;
					} else if (curLine < this.insertsPerLine ) {
						// line 1-499
						this.finalSQL += "(" + lineArr[NPROJCODE] + "," + trunk_id + "," + callDate + "," +
							lineArr[NMIN] + "," + lineArr[NAMT] + "," + callgrp_id + "," + number_id + "), ";
						curLine++;
					} else {
						// line 500
						this.finalSQL += "(" + lineArr[NPROJCODE] + "," + trunk_id + "," + callDate + "," +
							lineArr[NMIN] + "," + lineArr[NAMT] + "," + callgrp_id + "," + number_id + ")\n";
						curLine=0;
					}
					lines++;
					System.out.println(lines);
				}

				this.input.close();
				this.finalSQL = this.finalSQL.trim();
				//remove the final comma from the file
				if (this.finalSQL.endsWith(",")) {
					this.finalSQL = this.finalSQL.substring(0, (this.finalSQL.length()-1));
				}
				this.endImport();
				return lines;
			} catch (SQLException ex) {
				// TODO Auto-generated catch block
				ex.printStackTrace();
			}
		}						
		// returns zero lines, hence it failed
		return 0;
	}
	
	private void endImport() {
		PrintWriter print;
		
		// Write out SQL to file in case of error.. Also later, add code to ask user to continue
		try {
			print = new PrintWriter("output.tmp");
			print.print(finalSQL);
			print.close();
		} catch (FileNotFoundException ex) {
			ex.printStackTrace();
		}
		
		database.startTransactionSegment();
		try {
    	    // Open the file that is the first 
    	    // command line parameter
    	    FileInputStream fstream = new FileInputStream("output.tmp");
    	    // Get the object of DataInputStream
    	    DataInputStream in = new DataInputStream(fstream);
    	    BufferedReader br = new BufferedReader(new InputStreamReader(in));
    	    String strLine;int a = 0;
    	    //Read File Line By Line
    	    while ((strLine = br.readLine()) != null){
    	    	//System.out.println(strLine.substring(0,90));
    	    	database.manualInsert(strLine);
    	    	a++; System.out.println(a);
    	    }
			database.commit();
    	    in.close(); 	    
		} catch (SQLException ex) {
			ex.printStackTrace();
			database.rollback();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		database.endTransactionSegment();
	}
	
	private HashMap createHashMap (DatabaseTables dbaseEnum) throws SQLException {
		ResultSet results;
		HashMap<String, Integer> retVar = new HashMap<String, Integer>();
		
		if (dbaseEnum.equals(DatabaseTables.CallGroups)) {
			results = this.database.getCallGroups();
		} else if(dbaseEnum.equals(DatabaseTables.Numbers)) {
			results = this.database.getNumbers();
		} else if(dbaseEnum.equals(DatabaseTables.Prefixes)) {
			results = this.database.getPrefixes();
		} else if(dbaseEnum.equals(DatabaseTables.TrunkLines)) {
			results = this.database.getTrunks();
		} else {
			results = null;
		}
	    
		while (results.next() && results != null) {
	        // pull out result and add it to HashSet
			retVar.put(results.getString("result"),results.getInt("id"));
	    }
		
		return retVar;
	}
	
	private boolean parseHeader(String line) {
		// read first line and split it by commas
		line = line.toUpperCase().replace("\"", "");
		String[] headerArr = line.split(",");
		this.createHeaderVals(headerArr);
		// Check to make sure all headers are there before proceeding
		if (this.checkHeaderIntegrity()) {
			return true;
		} else {
			return false;
		}
	}
	
	private void createHeaderVals(String[] header) {
		for(int i =0; i<header.length; i++) {
			if (header[i].equals("NPROJCODE")) {
				this.NPROJCODE = i;
			} else if (header[i].equals("CAUTHCODE")) {
				this.CAUTHCODE = i;
			} else if (header[i].equals("DCALL")) {
				this.DCALL = i;
			} else if (header[i].equals("CTIME")) {
				this.CTIME = i;
			}  else if (header[i].equals("NMIN")) {
				this.NMIN = i;
			} else if (header[i].equals("NAMT")) {
				this.NAMT = i;
			} else if (header[i].equals("CCALLGRP")) {
				this.CCALLGRP = i;
			}  else if (header[i].equals("NBR")) {
				this.NBR = i;
			} else if (header[i].equals("SPECIAL")) {
				this.SPECIAL = i;
			} 
		}
	}
	
	private boolean checkHeaderIntegrity() {

		// TODO Add code
		
		return true;
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		/*
		String foo = "CAUTHCODE,DCALL, WOOT, CTIME, NMIN, , CCALLGRP, TRASH, BLAH, NBR, SPECIAL";
		Import main = new Import();
		main.startImport(foo);
		*/
	}

}

and my Database class

Code: 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

public class Database {
	private Connection conn; 
	
	public Database(String userName, String password, String url) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
		try {
			// Imports MySQL class
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			//System.out.println("MySQL class imported");
			// Connect to database
			conn = DriverManager.getConnection(url, userName, password);
            //System.out.println("Database connection established");	
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		}
	}
	
	public Connection getConn() {
		return conn;
	}

	public void createSummaryView(String startDate, String endDate) throws SQLException {
		String sqlStr = "CREATE OR REPLACE VIEW summary (dept_name, dept_num, minutes, amount, num_calls) AS ";
		sqlStr += "SELECT a.dept_name, a.dept_num, SUM(b.minutes), SUM(b.amount), COUNT(b.id) FROM departments AS a, statements AS b, users AS c ";
		sqlStr += "WHERE c.dept_num = a.dept_num AND c.nprojcode = b.nprojcode AND b.call_date > " + startDate + " AND b.call_date < " + endDate + " GROUP BY a.dept_num";
	
		Statement sqlStatement = this.conn.createStatement();
		sqlStatement.executeUpdate(sqlStr);
	}
	
	public void createDeptView(String startDate, String endDate, int deptNum) throws SQLException {
		String sqlStr = "CREATE OR REPLACE VIEW summary_users (dept_name, name, call_date, minutes, amount, number, description) AS";
		sqlStr += " SELECT g.dept_name, CONCAT(b.last_name,\", \", b.first_name) AS name, FROM_UNIXTIME(a.call_date,'%m/%e/%Y %l:%i %p') as call_date, a.minutes, a.amount, CONCAT(\"(\",e.area_code,\") \",e.prefix,\"-\",c.number) AS number, c.description";
		sqlStr += " FROM statements AS a, users AS b, numbers AS c, prefixes AS e, departments AS g WHERE a.call_date > " + startDate + " AND a.call_date < " + endDate;
		sqlStr += " AND b.nprojcode = a.nprojcode AND b.dept_num = " + deptNum + " AND g.dept_num = b.dept_num AND c.id = a.number_id";
		sqlStr += " AND e.id = c.prefix_id ORDER BY dept_name ASC, name ASC, a.call_date ASC";
		
		Statement sqlStatement = this.conn.createStatement();
		sqlStatement.executeUpdate(sqlStr);
	}
	
	public void createFaxView(String startDate, String endDate) {
		
	}
	
	public ResultSet getDepartmentResults(String startDate, String endDate) throws SQLException {
		String deptStr = "SELECT DISTINCT a.dept_num, a.dept_name FROM departments AS a, statements AS b, users AS c WHERE c.dept_num = a.dept_num";
		deptStr += " AND c.nprojcode = b.nprojcode AND b.call_date > " + startDate + " AND b.call_date < " + endDate + " GROUP BY a.dept_num";
		
		Statement sqlStatement = this.conn.createStatement();
		sqlStatement.executeQuery(deptStr);
	    ResultSet result = sqlStatement.getResultSet();
	    
	    return result;
	}
	
	public ResultSet getTrunks() {
		String sqlStr = "SELECT b.id, CONCAT(a.area_code,\" \",a.prefix,\"-\",b.number) AS result FROM `prefixes`  AS a, `outlines` AS b WHERE a.id=b.prefix_id";
		ResultSet result = null;
		try{
			Statement sqlStatement = this.conn.createStatement();
			sqlStatement.executeQuery(sqlStr);
		    result = sqlStatement.getResultSet();
		    
		    return result;
		} catch (SQLException ex) {
			result = null;
			
			return result;
		}
	}
	
	public int insertTrunk(int prefix_id, String number) throws SQLException {
		String sqlStr = "INSERT INTO outlines (prefix_id, number,description) VALUES (" + prefix_id + "," + number + ",\"\")";
		System.out.println(sqlStr);
		// Insert new value
		Statement sqlStatement = this.conn.createStatement();
		sqlStatement.executeUpdate(sqlStr);
		
		// Now get the id created from it
		ResultSet result = sqlStatement.executeQuery("SELECT LAST_INSERT_ID() AS id");
		int id = -1;
		while (result.next()) {
			id = result.getInt("id");
		}
		
		System.out.println("id: " + id);
		return id;
	}
	
	public ResultSet getNumbers() {
		String sqlStr = "SELECT b.id, CONCAT(a.area_code,\" \",a.prefix,\"-\",b.number) AS result FROM `prefixes`  AS a, `numbers` AS b WHERE a.id=b.prefix_id";
		ResultSet result = null;
		try{
			Statement sqlStatement = this.conn.createStatement();
			sqlStatement.executeQuery(sqlStr);
		    result = sqlStatement.getResultSet();
		    
		    return result;
		} catch (SQLException ex) {
			result = null;
			
			return result;
		}
	}
	
	public int insertNumber(int prefix_id, String number) throws SQLException {
		String sqlStr = "INSERT INTO numbers (prefix_id, number) VALUES (" + prefix_id + "," + number + ")";
		// Insert new value
		Statement sqlStatement = this.conn.createStatement();
		sqlStatement.executeUpdate(sqlStr);
		
		// Now get the id created from it
		ResultSet result = sqlStatement.executeQuery("SELECT LAST_INSERT_ID() AS id");
		int id = -1;
		while (result.next()) {
			id = result.getInt("id");
		}
		
		System.out.println("id: " + id);
		return id;
	}
	
	public ResultSet getPrefixes() {
		String sqlStr = "SELECT id, CONCAT(`area_code`, \" \",`prefix`) AS result FROM `prefixes`";
		ResultSet result = null;
		try{
			Statement sqlStatement = this.conn.createStatement();
			sqlStatement.executeQuery(sqlStr);
		    result = sqlStatement.getResultSet();
		    
		    return result;
		} catch (SQLException ex) {
			result = null;
			
			return result;
		}
	}
	
	public int insertPrefix(String area_code, String prefix) throws SQLException {
		String sqlStr = "INSERT INTO prefixes (area_code, prefix) VALUES (" + area_code + "," + prefix + ")";
		// Insert new value
		Statement sqlStatement = this.conn.createStatement();
		sqlStatement.executeUpdate(sqlStr);
		
		// Now get the id created from it
		ResultSet result = sqlStatement.executeQuery("SELECT LAST_INSERT_ID() AS id");
		int id = -1;
		while (result.next()) {
			id = result.getInt("id");
		}
		
		System.out.println("id: " + id);
		return id;
	}
	
	public ResultSet getCallGroups() {
		String sqlStr = "SELECT id, callgrp AS result FROM call_group";
		ResultSet result = null;
		try{
			Statement sqlStatement = this.conn.createStatement();
			sqlStatement.executeQuery(sqlStr);
		    result = sqlStatement.getResultSet();
		    
		    return result;
		} catch (SQLException ex) {
			result = null;
			
			return result;
		}
	}
	
	public int insertCallGroup(String name) throws SQLException {
		String sqlStr = "INSERT INTO callgroup (callgrp) VALUES (\"" + name + "\")";
		// Insert new value
		Statement sqlStatement = this.conn.createStatement();
		sqlStatement.executeUpdate(sqlStr);
		
		// Now get the id created from it
		ResultSet result = sqlStatement.executeQuery("SELECT LAST_INSERT_ID() AS id");
		int id = -1;
		while (result.next()) {
			id = result.getInt("id");
		}
		
		System.out.println("id: " + id);
		return id;
	}
	
	public void manualInsert(String sql) throws SQLException {
		Statement sqlStatement;

		sqlStatement = this.conn.createStatement();
		sqlStatement.executeUpdate(sql);
	}
	
	public void startTransactionSegment() {
		try {
			this.conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void endTransactionSegment() {
		try {
			this.conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void commit() {
		try {
			this.conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void rollback() {
		try {
			this.conn.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
 
TheDude05 said:
But before you flame, there are parts of my Java code that I know are really inefficient (Like writing a file, closing it, then re-opening it) and are just there for time being
Click to expand...
Er, then why is it surprising that the Java version is slower?
 
Because it shouldn't be that substantial. Its not that I'm surprised the Java version is slower either because like I said I haven't profiled it or anything. I just didn't think it would execute that much slower than its php counterpart.
 
I'm starting to work on this project again. Before I dive in, did anyone ever take a look at the code and see anything blatantly wrong?
 
TheDude05 said:
I'm starting to work on this project again. Before I dive in, did anyone ever take a look at the code and see anything blatantly wrong?
Click to expand...

Yes. You should rewrite your code to use a StringBuilder rather than continuously concatenating strings together ( mystring += "blah blah blah" ).

Since strings are immutable in Java each time you do a concatenation it has to make a new string by copying the data from the original as well as copying the data from the thing to append. This doesn't sound slow but on a large scale it will really add up.

More here: http://java.sun.com/developer/JDCTechTips/2002/tt0305.html#tip1

You can find copious amounts of information about this little optimization if you search for "java string concatenation performance" or some variant thereof.
 
You should probably spend some time going through the JDBC Tutorial. Specifically, pay attention to how PreparedStatement works. You shouldn't be creating new strings for your SQL statements each time (no matter how you do it). The statements should just be constants/literals that use a "?" as a placeholder for the value that you want to plug in.
 
kschneid said:
You should probably spend some time going through the JDBC Tutorial. Specifically, pay attention to how PreparedStatement works. You shouldn't be creating new strings for your SQL statements each time (no matter how you do it). The statements should just be constants/literals that use a "?" as a placeholder for the value that you want to plug in.
Click to expand...

Yes, this will help tremendously as well. This way the JDBC driver can signal to the server to compile your statement one time. Plus this greatly lessens the overhead of the driver having to build a new statement every time.
 
You also need to pay attention to what you're doing within loops:

Code: 
 while ((strLine = br.readLine()) != null){
    ...
    database.manualInsert(strLine);
    ...
}

That's a potentially expensive operation to perform, so you may want to see whether or not you can batch those calls: Using PreparedStatement Objects in Batch Updates.

Code: 
while (input.hasNextLine()) {
    ...
    SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy k:m");
    ...
}

There's no reason to recreate that format object each time through the loop. It doesn't even need to be recreated each time the method is invoked. Unless I missed something, it could just be a static field that's initialized once (as long as it's not possible to access it from more than one thread - but that's a different kettle of fish).
 
You must log in or register to reply here.
Back
Top