0

I've got a view which is working very fast in SSMS (MS Sql Server) but working really slow in my web app. My ARITHABORT setting in the database is set to ON but I'm unsure whether this continues through to the web app.

Is there any way to set this in the view itself? I know there are other possible issues in the speed discrepancy between the SSMS and the web app but just want to give this a go (as many other people with the same issue say this resolves it).

By the way, I'm not a database admin and don't really have access rights. All I have is this view and the code for the bit of the web app I'm working on.

user25730
  • 517
  • 2
  • 6
  • 24

1 Answers1

0

Yeah definately mate.

Here is an example of one of the view from our data warehouse.

You can set anything you want in the top section 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[vw_Dim_Date]
as

-- select * from dbo.vw_Dim_Date

SELECT [Date_key]
, Format([Full_date],'MMMMM yyyy')as Date_label1
      ,[Full_date]
      ,[Calendar_year]
      ,[Calendar_quarter]
      ,[Calendar_month]
      ,[Year_of_contract]]
  FROM [dbo].[Dim_Date]
GO

So in your Case you could just use 

SET ARITHABORT ON 

Create View [dbo].[view_Name]
 as .....

Just set it when you create the view so just do a Drop and Create To And add that line in and you will be good to go :D

enter image description here

Dheebs
  • 398
  • 1
  • 6
  • 19
  • `ANSI_NULLS` and `QUOTED_IDENTIFIER` are two settings that are specifically captured at the point of object creation. I don't believe that any other settings are given the same treatment. This is surprisingly poorly documented. I can only find a hint in the [`QUOTED_IDENTIFIER` documentation](https://msdn.microsoft.com/en-gb/library/ms174393.aspx) where it discusses them in the context of stored procedures, but I believe it's equally applicable to views and functions. – Damien_The_Unbeliever Sep 21 '16 at 06:34
  • You do get a further hint to this, though, if you view [sys.sql_modules](https://msdn.microsoft.com/en-us/library/ms175081.aspx) where you'll observe that there are two specific columns related to `ANSI_NULLS` and `QUOTED_IDENTIFIER` and no other `SET` options. – Damien_The_Unbeliever Sep 21 '16 at 06:37
  • interesting then, i might have to ask our DBA's on the account because it was an option for me in ssms. They might have tweaked some setting somewhere. They have done some pretty strange things – Dheebs Sep 21 '16 at 10:11
  • Interesting, but unsure how viable. The thing is, the view already exists in the database. We're not creating a new view each time. I could recreate it using the method above, so long as the ARITHABORT which I set will still be set whenever it is called by the web app. – user25730 Sep 21 '16 at 23:54
  • Will give this a go shortly. – user25730 Sep 22 '16 at 00:06
  • Given this a go, but unfortunately it still runs just as slow on the web app. – user25730 Sep 22 '16 at 01:49