Hacker News Reader

Comment by m-gasser | Hacker News Reader

> Did you know there is a setting in MS SQL Server that impacts performance by an order of magnitude when sending/receiving data from the Cloud to your on-premises servers? It's the default in the ORM generated settings.

Sounds interesting, which setting is that?

View on HN

Multiple Active Result Sets (MARS). During large query responses or bulk loads, "full" packets cause an additional packet to be sent over the wire with about five bytes to hold the MARS "wrapper". The net result is one full packet, and one empty packet on the wire, alternating. The performance impact in LAN latency is negligible. However on higher latency between AWS and your premises it has a terrible performance impact.

MARS isn't strictly needed for most things. Some features that requires it are ORM (EF) proxies and lazy loading. If you need MARS, there are third party "accelerators" that workaround this madness.

"MARS Acceleration significantly improves the performance of connections that use the Multiple Active Result Sets (MARS) connection option."

https://documentation.nitrosphere.com/resources/release-note...

Is that not a client connection flag? MARS does not require a setting change on the server?

I think you may have misinterpreted what he said. I can see why it seems to imply a server setting but that isn't the case

> Did you know there is a setting in MS SQL Server that impacts performance by an order of magnitude when sending/receiving data from the Cloud to your on-premises servers? It's the default in the ORM generated settings

You are right. For some reason when I initially sped through the post I read it as if RDS was doing something wrong.

It is a setting in connection string in the client app.config, "MultipleActiveResultSets=true;".

"Multiple Active Result Sets=true;"

Yeah, honestly most lazy loading and EF proxy use I have seen is more aptly named lazy coding instead. There are times when you might be running 3-4 queries to project some combination of them and want to do that in parallel, but in general if you have lazy loading enabled in EF you are holding up a sign that says “inconsistent reads happening in here”.

I use and love EF, but generally leave MARS off when possible because it is responsible for more trouble than performance gains nearly every time.

Would love to know as well.

Reply on news.ycombinator.com