(Very) preliminary DB (Sql Server) related benchmarks -- Objo vs C#

bgrommes

This isn't a very scientific or comprehensive test, it is just trying to get a general feel for relative performance on some contrived workloads for Objo vs C#.

Note that both Objo Studio and Visual Studio are running the code in debug mode on their respective VMs (and on the same "hardware"), and both sets of benchmarks were done under Windows Server DataCenter Edition 2022 against the Azure DB version of Sql Server. Although both the DB server and the machine running the benchmarks are in the Azure cloud, physically they are on the same local net and the same server farm.

I would expect C# to have better numbers if it were AOT-compiled to native binaries, but that is not all that common for line-of-business deployments. Native binaries would be more the rule if we were comparing two commercially distributed desktop apps.

All benchmarks are the average of 4 runs each, using identical SQL.

Objo version is 26.7.2

Empty while loop iterating 250,000 rows of 22 columns each, literal / non-parameterized SQL:
Objo 367 milliseconds
C# 512 milliseconds

This is timing just the loop, not the (synchronous) DB query.

Objo was just:

While rs.MoveToNextRow()
Wend

C# was:

while (reader.Read()) {
}

I did not expect much difference here since this is an I/O bound rather than CPU bound operation.
Yet Objo was 30% faster than C#. So this result is surprising.
Although Objo was about 4x faster on the first run only. Ignoring the first run, they settled down to
an average of 450 ms for Objo and 504 for C#, which is still 10% in Objo's favor.

Conclusion: Safe to say Objo is not at any disadvantage on this particular raw measure. Without doing a more thorough job than I have time for, we can say that Objo and C# are comparable at this particular (minimalist contrived) task.

Now we add some basic display work, sending one string value per row to the console, using the same SQL:

While rs.MoveToNextRow()
  Print(rs.Column("Name").StringValue)
Wend

while (reader.Read()) {
  Console.WriteLine(reader.GetString("Name"));
}

Result, identical query to first test, average of four runs:
Objo wins big here, but it does so by not displaying all the lines. The console output doesn't show the tail in Objo, whereas the OS console window does.
I've reported the failure to at least show the tail of the output as a (minor) bug.
Objo 3.242 seconds
C# 21.295 seconds

Let's remove the print statements and replace them with simple assignments that are discarded rather than printed:

While rs.MoveToNextRow()
  Var s = rs.Column("Name").StringValue
Wend

while (reader.Read()) {
  var s = reader.GetString("Name");
}

Objo: 1.112 seconds
C#: 0.955 seconds

Advantage goes to C# by 15% here. Pretty respectable. I would guess that real world code would be doing more busy-work, allocations, etc. within the loop and so we can expect more divergence.

Still, this is a good initial showing against a formidable, mature, highly tuned competitor!

I don't see much value in benchmarking parameterized queries as ? vs named parameters and the like would not have a big impact on the client side of things and probably not on the server side either.
The big difference will be in the respective VMs and how they handle similar units of CPU-dominated work. We've gotten a very preliminary and incomplete taste of that with these benchmarks.

MikeB

bgrommes I'm curious which .NET are you using with C#? .NET Framework, .NET 8/9/10? You know - geek stuff! lol

TheTomas

would like a comparison to... cough... xojo... cough

Garry

TheTomas I would but I feel a little dirty touching that IDE at the moment 😆

Garry

Thank you very much for taking the time to put this together and share the results. I really appreciate the effort involved!

These are obviously preliminary benchmarks, as you’ve said, but I’m honestly extremely encouraged by them. C# is a mature, highly optimised platform and a formidable comparison, so seeing Objo perform this competitively (especially when retrieving and processing values across 250,000 rows) is genuinely exciting. Looks like reading all those compiler optimising text books paid off 🙂 !

Database-backed line-of-business applications are an important use case for Objo, and these early numbers strongly suggest that the language has the performance needed to handle them comfortably. There will always be opportunities for further optimisation, but this is a remarkably good initial showing.

bgrommes

Garry Yeah I no longer have Xojo on that machine and my license lapsed probably 3 years ago.

bgrommes

MikeB Yeah I should have mentioned that -- this was .NET 10. Typically when each .NET release comes out every November, I wait a couple of months (let someone else be the pioneer with arrows in their back -- although in truth I've never encountered any bug or instability just from the annual upgrade) and then migrate everything to it. So I'm pretty much on the current release and don't think about the old ones anymore.

It's a relevant question though because there's been a TON of performance tuning done since .NET 6, much less Framework 4.8. .NET Core itself was largely embarked on to re-architect for performance and to retire technical debt (for example, the garbage collection system is now able to do its thing in most cases without the old, simpler "stop the world" approach). So Objo is, in fact, holding up well against the current .NET version :-)

