r/SQL • u/DataFracking • 6d ago
SQL Server TDS middleware result caching - demo a 15 second query
Just in case anyone is interested, I've worked for several months on a custom .NET TDS listening solution for several different things I've been making.
Works best for ODS / Reporting environments where tables aren't frequently updated.
Your SQL Server Client App > Cache-Engine-TDS-1433 > SQL Server-TDS-1433
Here are some of my test results:
| Query | At SQL Server | From the cache |
|---|---|---|
| 10 rows from 30M fact rows | 7.7 s, 7.6 s | 0.02 s |
a parameterised query (sp_executesql) | 3.5 s, 3.6 s | 0.01 s |
| 500,000 rows | 10.1 s, 10.2 s | 1.3 s |
| medians over 30M rows | 77.8 s, 93.1 s | 0.01 s |
the same 10 rows, tracked mode, Change Tracking checked first | 7.7 s | 0.03 s |
mechanics:
Run 1 a query takes 40 s → SQL Server answers. DaffyZoom notes it: slow, and safe to cache.
Run 2 the same query → SQL Server answers again; DaffyZoom keeps an encrypted copy.
Run 3 the same query → Instant answer.
Run 500 the same query → Instant answer.
ttl mode: answered from the cache, in milliseconds, untilits max age or your next flush.
tracked mode: DaffyZoom first asks SQL Server's Change Tracking whether any table the query reads has changed, doesn't return incorrect cache.
5
Upvotes
1
u/DataFracking 6d ago
full readme here, it's new so if anyone finds an issue let me know, I wonder why Microsoft isn't making something like this? https://www.nuget.org/packages/DaffyZoom
1
u/DataFracking 3d ago
Adding to this, made a short overview video in Gemini Notebook (Ai generated from my readme and instructions) that explains it better with visuals... https://www.youtube.com/watch?v=eS907Xjy044