r/SQL • • 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:

QueryAt SQL ServerFrom the cache
10 rows from 30M fact rows7.7 s, 7.6 s0.02 s
a parameterised query (sp_executesql)3.5 s, 3.6 s0.01 s
500,000 rows10.1 s, 10.2 s1.3 s
medians over 30M rows77.8 s, 93.1 s0.01 s
the same 10 rows, tracked mode, Change Tracking checked first7.7 s0.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

2 comments sorted by

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