Querydsl is generating a stored procedure with a nvarchar
parameter for the column, but the table's column type is varchar
. This way MS-SQLServer is not able to use its index, which remarkably increases query execution time.
The metadata which is used by Querydsl is generated using Querydsl's MetaDataSerializer
(ant target).
How do I force Querydsl to use a varchar
stored procedure parameter for the column?
DB table:
TableX with three columns: id (int), ColA (varchar) and ColB (nvarchar)
Generated metadata:
public class QTableX extends com.mysema.query.sql.RelationalPathBase<QTableX> {
private static final long serialVersionUID = 1142355320;
public static final QTableX TableX= new QTableX("TableX");
public final StringPath id = createNumber("id", Integer.class);
public final StringPath colA= createString("ColA");
public final StringPath colB= createString("ColB");
public QTableX(String variable) {
super(QTableX.class, forVariable(variable), "dbo", "TableX");
}
public QTableX(Path<? extends QTableX> path) {
super((Class)path.getType(), path.getMetadata(), "dbo", "TableX");
}
public QTableX(PathMetadata<?> metadata) {
super(QTableX.class, metadata, "dbo", "TableX");
}
}
Querydsl:
SQLQuery query = new SQLQueryImpl(new SQLServerTemplates());
query.from(QTableX.TableX).where(QTableX.TableX.colA.eq("FieldName")
.and(QTableX.TableX.colB.like(".1.")));
generated sql:
declare @p1 int
set @p1=NULL
-- @P0 should be varchar (not nvarchar)
exec dbo.sp_prepare @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000)',N'select TableX.id
from TableX TableX
where TableX.ColA = @P0 and TableX.ColB like @P1 escape ''\''',1
exec dbo.sp_execute @p1, N'FieldName', N'.1.'