Help on INDEXED VIEWS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help on INDEXED VIEWS

Author

Topic

vramanhyd
Starting Member

2 Posts

Posted - 2005-08-22 : 07:52:09

Hi
The database is in SQL SERVER 2000 EE.
My objective is to index the views in database. But the views have got OuterJoin, Case which restricts the index use on those views.

I wanted to know if LeftJoin replacing outerjoin in a view allows indexing OR
how can i eliminate the use of outerjoin eventhough i need all the rows from the table?
And What can replace the usage of CASE in views?

If someone could provide a brief note on this, it will be helpful.

Kind Regards
Vraman

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-08-22 : 10:42:27

It would help to show us the code for the view you want to index, and the DDL of the underlying tables.

I have found indexed views are often misunderstood and rarely needed. Remember that a View will still make use of any existing indexes on the underlying tables -- often, just having proper indexes on the table, a properly normalized database, and well written SQL in your view is all you need to keep it efficient.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-22 : 10:50:58

Hi Vraman,

I'm not crazy about indexing views. There are way too many restrictions on what's allowed for me (including outer joins). By the way, a LEFT JOIN is an OUTER JOIN. It's a LEFT OUTER JOIN. So for what you've said I don't think indexing the view will work for you.

Usually, the indexes that are on the underlying tables are sufficient for performance. When that's not the case, query optimization or rethinking the indexes on the underlying tables is usually the answer rather than indexing the view.

Feel free to post the DDL of the underlying tables as well as the code for your query. We'll have a look to see if we can boost performance for you.

EDIT:

by the good doctor...

Be One with the Optimizer
TG

vramanhyd
Starting Member

2 Posts

Posted - 2005-08-25 : 06:52:18

Hi both
This is the view,On which i need some Indexes since lot of Dead locks happening although lot of performance tuning has been done..

SELECT dbo.ENQUIRY.Enquiry_Ref, dbo.ENQUIRY.Batch_Id, dbo.ORGANISATION.organisation_id, dbo.BRANCH.branch_id, dbo.ENQUIRY.Individual_id,
dbo.STATUS_MST.Description_Text AS Status, dbo.MANUAL_ACTION.Action_Name AS Manual_Action,
dbo.PAYMENT_TYPES.Payment_Type_Name AS Payment_Type, dbo.CONTACT_TYPES.Description_text AS Contact_type,
dbo.PRODUCT_MST.Product_Name AS EnquiryType, dbo.SUB_PRODUCTS.Sub_Product_Name AS EnquirySubType,
dbo.SUB_PRODUCTS.Priority , dbo.ENQUIRY.Site_Size,
dbo.ENQUIRY.Boundary_Ref, dbo.ENQUIRY.In_Office_DT, dbo.ENQUIRY.In_Thames_DT, dbo.ENQUIRY.DT_In_Letter, dbo.ENQUIRY.Cancellation_DT,
dbo.ENQUIRY.Customer_Reference, dbo.ENQUIRY.Local_Authority, dbo.ENQUIRY.Water_Company, dbo.ENQUIRY.Sewage_Company,
dbo.ENQUIRY.Eastings, dbo.ENQUIRY.Northings, dbo.ELS_SCANNED_FILE(dbo.ENQUIRY.Enquiry_Ref) AS Scanned_File,
dbo.ELS_MAPSHEET(dbo.ENQUIRY.Enquiry_Ref) AS Mapsheet, dbo.ENQUIRY.Edit_Lock_User, dbo.ENQUIRY.Edit_Lock_DT,
dbo.ENQUIRY.Edit_Lock_Flag, dbo.ENQUIRY.Created_DT, dbo.ENQUIRY.Created_By, dbo.ENQUIRY.Updated_By, dbo.ENQUIRY.UPDATED_DT, dbo.ENQUIRY.Closed_DT,
dbo.ENQUIRY.Site_Development,dbo.RESPONSE_TYPE_MST.Response_Type AS Response_Type_id,
case
when dbo.ENQUIRY.Batch_Mode_Flag = 'UPSE' then cast(1 as bit)
else cast(0 as bit)
end as Upsell_Flag,
dbo.ELS_HAS_MAPGUIDE_EXCEPTION(ENQUIRY.Enquiry_Ref) as HAS_MAPGUIDE_EXCEPTION,
dbo.ELS_HAS_ELS_EXCEPTION(ENQUIRY.Enquiry_Ref) as HAS_ELS_EXCEPTION,
dbo.ENQUIRY.Batch_Mode_Flag,
dbo.ENQUIRY.Service_Req_Id,
dbo.ENQUIRY.NLIS_Originator_Id

