I was writing a stored procedure the other day and trying to make it run faster. The problem was that it ran in under a second in SQL Server Management Studio, but then took over thirty seconds when running from .Net. What on earth was going on?
After checking with Profiler, I found something quite significant.
It turns out that .Net’s SQL Server client does this first of all:
SET ARITHABORT OFF
Which doesn’t sound particularly heinous, does it? I mean, just turning off the flag so it won’t bail out when it hits a divide by zero, that can’t be such a bad thing, can it?
You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.
So there’s two choices.
One: put “SET ARITHABORT OFF;” at the beginning of every SQL query. Which means using stored procedures gets harder, you can’t use parameters… it’s a nightmare.
or, two: set it to ON in SQL Server Management Studio and optimise your queries accordingly.
I didn’t fancy the idea of ending up in the SQL injection hall of fame. SET ARITHABORT OFF it is then. *sigh*.
The post “Why is my stored proc slow in .Net?” first appeared on esotechnica.co.uk and is Copyright © C3 Enterprises Ltd 2018. All rights reserved.