Ormlite passing string parameter as varchar(max) [Performance]

ServiceStack.OrmLite SQL Server
1

Hello,
we are using ServiceStack OrmLite to access a MS SQL database. At the moment we are fine tuning the systems and analyzing the performance.
In this process, we found out that if we are requesting something from the database by supplying string values, OrmLite always passes the strings as varchar(max) no matter what length the queried field has.
Is there a way to change the SQL Parameter data type?

2

You can change the behavior of string properties by registering a custom Type Converter.

You’ll likely want to subclass the existing SqlServerStringConverter and customize what you want, then register it with the Dialect Provider you’re using, e.g:

SqlServerDialect.Provider.RegisterConverter<string>(new MyCustomStringConverter());

Overriding GetColumnDefinition() changes the column definition the table is created with whilst overriding InitDbParam() can be used to change the DB parameter used.

3

Is there any possibility to change the behavior depending on the column?
When I get to the InitDbParam method, I get @0 as parameter and type string - so no way to reference the defined string length for the Property in the class definition.

4

No, the Type converters are used to configure all db params for that type, they’re not column specific.

5

Thank you.
Any other hints or ideas how it could be possible to limit the length of the varchar to the length of the column or to the length of the given string?
On huge tables in MSSQL this has a really big impact as the engine does not use the index.

6

If you’re querying with a typed SqlExpression, you could inspect and modify the populated params for the query, e.g:

var q = db.From<Person>()
    .Where(x => x.Age == 27)
    .Select(x => x.LastName);    

q.Params //Inspect & Modify Params:

The only way to intercept Updates/Inserts commands and inspect/modify their populated params is to use a BeforeExecFilter

OrmLiteConfig.BeforeExecFilter = dbCmd => ...

Alternatively you could override a SqlServer Dialect provider as done in SqlServerOrmLiteDialectProviderVersions.cs which will let you intercept populated commands by overriding the Execute* methods which you would then use instead:

public class MySqlServerDialectProvider : SqlServer2016OrmLiteDialectProvider 
{
    public new static MySqlServerDialectProvider Instance = new();
    // Override methods    
}

var dbFactory = new OrmLiteConnectionFactory(
    connectionString,  
    MySqlServerDialectProvider.Instance);
7

Thank you very much - I think that will help.
The question is mainly about Select.
I will take the inspect and modify option for the parameters and test with that.

1 Like