FROM dbo.ENQUIRY INNER JOIN
dbo.PRODUCT_MST ON dbo.ENQUIRY.Product_Id = dbo.PRODUCT_MST.Product_Id INNER JOIN
dbo.SUB_PRODUCTS ON dbo.ENQUIRY.Sub_Product_id = dbo.SUB_PRODUCTS.Sub_Product_id INNER JOIN
dbo.INDIVIDUALS ON dbo.ENQUIRY.Individual_id = dbo.INDIVIDUALS.Individual_id INNER JOIN
dbo.BRANCH ON dbo.INDIVIDUALS.branch_id = dbo.BRANCH.branch_id INNER JOIN
dbo.ORGANISATION ON dbo.BRANCH.organisation_id = dbo.ORGANISATION.organisation_id INNER JOIN
dbo.CONTACT_TYPES ON dbo.ENQUIRY.Contact_type_id = dbo.CONTACT_TYPES.Contact_type_id INNER JOIN
dbo.STATUS_MST ON dbo.ENQUIRY.Status_Code = dbo.STATUS_MST.Status_Code INNER JOIN
dbo.PAYMENT_TYPES ON dbo.ENQUIRY.Payment_type_id = dbo.PAYMENT_TYPES.Payment_type_id INNER JOIN
dbo.RESPONSE_TYPE_MST ON dbo.ENQUIRY.Response_Type_id = dbo.RESPONSE_TYPE_MST.Response_Type_id LEFT JOIN
--LEFT OUTER JOIN
dbo.MANUAL_ACTION ON dbo.ENQUIRY.Manual_Action_Id = dbo.MANUAL_ACTION.Manual_Action_Id
WHERE (dbo.ENQUIRY.Batch_Mode_Flag IN ('SE', 'MULC', 'MULSC', 'UPSE','UPSEC'))

CREATE TABLE [ENQUIRY] (
[Enquiry_Ref] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Individual_id] [int] NULL ,
[Status_Code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Manual_Action_Id] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payment_type_id] [int] NOT NULL ,
[Contact_type_id] [int] NULL ,
[Product_Id] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sub_Product_id] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Size] [int] NULL ,
[Site_Area] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payment_Mode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Net_Amount] [smallmoney] NULL ,
[VAT_Amount] [smallmoney] NULL ,
[Total_Amount] [smallmoney] NULL ,
[Pre_Paid_Amount] [smallmoney] NULL ,
[Balance_Amount] [smallmoney] NULL ,
[Batch_Id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Boundary_Ref] [int] NULL ,
[Eastings] [int] NULL ,
[Northings] [int] NULL ,
[In_Thames_DT] [smalldatetime] NOT NULL ,
[In_Office_DT] [smalldatetime] NOT NULL ,
[DT_In_Letter] [smalldatetime] NULL ,
[Cancellation_DT] [smalldatetime] NULL ,
[Customer_Reference] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Billing_Branch_Id] [int] NULL ,
[Send_To_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Flat_No] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_House_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Road] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Town] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_County] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Locality] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Telephone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Out_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_In_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_DX_No] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_DX_Exchange] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Local_Authority] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Water_Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sewage_Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Batch_Mode_Flag] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Edit_Lock_Flag] [bit] NOT NULL CONSTRAINT [DF_ENQUIRY_Edit_Lock_Flag] DEFAULT (0),
[Edit_Lock_User] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Edit_Lock_DT] [smalldatetime] NULL ,
[Response_Doc_Ref] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Plot_Of_Land] [bit] NOT NULL CONSTRAINT [DF_ENQUIRY_Plot_Of_Land] DEFAULT (0),
[DME_Input] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DME_Audit_Log] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Template_Ref] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Development] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Plot] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Property_Status] [bit] NULL ,
[Print_scale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created_By] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Created_DT] [smalldatetime] NOT NULL ,
[Updated_By] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPDATED_DT] [smalldatetime] NOT NULL ,
[Closed_DT] [smalldatetime] NULL ,
[Response_Type_id] [int] NULL ,
[Enquiry_Id] [int] IDENTITY (1, 1) NOT NULL ,
[Billing_Indv_id] [int] NULL ,
[Send_To_Building_No] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_AssocRd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Land_Enquiry] [bit] NULL ,
[cisPropertyNo] [int] NULL ,
[Send_To_EMail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Comments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_IndvID] [int] NULL ,
[Invoice_Job_Owner] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Invoice_Generated] [bit] NULL CONSTRAINT [DF__enquiry__Invoice__21A1C21B] DEFAULT (0),
[NLIS_Originator_Id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PO_Number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Composite_Payment] [bit] NULL CONSTRAINT [DF__enquiry__Composi__2295E654] DEFAULT (0),
[Service_Req_Id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Ind_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Ind_Telephone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Send_To_Ind_Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ENQUIRY_MASTER_PK] PRIMARY KEY NONCLUSTERED
(
[Enquiry_Ref]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_ENQUIRY] CHECK ([Batch_Mode_flag] = 'SE' or [Batch_Mode_flag] = 'MULP' or [Batch_Mode_flag] = 'MULC' or [Batch_Mode_flag] = 'MULSP' or [Batch_Mode_flag] = 'MULSC' or [Batch_Mode_flag] = 'UPSE' or [Batch_Mode_flag] = 'UPSEP' or [Batch_Mode_flag] = 'UPSEC')
) ON [PRIMARY]
GO

CREATE TABLE [MANUAL_ACTION] (
[Manual_Action_Id] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Action_Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [bit] NOT NULL CONSTRAINT [DF_MANUAL_ACTION_Status] DEFAULT (1),
[Created_By] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Created_DT] [smalldatetime] NOT NULL ,
[Updated_By] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Updated_DT] [smalldatetime] NOT NULL ,
CONSTRAINT [MANUAL_ACTION_PK] PRIMARY KEY CLUSTERED
(
[Manual_Action_Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

In the above view, there is CASE,OUTERJOIN.
I need to erase them and replace with alternate code.
Thanks for taking the effort.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources