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???
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.
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....
Joined: Jun 26, 2002
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/
Joined: Mar 22, 2007
Hi Steve, it is a Sybase stored procedure which was kept common when we migrated from VB to web.