Test Improvements & Benchmarks for SqlKata Compiler #738

faddiv · 2025-02-08T14:50:57Z

Hi Ahmad,

We've been using SqlKata in our project, and I’ve started exploring its internals.
I thought the compiler part could be improved significantly both for speed and memory usage.
However, to rewrite it safely, I first wanted to improve the tests and add some benchmarks.

This PR shows what I did in this regard. Please review this PR, and let me know if you are interested in any part of my work. (I also have a branch in which I implemented a new compiler, just enough to run the benchmarks.)

How to run the benchmark

In IDE:

Set QueryBuilder.Benchmarks as the startup project.
Set Build mode to release. (BanchmarkDotNet only runs on release compilation)
Start the project without debugging.
Command line:
From the solution folder (on Windows):
dotnet run -c Release --project .\QueryBuilder.Benchmarks\QueryBuilder.Benchmarks.csproj

The reports are generated into the .\BenchmarkDotNet.Artifacts\ folder at the solution level.

Meanwhile, it is recommended to close everything else, and do as little as possible so other processes don't affect the results.

Summary of Changes:

I broke down the tests that check the same input with multiple engines, with Theory and InlineData attributes. This way it can be seen if anything fails separately, and better visible if any test is missing.
- At some places, I didn't do this breakdown everywhere since I was unsure if it was really worth it. (Base class functionality was tested.)
- I replaced TestCompilersContainer completely. I was not sure in which direction you wanted to move these tests, but I saw somewhere that you wished to deprecate them.
Created separate tests for the bindings.
Created some benchmarks with three example queries.
- These benchmarks only test the compiler part.
- In benchmarks, I usually create pre-tests to validate whether they return the correct results, which I did here as well.
Also, it was my goal, to make the compiler easily replaceable in the tests.

Looking forward to your feedback!

ahmad-moussawi · 2025-02-12T20:13:22Z

Hey @faddiv thanks for your contribution, I would take a bit of time to review it, but it seems interesting,
can you please add to the PR description how can I run the benchmarks?

faddiv · 2025-02-13T07:08:09Z

Hi @ahmad-moussawi ,

I added the description. It simply can run with the following command: dotnet run -c Release --project .\QueryBuilder.Benchmarks\QueryBuilder.Benchmarks.csproj

This workflow automates the build and test process for a .NET project on push and pull request events to the main branch.

Copilot

Pull Request Overview

This PR significantly improves test infrastructure and adds performance benchmarking for SqlKata's query compiler by restructuring existing tests to use Theory and InlineData attributes and introducing BenchmarkDotNet-based performance tests.

Replaced centralized compiler container with direct compiler creation methods for better test isolation
Refactored test methods to use parameterized tests with Theory and InlineData for better test granularity
Added comprehensive benchmark project with memory diagnostics for compiler performance analysis

Reviewed Changes

Copilot reviewed 48 out of 48 changed files in this pull request and generated 3 comments.

