August 27, 2008 at 7:12 am
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
August 27, 2008 at 7:21 am
It sounds like you're having the usual "parameter sniffing" problem. Look that phrase up, there are ways to test for it and solve it, both on this site and others.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply