File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Prepared Statement Hanging. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Prepared Statement Hanging." Watch "Prepared Statement Hanging." New topic

Prepared Statement Hanging.

Sandeep Jain
Ranch Hand

Joined: Oct 25, 2000
Posts: 124
Hello Guys,

I am facing a strange problem with JDBC Connection.

I have used, oracle9i.jar,ojdbc14.jar as the Driver.

The Program is as follows.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import java.sql.ResultSet;

public class x {

public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection("jdbc racle:thin:@ 00FR1", "xyz", "xyz");

String query = "select * from cdr where phone_number=? AND day_of_year between 291 AND 292 " ;
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();

/* String query = "select * from cdr where phone_number='+552192223293' AND day_of_year between 291 AND 292" ;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
When I Comment the Prepared Statement and use normal Statement the query comes out very fast however When I use Prepared Satement it just hangs .

The Load in the Table is around 2000 million. and the Phone number is indexed. and day_of_year is partitioned.

Any help is appriciated as to why Prepared Staement is hanging.

Thanks in Advance

Sandeep Jain

Try and Try Till u succeed<br /> <br />Sandeep Jain
Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
You have a table with 2 billion entries? Impressive.

Can you run an analyzer to see what plan is actually executed? Maybe the index is disregarded for some reason, and a table scan would take some time for such a big table.
[ November 07, 2005: Message edited by: Ulf Dittmer ]
I agree. Here's the link:
subject: Prepared Statement Hanging.
It's not a secret anymore!