another java / sql question?

Question

your data query program passed all performance tests during development. however during production, it grew slower and slower as more people started using it. which one of the following is the most likely to result in maximum performance improvement, assuming you have not already implemented it?
I am thinking b or c


a.) Replace dynamic and precompiled SQL statements w/ stored proCEDURES.

b.) Use StringBuffer objects instead of instances of String when building SQL queries as string in your code.

c.) Use connection pooling, either through a datasource or by implementing your own.

d.) minimize queries involving joints.

e.) create all ResultSet objects to be TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

Answer 1

Here are my thoughts on the various choices:

a) This will reduce resources used to compile and cached previously-compiled SQL. Always use bind variables (vs. build dynamic SQL with variables values embedded). Depending on how many unique SQL strings you have, this can be a big win.



b) This is on the application-side. Using StringBuffer will reduce the overhead to allocate/deallocate objects in the heap by producing less garbage. This is a good idea, but usually the impact of the amount of garbage you can create while building a SQL statement is overwhelmed by the execution time of the SQL itself.



c) Never ever build a database application without using connection pooling. Creating a new connection tends to be fairly resource-intensive.



d) You can redesign your schema to be less normalized (and therefore require fewer joins), but this does not necessarily mean your performance/scalability will be any better.



e) Not a bad idea if you are going to be just scanning the results, but probably is not going to give a big performance boost.



My experience (too many years to mention) says the best answer is "c" - Good luck!

Answer 2

Well, lets see... this is what I think:



A - is a winner, instead of processing on the web-server that handles all sorts of requests (that don't necessarily involve sql queries). Do all the processing on the database layer.



B - I thought StringBuffer objects took up more processing and memory than instances of strings (depending on how many instances), then again it depends on various things (like your loops). You can always call System.GC() in order to empty any garbage produced. I may be wrong about the amount of resources StringBuffer uses...



C - Not sure about this one... However connection pooling, from what I know, is a good idea (programmaticaly anyways)



D - How? you would then have to implement this using java, using loops, on the web-server (that would result in slow-downs)



E - Not sure about this one either...



Just throwing it up there for others to tear it down! ;)

Answer 3

even if that is utilising sq. server because the database then each and each and every time the mission calls the database your java interpreter will *****. in case you could attempt to open up a connection to the unique database from the hot community so as that the making use of can talk with it from the hot area (if it extremely is a threat) except that you'll ought to get sq. server in that surroundings. wish that permits RJ

Answer 4

I use connection pooling for a huge application accessed hundreds of times a day and the speed of the functionality hasn't ever decreased. Give it a shot!