Executing SP in MSSQL takes forever via JDBC?!

Hi all,
I need to execute a stored procedure in our database server, MS SQL Server
2000 and it takes forever...
I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
My code:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
cs.setString(1,"param1");
cs.setString(2,"param2");
long start = System.currentTimeMillis();
ResultSet rs = cs.executeQuery();
System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
ms");
...
Every time I execute this piece of code, it takes between 45-50 seconds...
I have tried to execute my SP from DBVisualizer(an app using the same JDBC
driver) and it is the same result. But, when I execute my SP from Query
Analyzer it takes less than a second?! I have also tried executing the SP
from TOAD for SQL Server, and then the execution time also is less than a
second. Why? What have I missed? Does anybody recognize this problem?
It is not me personally that has wriiten the SP, so I do not really know
what it does and how it looks, but I know that the SP I'm calling is calling
another SP that is creating a couple of temporary tables while it is
executing...
Greatfull for any suggestions...
Cheers
//Anders =)
anders.hedstrom wrote:

> Hi all,
> I need to execute a stored procedure in our database server, MS SQL Server
> 2000 and it takes forever...
> I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
> My code:
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> Connection conn = DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
>
> CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
> cs.setString(1,"param1");
> cs.setString(2,"param2");
Try these two things:
1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
2 -
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
Joe Weinstein at BEA

> long start = System.currentTimeMillis();
> ResultSet rs = cs.executeQuery();
> System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
> ms");
> ...
>
> Every time I execute this piece of code, it takes between 45-50 seconds...
> I have tried to execute my SP from DBVisualizer(an app using the same JDBC
> driver) and it is the same result. But, when I execute my SP from Query
> Analyzer it takes less than a second?! I have also tried executing the SP
> from TOAD for SQL Server, and then the execution time also is less than a
> second. Why? What have I missed? Does anybody recognize this problem?
> It is not me personally that has wriiten the SP, so I do not really know
> what it does and how it looks, but I know that the SP I'm calling is calling
> another SP that is creating a couple of temporary tables while it is
> executing...
> Greatfull for any suggestions...
> Cheers
> //Anders =)
|||
> Try these two things:
> 1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
> 2 -
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
> Joe Weinstein at BEA
Hi Joe,
thanx alot for your tip! This fixed my problem;
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
It would be quite interesting to know why it takes so long to execute the SP
when using CallableStatement and PreparedStatement...
By the way, the lowercase 's' on sendStringParametersAsUnicode made no
difference.
Once again Joe, thanx!!!
Cheers
//Anders =)