Hi,
I have a scenario where Ormlites SingleAsync<T> is about 50 times slower than Single<T> when fetching data from a table with a nvarchar(max) field containg a lot of chars.
This happens with SqlServer2012Dialect, but not with SqliteDialect.
Note: I have found a workaround using Dappers QuerySingleAsync method, which is fast in this scenario.
When testing with a string length of 1000000 against an otherwise empty table, these are typical results:
Testing with text length 1000000 using SqliteOrmLiteDialectProvider
db.Single<T>: 35ms
db.Single<T>: 2ms
db.Single<T>: 2ms
db.Single<T>: 0ms
db.Single<T>: 0ms
await db.SingleAsync<T>: 5ms
await db.SingleAsync<T>: 0ms
await db.SingleAsync<T>: 0ms
await db.SingleAsync<T>: 0ms
await db.SingleAsync<T>: 0ms
Testing with text length 1000000 using SqlServer2012OrmLiteDialectProvider
db.Single<T>: 14ms
db.Single<T>: 7ms
db.Single<T>: 6ms
db.Single<T>: 6ms
db.Single<T>: 5ms
await db.SingleAsync<T>: 433ms
await db.SingleAsync<T>: 385ms
await db.SingleAsync<T>: 391ms
await db.SingleAsync<T>: 334ms
await db.SingleAsync<T>: 292ms
I am not sure how I best can provide something for you to reproduce, but below is all the code needed to reproduce in a .net 8 Console app, using ServiceStack 8.4.0, if you have access to SQL server in some form:
I have tested this on SQL Server LocalDB, SQL Server 2022 on Docker Linux and SQL Server 2022 on Windows.
//Program.cs
using System.Diagnostics;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
var text = new string('x', 1000000);
var iterations = 5;
var connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDb;";
var factories = new List<OrmLiteConnectionFactory> {
new (":memory:", SqliteDialect.Provider),
new(connectionString, SqlServer2012Dialect.Provider),
};
foreach (var dbFactory in factories)
{
await RunAsync(text, iterations, dbFactory);
}
static async Task RunAsync(string text, int iterations, OrmLiteConnectionFactory dbFactory)
{
Console.WriteLine($"Testing with text length {text.Length} using {dbFactory.DialectProvider.GetType().Name}");
using var db = dbFactory.Open();
if (dbFactory.DialectProvider.GetType() == SqliteDialect.Provider.GetType())
{
db.CreateTable<MyTable>();
}
var id = db.Insert(new MyTable { Contents = text }, selectIdentity: true);
var sw = new Stopwatch();
var timerResults = new List<string>();
for (var i = 0; i < iterations; i++)
{
sw.Restart();
var result = db.Single<MyTable>(x => x.Id == id);
sw.Stop();
var errror = (result?.Contents.Length ?? 0) == 0 ? "NO CONTENT!" : "";
timerResults.Add($"db.Single<T>: {sw.ElapsedMilliseconds}ms {errror}");
}
for (var i = 0; i < iterations; i++)
{
sw.Restart();
var result = await db.SingleAsync<MyTable>(x => x.Id == id);
sw.Stop();
var errror = (result?.Contents.Length ?? 0) == 0 ? "NO CONTENT!" : "";
timerResults.Add($"await db.SingleAsync<T>: {sw.ElapsedMilliseconds}ms {errror}");
}
db.Delete<MyTable>(x => x.Id == id);
Console.WriteLine($"{string.Join(Environment.NewLine, timerResults)}\n");
}
public class MyTable
{
[AutoIncrement] public int Id { get; set; }
[StringLength(StringLengthAttribute.MaxText)]
public string Contents { get; set; }
}
/*
CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Contents] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
*/
Best regards, Anders
and in that case, let me know if there is anything I can do to help.