Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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

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.

  • 2
1 Solution
Are you using the same driver version?

why don't you run it as a stored prec (via CallableStatement)?
BrianMc1958Author Commented:
I thought of that overnight, while watching the Sox.  Will let you know how it goes,  Thanks...
BrianMc1958Author Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now