#723 Prepared statements with like operator takes a long time on SQL Server 2008

[Jochus]

Hi,

I'm using jTDS in combination with JBoss 6.1.0 (JPA & Hibernate) to query a database. I've noticed a query (SELECT * FROM MYTABLE WHERE mycolumn LIKE ?) is running for 10 seconds using jTDS, but for 0,5 seconds in SQL Server Studio Management.

The difference is that jTDS is first creating a prepared statement from my query (using sp_prepare), and then executes the prepared statement using sp_execute. But the sp_execute procedure takes 10 seconds.

When I execute the sp_prepare and sp_execute in SQL Server Studio Management, it also takes 10 seconds:

DECLARE @P1 int;
EXEC sp_prepare @P1 output,
N'@P1 VARCHAR(255)
N'SELECT * FROM MYTABLE WHERE mycolumn LIKE ?';
EXEC sp_execute @P1, N'test';
EXEC sp_unprepare @P1;

Is there a way to NOT us prepared statements?

[stardif]

We have a similar issue. Would be nice to have some feedback, workarounds, and options.

[momo]

Have you already tried setting connection parameter prepareSQL=0? This should avoid any preparation.

Cheers,
momo

[babi]

Hi Jochus.
Your problem could be related to LIKE operator. JTDS adds "N" (represents unicode character) before the compared string.
.. EXEC sp_execute @P1, N'test';
Try it without that N.