ServiceStack.OrmLite: Slow write/reads?

Question

UPDATE June 30 This question made a more clean benchmarking, and Mythz found an issue and resolved it: ServiceStack benchmark continued: why does persisting a simple (complex) to JSON slow down SELECTs?

---

ARE WRITE/READ SPEEDS REASONABLE?

Im my trials with OrmLite, I am going to test to convert all our current data/objects from our own implementation for saving to database, and switch over to OrmLite.

However, I did a simple benchmark/speedtest, where I compared our current serialization and write to db as well as read from db and deserialize.

What I found was that ServiceStack is much slower than how we currently do it (we currently just serialize the object using FastSerializer, and write the byte[] data to a BLOB field, so its fast to write and read, but of course obvious drawbacks).

The test I did was using the Customer class, that has a bunch of properties (used in our products, so its a class that is used every day in our current versions).

If I create 10 000 such objects, then measure how long it takes to persist those to a MySql database (serialization + write to db), the results are:

---

UPDATE

As the "current implementation" is cheating (its just BLOBing a byte[] to database), I implemented a simple RelationDbHandler that persists the 10 000 objects in the normal way, with a simple SQL query. Results are added below.

---

WRITE 10 000 objects:
Current implementation: 33 seconds
OrmLite (using .Save): 94 seconds
Relational approach: 24.7 seconds
READ 10 000 objects:
Current implementation: 1.5 seconds
OrmLite (using Select<>): 28 seconds Relational approach: 16 seconds

I am running it locally, on a SSD disk, with no other load on CPU or disk. I expected our current implementation to be faster, but not that much faster.

I read some benchmark-stuff on ServiceStack webpage (https://github.com/ServiceStack/ServiceStack/wiki/Real-world-performance), but most of the links area dead. Some plain that reading 25 000 rows takes 245 ms, but i have no idea what a row looks like.

Question 1: Are there any benchmarks I can read more about?
Question 2: The Customer object is specified below. Does mythz think the write/read times above is reasonable?

TEST CASE: This is the Customer objects as it looks in the database after OrmLite created the table. I only populate 5 properties, one is "complex" (so only one field has a JSON serialization represenation in the row), but since all fields are written, I dont think that matters much?

Code to save using OrmLite:

 public void MyTestMethod<T>(T coreObject) where T : CoreObject
    {
        long id = 0;
        using (var _db = _dbFactory.Open())
        {
            id = _db.Insert<T>(coreObject, selectIdentity: true);
        }           
    }

Code to read all from table:

 internal List<T> FetchAll<T>()
        {
            using (var _db = _dbFactory.Open())
            {
                List<T> list = _db.Select<T>();
                return list;
            }
        }

Answer 1

Use Insert() for inserting rows. Save() will check if the existing record exist and update it if it does, it also populates Auto Increment primary keys, if any were created.

There's also InsertAsync() APIs available but Oracle's official MySql.Data NuGet package didn't have a proper async implementation in which case using https://github.com/mysql-net/MySqlConnector can yield better results by installing the ServiceStack.OrmLite.MySqlConnector NuGet package and using its MySqlConnectorDialect.Provider.

You'll also get better performance using .NET Core which will use the latest 8.x MySql.Data NuGet package.

Note: The results in https://github.com/tedekeroth/OrmLiteVsFastSerializer are not comparable, which is essentially comparing using MySql as a NoSQL blob storage vs a quasi relational model in OrmLite with multiple complex type blobbed fields.

In my tests I've also noticed several serializaation exceptions being swallowed which will negative impact performance, you can have Exceptions bubbled by configuring on Startup:

OrmLiteConfig.ThrowOnError = JsConfig.ThrowOnError = true;