Slow parameterised query

[meindert <me...@eduflex.com>]

Hi,

 

This is probably more a JDBC (MS Sql 2005, sqljdbc 1.2)  question then a
ibatis question.

I've been noticing that parameterised queries are sometimes dead slow..

 

I have the following Sql map what takes more than a minute to execute
(returning 10 rows);

select distinct actmonth from ytdtotals WITH(NOLOCK) where
dealercode=#value# order by actmonth desc

 

Changing it to non parameterised fixed that;

select distinct actmonth from ytdtotals WITH(NOLOCK) where
dealercode='$value$' order by actmonth desc

 

The ytdtotals table is huge but indexed on the actmonth, running the query
straight against the DB isn't giving me any issues;

select distinct actmonth from ytdtotals where dealercode='FMC92031' order by
actmonth desc 

 

Running the query parameterised is also not giving any issues;

DECLARE @sql nvarchar(2048)

DECLARE @dealercode nvarchar(512)

DECLARE @paramlist nvarchar(512)

 

SET @sql='select distinct actmonth from ytdtotals where
dealercode=@xdealercode order by actmonth desc'

SET @dealercode= 'FMC92031'

SET @paramlist='@xdealercode varchar(10)'

 

EXEC sp_executesql @sql,@paramlist ,   @dealercode

 

Anybody noticing this same issue and know a way to fix it?

 

Meindert Hoving