Anyone know of PreparedStatement problems with SQL 2000 SP's?

Posted on 2007-10-03
Last Modified: 2010-03-30
Dear Experts,

This may be the most obscure question I ever asked here.  

I've got a PreparedStatement running at a customer site that selects only by fields in the primary key.  At this site--and nowhere else--it's running for hours because it's deciding to use an index scan, instead of an index seek.

My table has a primary clustered  key of KeyA, KeyB and KeyC.  And I have a PreparedStatement that says:

SELECT * FROM MyTable where KeyA = ? and KeyB = ? and (KeyC > ? and KeyC < ?)

I'm Profile Tracing the plan, and at that site it's clearly saying "index scan".  They have SQL 2000 SP4.  I run it here, with SQL 2000, no SP, and it runs in 2 minutes, with "index seek".

I have posted this problem first in the MS SQL site, and they've tried like hell but they don't know what it is either.  I'm posting here because of one more clue:  At the cust site, I created a stored procedure that did approximately what my PreparedStatement does, and it ran correctly, with index seek.  So I'm thinking it could possibly be a problem with PreparedStatement in conjunction with one of the SQL 2000 Service Packs.

If any of you can definitively answer this question, I will personally deliver a large stuffed bunny to your door first thing in the morning.

Question by:BrianMc1958
    LVL 92

    Accepted Solution

    Are you using the same driver version?

    why don't you run it as a stored prec (via CallableStatement)?

    Author Comment

    I thought of that overnight, while watching the Sox.  Will let you know how it goes,  Thanks...

    Author Comment

    It's ugly but it works.  For anyone reading this, I'm finding the PreparedStatement is actually slightly faster (maybe 2%)  than the equivalent stored procedure.  Always wondered about that...

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Performance issue while iterating with streams 5 34
    bunnyEars2 challenge 6 46
    changeXy challenge 13 40
    mapShare challenge 13 41
    An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
    Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
    Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
    Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now