Hibernate version:
3.2.0.ga
Name and version of the database you are using:
MS SqlServer 2000
My DBAs are reporting the following issue with use of parameterised queries as produced by Hibernate. Sorry that isn't directly relevant to Hibernate but I know of no other forum where I can find a community with same skills and experience. Unfortunately if I can't get around this problem our adoption of Hibernate will be bought into question.
A fairly complex Criteria query in Hibernate is producing SQL something like the following:
Code:
declare @P1 int
exec sp_prepare @P1 output, N'@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 datetime', N'
select top 50
... {various column names deleted} ...
from ftxFnclTxn this_
left outer join clsClasn clasn2_ on ...
... {various joins deleted} ...
where (this_.ftxOrigContraRplcInd is null or not this_.ftxOrigContraRplcInd in (@P1,@P2))
and this_.ftxOrigSigningNmbr like @P3 and this_.ftxOrigSigningDate=@P4 order by this_.ftxSID
desc'
exec sp_execute @P1, 'C', 'X', '5%', 'Sep 22 2006 12:00:00:000AM'
exec sp_unprepare @p1
What they're telling me is that due to the way the SqlServer cost-based optimiser is working:
1. The sp_prepare creates an execution plan best suited to execute the query based upon the statistics present for the tables but the plan will not be optimal for all combinations of parameters
2. sp_execute executes the sql with the supplied parameters but the plan is not optimal and is not recompiled.
This is due to un-even distribution of data in the parameterised columns.
Execution times are varying between 3 seconds to 3 minutes compared to the stored-proc equivalents.
Any ideas on how I can influence SqlServer to do the right thing? I.e. reconstruct the query plan based on the parameters that are passed without having to resort to throwing out Hibernate and using Stored Procs instead?
Thanks!