none
performance degradation...

    Question

  • i am using the view as an source:

    view is created by inner join of two tables. first table that has its created script as :

    USE [web_master]
    
    GO
    
    
    
    /****** Object: Table [dbo].[Eligibility] Script Date: 08/31/2009 14:06:03 ******/
    
    SET ANSI_NULLS ON
    
    GO
    
    
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    
    
    
    SET ANSI_PADDING ON
    
    GO
    
    
    
    CREATE TABLE [dbo].[Eligibility](
    
    	[member] [int] NOT NULL,
    
    	[parent] [int] NOT NULL,
    
    	[child] [int] NOT NULL,
    
    	[effective_date] [datetime] NOT NULL,
    
    	[termination_date] [datetime] NOT NULL,
    
    	[bill_indicator] [char](1) NULL,
    
    	[product_description] [varchar](6) NULL,
    
    	[location] [int] NULL,
    
    	[hire_date] [datetime] NULL,
    
    	[incentive_start_date] [datetime] NULL,
    
    	[created_time] [datetime] NOT NULL,
    
    	[created_name] [varchar](25) NOT NULL,
    
    	[modified_time] [datetime] NOT NULL,
    
    	[modified_name] [varchar](25) NOT NULL,
    
    	[Eligibility_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    
    	 CONSTRAINT [PK_Eligibility] PRIMARY KEY NONCLUSTERED 
    
    (
    
    	[Eligibility_id] ASC
    
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
    
    )
    
    
    
    GO
    
    SET ANSI_PADDING OFF
    
    GO
    
    

    second tables ahs same conditions as this with different coulmns.

    Now my query is selecting columns fron the view...

    If other users also use this view then will it degrade the performance of other users.

    Thanks in advance!!!!!!!!

    Punia

    • Edited by punia Tuesday, August 31, 2010 6:31 PM
    Tuesday, August 31, 2010 6:26 PM

All replies

  • Use Indexed view.
    Tuesday, August 31, 2010 6:27 PM
  • What is the structure of your View?

    There are some limitations on using Index Views. You have to be aware of those limitations.

    http://msdn.microsoft.com/en-us/library/aa933148(SQL.80).aspx

     


    Best Wishes, Arbi --- Please vote if you find this posting was helpful.
    Tuesday, August 31, 2010 6:40 PM
  • Hi,

    Just forget about view...

    now table eligibility has row lock and page lock on.... if i am selecting something from this table and other users also select from this table, then how will it degrade performance?

    If i am just selecting * from table directly ,,,, not even using view...

    Tuesday, August 31, 2010 7:42 PM
  • lock will not allow other user to access the table if there are locks on that table and its in use. it'll wait..

     

    what's ur requirement.  you want to explain that in more detail?

    Tuesday, August 31, 2010 7:46 PM
  • i am using this table for selecting... other users also use for selecting... no insertion and updation....

    and suppose if execute my select query first ,,, and then other user exec select query after me... so will it degrade other user performance

    Tuesday, August 31, 2010 7:54 PM
  • how busy is this table?

    do any transactions involve in it?

     

    if its just select, and if its not heavy transaction based, i don't think thats big issue. create proper index, make sure your query doesn't run for long time.

    Tuesday, August 31, 2010 7:56 PM