Re: SQLOLEDB vs Query Analyzer performance (not parameter sniffing)

Mike Jansen (mjansen_nntp@xxxxxxxx) writes:
I'm running into an issue that I've seen in production and also reproduced
in a test environment:

1. Call to stored procedure from application via ADO takes a long time
2. Immediately calling the same stored procedure WITH SAME PARAMETERS from
Query Analyzer returns the results almost immediately.

Here is a quick test to run:

Before you run the query in QA, run this command:

SET ARITHABORT OFF

If my prediction is correct, you will now get a slow plan in QA as well.

Next is to try

DBCC FREEPROCCACHE

and with unchanged settings run from QA first and SQLOLEDB next.

QA runs with ARITHABORT ON by default, SQLOLEDB does not. ARITHABORT is
one these settings that affect the query plan. That is, you get one plan
with ARITHABORT ON, and one with it OFF. (For more details on this, see
http://www.karaszi.com/SQLServer/info_sp_recompile_set.asp.

The most likely reason is that you have an indexed view or an
indexed computed column in the game. For these to come in play,
ARITHABORT must be ON.

The way to address it, is to send SET ARITHABORT ON from the client
when you connect.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Stored Procedure is running slow!
    ... > I run stored procedure from QA, it takes some 15 seconds to execute. ... > seconds when called from MS Fox Pro, via ADO. ... Try this in Query Analyzer: ... By default Query Analyzer runs with ARITHABORT ON, ...
    (microsoft.public.sqlserver.programming)
  • ARITHABORT problem
    ... I'm running D7 and MS SQL 2000. ... I have a calculated indexed column that is ... that inserts new rows into the table, I get an ARITHABORT error. ... the same stored procedure from the MS SQL QA it works fine. ...
    (borland.public.delphi.database.ado)