File	Description
sqlkata.sln	Adds benchmark project to solution
QueryBuilder.Tests/*.cs	Restructures tests from single multi-engine methods to parameterized tests
QueryBuilder.Tests/Infrastructure/	Replaces TestCompilersContainer with direct compiler creation methods
QueryBuilder.Benchmarks/	New benchmark project with infrastructure and test validation

_{Tip: Customize your code reviews with copilot-instructions.md. Create the file or learn how to get started.}

Copilot · 2025-09-27T03:51:03Z

QueryBuilder.Tests/UpdateTests.cs

-
            var query = new Query("Books")
-                .With("OldBooks", q => q.From("Books").Where("Date", "<", now))
+                .With("OldBooks", q => q.From("Books").Where("Date", "<", "2024-05-24"))


Hardcoded date value '2024-05-24' should be replaced with a more maintainable approach, such as using a DateTime constant or variable, to make the test more readable and easier to maintain.

Copilot · 2025-09-27T03:51:04Z

QueryBuilder.Tests/SelectTests.cs

+            "SELECT * FROM \"Phones\" UNION SELECT * FROM \"Laptops\" WHERE \"Type\" = 'A'")]
+        [InlineData(EngineCodes.MySql, "SELECT * FROM `Phones` UNION SELECT * FROM `Laptops` WHERE `Type` = 'A'")]
+        [InlineData(EngineCodes.Firebird,
+            "SELECT * FROM \"PHONES\" UNION SELECT * FROM \"Laptops\" WHERE \"Type\" = 'A'")] // Is this good?


The comment '// Is this good?' suggests uncertainty about the test case. Either remove the comment if the test case is correct, or investigate and fix the case if there's an issue with the expected SQL output.

Suggested change

"SELECT * FROM \"PHONES\" UNION SELECT * FROM \"Laptops\" WHERE \"Type\" = 'A'")] // Is this good?

"SELECT * FROM \"PHONES\" UNION SELECT * FROM \"LAPTOPS\" WHERE \"TYPE\" = 'A'")]

Copilot · 2025-09-27T03:51:04Z

QueryBuilder.Tests/Infrastructure/TestSupport.cs

+                EngineCodes.SqlServer => new SqlServerCompiler
+                {
+                    UseLegacyPagination = useLegacyPagination ?? true
+                },


[nitpick] The default value for SqlServer's UseLegacyPagination is true while Oracle's is false. This inconsistency could lead to confusion. Consider documenting why different engines have different defaults or making the defaults consistent.

ahmad-moussawi · 2025-09-27T03:52:19Z

@faddiv sorry for the late reply,
I have few comments on the tests adjustments.
Is it easy for you to address the Benchmarks in a separate PR, so we can merge it, then we discuss the tests enhancements?

Added SQL Server service container and updated installation steps for sqlcmd tools.

faddiv · 2025-09-30T16:36:23Z

@faddiv sorry for the late reply, I have few comments on the tests adjustments. Is it easy for you to address the Benchmarks in a separate PR, so we can merge it, then we discuss the tests enhancements?

Hi @ahmad-moussawi.

I will close this and reorganize this into two separate pr.

ahmad-moussawi added 5 commits February 1, 2025 07:45

add ci workflow

0b12f6a

remove Program folder

9936d83

chore: add release pipeline

f6e9516

chore: update release ci to add nuget source

82ea97e

chore: update readme

d20e930

ahmad-moussawi and others added 3 commits September 26, 2025 23:15

Add GitHub Actions workflow for .NET build and test

8e687c2

This workflow automates the build and test process for a .NET project on push and pull request events to the main branch.

run ci on PRs

513e800

reorganize ci.yml

52f81c3

ahmad-moussawi requested a review from Copilot September 27, 2025 03:50

Copilot AI reviewed Sep 27, 2025

View reviewed changes

ahmad-moussawi and others added 7 commits September 27, 2025 12:26

Add MS SQL configuration to CI workflow

c08a66f

Change MSSQL user from 'admin' to 'sa'

32b3948

Replace MS SQL setup with installation commands

7603674

Configure SQL Server service in CI workflow

5e677e7

Added SQL Server service container and updated installation steps for sqlcmd tools.

Update ci.yml

758e9f3

Update ci.yml

b31ead9

use secret password for db

981f444

faddiv force-pushed the faddiv/main branch from b0672d2 to 981f444 Compare September 30, 2025 16:34

faddiv closed this Sep 30, 2025

This was referenced Sep 30, 2025

Benchmarks for SqlKata Compiler #763

Open

Test Improvements #764

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

Test Improvements & Benchmarks for SqlKata Compiler #738

Test Improvements & Benchmarks for SqlKata Compiler #738

Uh oh!

faddiv commented Feb 8, 2025 •

edited

Loading

Uh oh!

ahmad-moussawi commented Feb 12, 2025

Uh oh!

faddiv commented Feb 13, 2025

Uh oh!

Copilot AI left a comment

Uh oh!

Copilot AI Sep 27, 2025

Uh oh!

Copilot AI Sep 27, 2025

Uh oh!

Copilot AI Sep 27, 2025

Uh oh!

ahmad-moussawi commented Sep 27, 2025

Uh oh!

faddiv commented Sep 30, 2025

Uh oh!

Uh oh!

	"SELECT * FROM \"PHONES\" UNION SELECT * FROM \"Laptops\" WHERE \"Type\" = 'A'")] // Is this good?
	"SELECT * FROM \"PHONES\" UNION SELECT * FROM \"LAPTOPS\" WHERE \"TYPE\" = 'A'")]

Uh oh!

Test Improvements & Benchmarks for SqlKata Compiler #738

Test Improvements & Benchmarks for SqlKata Compiler #738

Uh oh!

Conversation

faddiv commented Feb 8, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

How to run the benchmark

Summary of Changes:

Uh oh!

ahmad-moussawi commented Feb 12, 2025

Uh oh!

faddiv commented Feb 13, 2025

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull Request Overview

Reviewed Changes

Uh oh!

Copilot AI Sep 27, 2025

Choose a reason for hiding this comment

Uh oh!

Copilot AI Sep 27, 2025

Choose a reason for hiding this comment

Uh oh!

Copilot AI Sep 27, 2025

Choose a reason for hiding this comment

Uh oh!

ahmad-moussawi commented Sep 27, 2025

Uh oh!

faddiv commented Sep 30, 2025

Uh oh!

Uh oh!

faddiv commented Feb 8, 2025 •

edited

Loading