One thing I notice running the profiler against the benchmark code is that each rs.MoveNext() averages 0.001 ms but each call to rs.Column takes 0.002 ms. I was only accessing 1 column so cranking through the lot of them will likely add overhead accordingly. Of course the same is true of .NET, more or less. I will probably rerun that last benchmark accessing maybe 10 columns instead of 1 and see how that scales on both platforms. Keep in mind I already was querying all 22 columns in that view, just not accessing them. I don't imagine the # of columns matters to the benchmarking of the RecordSet iteration itself, only the columns actually accessed. The overhead of all those columns coming from the server should be mostly concentrated in the SelectSQL() call, which I didn't include in my timing. In fact that call was nearly 75% of the total benchmark run time.

ETA: Oh heck, I went ahead and did it so I could get to sleep ;-) Unfortunately the results aren't as good.

To recap from earlier, to access 1 field per row:
Objo: 1.112 seconds
C#: 0.955 seconds

To access 10 fields per row:
Objo: 9.370 seconds
C#: 1.025 seconds

I chose a mix of string, integer, and boolean fields.
Two fields had NULLs, so the code was a bit more involved for those:

Var column As DatabaseColumn = rs.Column("PresentationEntityId") # Access the column once, not twice
Var presentationEntityId As Object = If(column.IsNull,Nothing,column.IntegerValue)
// vs:
int? presentationEntityId = reader.IsDBNull("PresentationEntityId") ? null : reader.GetInt32("PresentationEntityId");

Objo is, very roughly, iterating close to 10x slower when accessing 10 fields vs 1. A clue as to why may be where the docs mention that "Each time you call Column or ColumnAt on a RecordSet, a new snapshot is created - so values remain stable even after advancing to the next row." This sounds inefficient, depending on what "new snapshot" means exactly. It seems like the recordset should have all columns repopulated at each MoveToNextRow() call, so that accessing the values is just retrieving another value.

When you compare the C# timings for 1 vs 10 fields it's only a 5% penalty, so I'm guessing there's room to improve the performance here.

Let us know what you think, @Garry.

Garry

Thanks again for doing this, @bgrommes. Your ten-column result identified a genuine performance problem in the RecordSet/DatabaseColumn implementation, so I’ve now created a reproducible benchmark, investigated the underlying cause and implemented several optimisations.

There are some rather encouraging results 🙂.

What the original benchmark revealed

Your suspicion about the DatabaseColumn snapshot mechanism pointed towards the correct area, although the implementation was doing something slightly different from what the documentation wording might suggest.

SelectSQL() materialises the complete result set before returning the RecordSet. In other words, by the time this begins:

While rs.MoveToNextRow()
    // ...
Wend

all rows and all selected columns have already been read from SQL Server and stored in memory. MoveToNextRow() itself therefore only advances an index through that snapshot.

This also means that the empty-loop comparison is not quite measuring equivalent work unless the C# code also materialises the entire reader before starting its timer. The timed Objo loop is no longer performing database or network I/O.

However, the increase from one accessed field to ten accessed fields was absolutely meaningful. It exposed a real client-side cost in Objo.

Every call to:

rs.Column("Name")

was doing all of the following:

Looking up the column ordinal by name.
Looking up the DatabaseColumn class in the VM.
Creating a new Objo object to represent the column snapshot.
Creating a separate native state object containing its value.
Adding the object and state to a ConditionalWeakTable.
Looking the state up again when .StringValue, .IntegerValue, etc. was accessed.
In the name-based path, creating a capturing error-handling delegate.

For 250,000 rows and ten fields, that means at least 2.5 million DatabaseColumn objects, 2.5 million separate state objects, and millions of weak-table insertions and lookups. The nullable fields add some additional property accesses.

The result snapshot also repeated the column name and a NULL flag inside every stored cell, despite the column name already being available in the result-set metadata.

So no, Objo was not fetching the column from SQL Server again or copying the complete row each time. But the supposedly lightweight snapshot wrapper was considerably more expensive to construct and access than it needed to be.

I did some internal benchmarking

I added a permanent SQL Server benchmark to the repository. It runs SQL Server 2022 in Docker and creates:

250,000 rows.
22 selected columns.
A mixture of strings, integers, Booleans and NULLs.
Zero-, one- and ten-field access cases.
Both name-based Column() and ordinal-based ColumnAt() cases.

