Hi all.

I have a strange situation in our system which causes us a major problem. We have a query running on a big data table, joined with few fairly small tables. The query uses a date range. Our system is Java-Hibernate based, and when the system gets to the point of running the query it just gets stuck. When looking at the database from SQL Server profiler it looks like the database is busy, but not stuck (no deadlocks or timeouts). But here's the really strange part:

When copying the query- using copy/paste without changing a word, and using the EXACT time range of the hibernate query (the time range is past as a parameter)- the query takes MILLISECONDS! No problems at all.

We thought this has something to do with the date parameter sent to the query, so we altered the query in a way that it doesn't send a date as a parameter (but rather sends an integer). It WORKED! The query took 2 seconds to run.

The problem doesn't appear in Oracle databases, only SQL Server.

Anyone has any idea or insight on the matter? It smells like a Hibernate/MSSQL bug.... can anyone provide any details?

Thank you!
Nili