6 Replies - 175 Views - Last Post: 29 March 2014 - 03:41 AM Rate Topic: -----

#1 urban.life  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 30
  • Joined: 28-March 14

Prepared statements and connection to MS SQL database

Posted 28 March 2014 - 10:05 AM

I'm developing an application in JavaFX and a large part of it is connected to MS SQL Server 2008. I'm using Prepared statements for SQL queries/updates.
Ex.:
    public static void execute(String insert){
        try{ 
        connect(); // c is classwide connection variable and connection is made with method connect();
        PreparedStatement prep = c.prepareStatement("INSERT INTO x(name) VALUES(?)");
        prep.setString(1, insert);
        prep.executeUpdate();
        c.close(); 
        }catch(Exception e){
            System.err.println("Sql injection error!");
        }
    }



There is a batch of such statements. I got approx. 40 of these methods doing different queries, execution on SQL server which causes waiting time when doing transition between screens in JavaFX usually about 7 queries have to be executed before going to next screen. This transition happens about once every minute. I got a feeling that closing, opening connection and preparing a statement take most of the time.
I got followig questions:
What would be the best way to optimize this SQL work?
Would it be fine if I just kept the connection opened the whole runtime and have all the statements prepared on it and before executing one just check if connection is opened and reconnecting in case it is closed?
Or is there a better way to enhance the performance of these queries?

Your help is appreciated!

Is This A Good Question/Topic? 0
  • +

Replies To: Prepared statements and connection to MS SQL database

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10180
  • View blog
  • Posts: 37,586
  • Joined: 27-December 08

Re: Prepared statements and connection to MS SQL database

Posted 28 March 2014 - 10:34 AM

Connecting in each method, then closing at the end wastes time. Have one class manage your database interactions. Open a single connection when you start your application. Before you terminate your application, close the connection. Then have methods in this DatabaseManager class to allow for desired interaction.

Also, don't ignore your Exceptions. Log them so you can debug issues as they arise.
Was This Post Helpful? 0
  • +
  • -

#3 urban.life  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 30
  • Joined: 28-March 14

Re: Prepared statements and connection to MS SQL database

Posted 28 March 2014 - 01:36 PM

View Postmacosxnerd101, on 28 March 2014 - 10:34 AM, said:

Connecting in each method, then closing at the end wastes time. Have one class manage your database interactions. Open a single connection when you start your application. Before you terminate your application, close the connection. Then have methods in this DatabaseManager class to allow for desired interaction.

Also, don't ignore your Exceptions. Log them so you can debug issues as they arise.


I'm doing that exact thing, but currently it opens connection for each request, because program can run for 8 hours straight or more and in the mean time it can have long inactivity periods which cause disconnects from MS SQL server after default 3600s idle time, which causes errors.

Don't get me wrong the program runs fine, I would just like to make some enhancements on performance side of SQL.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5642
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: Prepared statements and connection to MS SQL database

Posted 28 March 2014 - 04:45 PM

Hmm...
// why the hell is this static?
public static void execute(String insert){
	try { 
		// A global value?  Named c?  NO
		connect(); // c is classwide connection variable and connection is made with method connect();

		PreparedStatement prep = c.prepareStatement("INSERT INTO x(name) VALUES(?)");
		prep.setString(1, insert);
		// there was a return value here...
		prep.executeUpdate();
		// wait, what if there is an exception?
		// you just leave this open?
		c.close(); 



First, the delay in opening and closing connections is trivial compared to timeouts of stale connections, impacts of dropped connections, etc. In the 80s applications used to just have a single open connection. Web servers introduced the idea of minimum and shortest connection time: it seems to work alright.

I would have something like:
private boolean execute(String sql, String insert) throws SQLException {
	boolean result = false;
	Connection conn = null;
	try { 
		conn = getConnection();
		PreparedStatement prep = conn.prepareStatement(sql);
		prep.setString(1, insert);
		result = prep.executeUpdate();
	} finally {
		if (conn!=null) try { conn.close(); } catch (SQLException e) { }
	}
	return result;
}
		
public boolean execute(String insert) throws SQLException {
	return execute("INSERT INTO x(name) VALUES(?)", insert);
}


Was This Post Helpful? 1
  • +
  • -

#5 urban.life  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 30
  • Joined: 28-March 14

Re: Prepared statements and connection to MS SQL database

Posted 28 March 2014 - 05:10 PM

The class is static so it doesn't have to be initialized every time it is called, which is often. Which I suppose is a bad thing to do?

Exceptions in lines above are left open, because there is practically zero chance of error. Value is selected from predefined lists of values that are correct for insertion, so error can come only from Connection error.

Maybe I forgot to mention it but it's a in-house application that runs on a local network, so I kind of skipped few safety guidelines especially the ones addressing what should be public or private.

Well if the connection time is supposed to be minimal I'm gonna write it by your recommendations.

Thanks.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5642
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: Prepared statements and connection to MS SQL database

Posted 28 March 2014 - 05:55 PM

View Posturban.life, on 28 March 2014 - 07:10 PM, said:

The class is static so it doesn't have to be initialized every time it is called, which is often. Which I suppose is a bad thing to do?


It's generally considered poor OO design. A common solution for this is the Singleton design pattern.

View Posturban.life, on 28 March 2014 - 07:10 PM, said:

Exceptions in lines above are left open, because there is practically zero chance of error.


Consider some way to log that error. Just printing it, while better than hiding it, will probably cause you pain later.

View Posturban.life, on 28 March 2014 - 07:10 PM, said:

in-house application ... so I kind of skipped few safety guidelines especially


Now is the time to be as draconian as possible with those guidelines. You can learn how to apply those concepts in a live application that you have control over. Only doing the right thing when you must means will make it that much harder then. Always do the right thing.
Was This Post Helpful? 0
  • +
  • -

#7 urban.life  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 30
  • Joined: 28-March 14

Re: Prepared statements and connection to MS SQL database

Posted 29 March 2014 - 03:41 AM

Quote

It's generally considered poor OO design. A common solution for this is the Singleton design pattern.

I googled Singleton design and I just wanna make sure I understood it correctlly - basically what it does is that you create a static instance of self in dynamic class and call it with a static function?

Quote

Consider some way to log that error. Just printing it, while better than hiding it, will probably cause you pain later.

I intend to add log writing to local. Is there an easy way to print all the errors to file (ex. like when running a jar file and just doing 2>>log.txt?) I'm using native packaging with netbeans since it's the easiest way to make an exe out of a jar with JRE packed inside.

Quote

Now is the time to be as draconian as possible with those guidelines. You can learn how to apply those concepts in a live application that you have control over. Only doing the right thing when you must means will make it that much harder then. Always do the right thing.

I know, tring my best here but I learn so much that is very hard to apply the corrections to all the classes already made.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1