It measures result materialisation and row iteration separately, including allocated bytes and garbage-collection counts. Every value-access test accumulates a checksum so the work cannot simply be discarded.

The figures below are the median of three measured iterations after warm-up. They were recorded on my M4 Max MacBook Pro using macOS 26.5.2, .NET 10.0.7 and Objo’s Release VM execution profile.

The absolute figures are therefore not directly comparable with your Windows/Azure/debug-profile results, but the before-versus-after comparison uses the identical machine, container, data and benchmark.

Before and after

Scenario	Before	After	Improvement
Empty iteration	62.971 ms	24.913 ms	60.4% faster
One field by name	120.110 ms	97.769 ms	18.6% faster
Ten fields by name	1,680.865 ms	673.611 ms	59.9% faster
One field by ordinal	119.059 ms	93.736 ms	21.3% faster
Ten fields by ordinal	1,492.671 ms	626.572 ms	58.0% faster

The ten-name case is the closest equivalent to the interesting part of your benchmark. Its loop time fell from approximately 1.68 seconds to 0.67 seconds.

The allocation results also demonstrate just how allocation-heavy this path was:

Measurement	Before	After
Ten-name loop allocations	1.826.3 GB	1.456.8 GB
Ten-name Gen-0 collections	211	174
Result materialisation time	390.7 ms	296.1 ms
Result materialisation allocations	289.1 MB	201.1 MB

Result materialisation is now approximately 24% faster and allocates about 30% less memory. The ten-field loop is approximately 60% faster.

What changed

This first series of optimisations deliberately makes no changes to the public Objo API.

DatabaseColumn is still an immutable snapshot, so this remains valid:

rs.MoveToNextRow()
Var firstName As DatabaseColumn = rs.Column("Name")

rs.MoveToNextRow()

// Still contains the first row's value.
Print(firstName.StringValue)

Internally, however:

DatabaseColumn is now represented by a specialised Objo instance that holds its native value directly.
The separate state object and ConditionalWeakTable have been removed.
The DatabaseColumn runtime class is resolved once per RecordSet, rather than once per field access.
The capturing error-handling wrapper has been removed from the already-materialised Column() path.
Stored cells no longer repeat their column name and NULL flag. Column metadata is stored once per result set.

Where this leaves us

There is still room for optimisation. Even with these improvements, the existing public API must create a stable DatabaseColumn object for every Column() call. That is why the ten-field benchmark still allocates around 1.46 GB while creating 2.5 million snapshots.

An allocation-free bulk-data API could go further, but I have intentionally not added one yet. I wanted first to optimise the existing API without breaking source compatibility or changing its semantics, then measure the result properly.

The roughly 60% reduction in the ten-field loop is a very worthwhile first improvement.

Thank you for staying up late and running that extra test! It directly resulted in a significant optimisation for the next Objo Studio release.

https://feedback.objo.dev/bug/820

bgrommes

Garry Thanks, Garry. I agree with the systematic approach and that this is a great start.

Yes, I benchmarked only the loop, not the DB query.

I suspect this is not strictly a SqlServerDatabase issue -- these or similar changes would likely benefit all supported databases, since what we're dealing with here is the firehose cursor implementation, not the mechanics of getting the data streamed by the DB server.

I haven't looked at the Microsoft open source for SqlDataReader but I have always assumed what is going on with that is that each column value is a boxed object, probably represented internally for each row by an object[]. At least an object is what the SqlDataReader instance indexer returns without using the helper methods (e.g. GetInt32(), GetString()). The column metadata isn't part of it. So for example an Sql int is converted to System.Int32 and stored ("boxed") in an Object. It the column happens to be NULL, regardless of the column type, the special singleton instance DBNull.Value is stored in the Object instead. So retrieving the value becomes a simple assignment and cast, or calling the relevant helper to do it for you. Calling the wrong helper (e.g. GetString() on an int column) would be an error rather than a best-effort to return something relevant like it is in Objo (or Xojo). Calling the correct helper when the column value is NULL would also be an error.

If you are not writing code against a known query (column names and/or types aren't known in advance) then there's a GetSchemaTable() method not unlike Objo's TableColumns() method so that you can obtain all that info in a one-time call and use it as needed throughout your manipulations.

For this reason, I never understood the Xojo RecordSet design putting that per-result column metadata in each and every column value, unless internally it was using something like a computed getter delegating to some singleton table column metadata class or similar. It just did not seem like a clean design.

I'm sure you know all this, but I'm saying it for the benefit of others who might read this, now or down the road. It looks like you are already on the path of, as you say, column metadata being stored once per result set, rather than per-column.