SET ARITHABORT ON makes ADO.NET query way faster. WHY?

[cmay]

We have this SP that returns about 25,000 rows from a join of 4
tables.

These 4 are all tables, and not views, and none of them have
calculated fields.

From Microsoft SQL Management Studio, putting SET ARITHABORT ON and

SET ARITHABORT OFF execute in the same time: fast, about 2 seconds.

When we try to fill a dataset with this data, it takes 20 seconds if
we have don't include the SET ARITHABORT ON line. When we include the
line, this is about 2 seconds, so 10x faster.

Anyone have any idea why this would be the case? No views, no
calculated fields.

 

[Mary Chipman [MSFT]]

My guess is that setting it to ON incurs more overhead when processing
each row. There's no way the server can know in advance if an overflow
or divide by zero error is going to occur. You might want to ask in
one of the sqlserver newsgroups - someone there might be able to give
you a more authoritative answer.

--Mary