Two Laptop Bag*
The moose likes JDBC and the fly likes VB vs Java stored procedure performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "VB vs Java stored procedure performance" Watch "VB vs Java stored procedure performance" New topic
Author

VB vs Java stored procedure performance

Panky Priyadarshi
Greenhorn

Joined: Mar 22, 2007
Posts: 4
Hi, I executed a migration project from VB to web based J2EE. My database remained the same i.e. SYBASE. I have stored procedures in my database being ececuted by my application. Now when that same stored proc is being executed from VB, it takes 5 mins but my web application takes 10-12 mins

Does anyone has a clue as to whats the difference??? how can i improve my stored proc performance???
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Stored procedures live in the database, so nothing should have changed with them. You probably need to do some investigation to see how long they take independent of your application and see if there are any problems. A stored procedure that takes five minutes to execute is very slow. I don't know what tools Sybase provide, but you should be able to do some query plan stuff.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
That seems odd. Try using the jamon jdbc driver mentioned in the faq below, and see if that tells you something. Perhaps it could be a jdbc driver setting.

'set showplan on' can tell you if your sybase proc is properly using indexes.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Panky Priyadarshi
Greenhorn

Joined: Mar 22, 2007
Posts: 4
I checked out the JDBC drivers used for connection between application server and database. Its using Type 4 driver which is supposed to be the best. After using sp_who and sp_lock i can see that the process is acquiring lock on a table having 7 million rows for a long time. The lock on that table is at page level. Now somehow invoking the Stored Proc from VB requires table lock for less time but Java requires it for more time under the same conditions(i.e. same load on servers etc)....i dont understand that!!!there has to be something different about the way the 2 connect...probably the drivers used are different....i am using com.sybase.jdbc2.jdbc.SybConnectionPoolDataSource jdbc provider....can i use something else which improves performance....
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
I don't think such an issue is a jdbc issue. are you sure the exact same query is being run under both circumnstances against the same server? you could use the jtds driver against sybase, to see if it is a driver issue. it is open source and can be found at: http://jtds.sourceforge.net/
Panky Priyadarshi
Greenhorn

Joined: Mar 22, 2007
Posts: 4
Hi Steve, it is a Sybase stored procedure which was kept common when we migrated from VB to web.
 
 
subject: VB vs Java stored procedure performance
 
Similar Threads
Caching EJB stored procedure calls.
Transaction Rolledback Exception in Websphere 3.5
VB vs J2EE
SQL's Execution from Springs taking long time.
How to determine Primary Key of New Record