Re: Executing SP in MSSQL takes forever via JDBC?!
From: Joe Weinstein (joeNOSPAM_at_bea.com)
Date: 11/29/04
- Next message: jdnospam_at_hevanet.com: "Looking for what causes this error"
- Previous message: anders.hedstrom: "Executing SP in MSSQL takes forever via JDBC?!"
- In reply to: anders.hedstrom: "Executing SP in MSSQL takes forever via JDBC?!"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Nov 2004 08:27:55 -0800
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.SQLServerDriver");
> Connection conn = DriverManager.getConnection
>
> ("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=secret;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 =)
- Next message: jdnospam_at_hevanet.com: "Looking for what causes this error"
- Previous message: anders.hedstrom: "Executing SP in MSSQL takes forever via JDBC?!"
- In reply to: anders.hedstrom: "Executing SP in MSSQL takes forever via JDBC?!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
