SQL Server 2008 - Weird slow query

I have a big table in a database (millions of records, size is ~500GB).

In this table I have many columns, among them there is a date field called FinishTime. There is an index on that field.

Now to the problem:

When I run a simple select query with the condition FinishTime >= <now - 10 hours> - query takes a lot of time to complete.

When I run a simple select query with the condition FinishTime >= <now - 11 hours> - query completes immediately.

The weird thing is that the second query's output should contain all of the first query's output, and more.

Some more information: I'm running these queries through a JAVA application, 开发者_StackOverflowusing the MS JDBC driver. I'm using a PreparedStatement.

The code that runs both queries is the same.

Any ideas?

Thanks

EDIT: More information, and corrections: I have one more relevant field - JobId, integer, which also have an index. The problem seem to happen on those queries (I see the stuck query in the management studio, due to PAGEIOLATCH_SH):

Stuck query:

select * from jobsData (with readuncommitted) where ([FinishTime >= {ts '2011-05-19 09:23:00'}) AND ([JobId]=5)

Query that completes immediately :

select * from jobsData (with readuncommitted) where ([FinishTime >= {ts '2011-05-19 04:23:00'}) AND ([JobId]=5)

As its a prepared statement its probably optimised the query for whatever the first parameter passed in was (I'd guess the 11 hour subtraction)

This can be tested by executing the query with a hint to force recompilation of the query plan.

See - http://msdn.microsoft.com/en-us/library/ms181714.aspx

Its not a pretty solution but if this improves performance, you know its related to this and you can look at adding query hints, or if only a limited range of values will ever be passed in having a statement for each variant.

Edit - From your comment above that says they both run immediately in management studio, it seems to reinforce my feeling on this.

Query plan caching is done by the sqlserver not the java app,

 connection.prepareStatement(sqlstring);

Passes your statement across to the sqlserver, notifies it of the variable places and lets it generate a reusable query plan based upon it when its first run. I doubt (but can't be certain without hunting through docs) that calling it forces a new query plan to be build as that would negate a large part of its benefits.

Instead of parametrising the query try building the query as a string and executing it.