Why is my stored proc slow in .Net?

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.

Oh.

So how do I turn that behaviour off? Well, I took a look through the .Net reference source and found that, basically, thanks to line 684 of SqlProvider.cs, you can’t.

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.

This entry was posted in Development and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.