Index on View - should I use them

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

SQL Server Development (2000)

Index on View - should I use them

Author

Topic

singhmonica
Starting Member

22 Posts

Posted - 2005-05-02 : 20:30:17

I have a view that joins 2 tables. It has 356363 rows now. I am using a heavy GUI tool to create reports. The report needs to pull in all the rows from the view. I cannot restrict the view or the query. I am experiencing extremely slow performance. Would using indexes on the view help. If so, how do I go about creating indexes?
Below is the script for the view-
ALTER view dbo.d_timed_history as
select d_id, column_name, calendar_date, changer, new_value_text
from dbo.d_history a, dbo.calendar_time_dim
where calendar_date > change_dttm - 1
and calendar_date < current_timestamp + 1
and calendar_date < (select isnull(min(b.change_dttm) - 1, current_timestamp)
from dbo.d_history b
where a.d_id = b.d_id
and b.change_dttm > a.change_dttm
and a.column_name = b.column_name)
and column_name = 'd_status'

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-03 : 11:10:40

Maybe the Index Tuning Wizard could give a suggestion. I really dont know if it works on views or not.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-05-03 : 12:07:26

Read up in Book Online about clustered indexes on views. There are loads of things you cant do in a query for a view if you want to put an index on it. Its suppose to give a performance gain. but i've not managed to get it to be any faster.
are you returning 356363 rows? if so, select top 10000 may give you the performance gain you need. then get the rest later if ou need it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-03 : 12:16:54

1) start off by using proper join syntax in your queries -- it makes it easier to read and debug

2) make sure all base tables have proper indexes set up before even considering an index on a view

3) also consider your SELECT statement, make sure it is as efficeint as possible before considering an index on a view

the only benefit of indexing a view is when you have expressions that are not normally available in the base tables. I.e., if you have a formula that concatenates a few columns in your view and it needs to be quickly searchable, or perhaps a date manipulation (like removeing the time component). OTherwise, any queries against a view will automatically be optimized by SQL Server to use any indexes available on the base tables, if the view is written efficiently.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 12:28:50

Well I don't see the relationship...unless it's a theta join...also, just as a mthod of self documenting, I always alias the tables to identify the columns that it's coming from.

If you supply us with the DDL (inclusing the indexes, foreign keys and Primary keys) we could probably help you out better



     SELECT  d_id, column_name, calendar_date, changer, new_value_text
       FROM  d_history a
 INNER JOIN  calendar_time_dim
	 ON ??????????????
      WHERE  calendar_date > change_dttm - 1
	AND  calendar_date < CURRENT_TIMESTAMP + 1
	AND  calendar_date < ( SELECT ISNULL(MIN(b.change_dttm) - 1, CURRENT_TIMESTAMP) 
				 FROM dbo.d_history b
			        WHERE a.d_id = b.d_id 
				  AND b.change_dttm > a.change_dttm
				  AND a.column_name = b.column_name)
	AND  column_name = 'd_status'

Column_name?????

Brett

8-)

singhmonica
Starting Member

22 Posts

Posted - 2005-05-03 : 16:52:49

My problem is extremely slow performance.
Below are the DDLs.

The table in DB is History table- it stores historical information on the different tables. It has recursive relationships. View defect_history is a view of the history table
with only the defect/bug information (where hs_table_name = bug). Now a second view is created from the defect_history_table and Calendar view to extract informaiton
on defect_status only. It is joined to calendar_date view to measure defect_status on a continuous basis, as in the history table a row is added only when the
defect changes status from one to another.

defect_timed_history view-
ALTER view dbo.defect_timed_history as
select defect_id, column_name, calendar_date, changer, new_value_text
from dbo.defect_history a, dbo.calendar_time_dim
where calendar_date > change_dttm - 1
and calendar_date < current_timestamp + 1
and calendar_date < (select isnull(min(b.change_dttm) - 1, current_timestamp)
from dbo.defect_history b
where a.defect_id = b.defect_id
and b.change_dttm > a.change_dttm
and a.column_name = b.column_name)
and column_name = 'defect_status'

defect_history view -
ALTER view defect_history as (
SELECT HS_KEY defect_id
, CASE HS_COLUMN_NAME
when 'BG_BUG_ID' then 'defect_id'
when 'BG_STATUS' then 'defect_status'
when 'BG_RESPONSIBLE' then 'responsible_user_id'
when 'BG_PROJECT' then 'test_project_name'
when 'BG_SUBJECT' then 'project_hierarchy_id'
when 'BG_SUMMARY' then 'defect_summary_txt'
when 'BG_DESCRIPTION' then 'defect_description_txt'
when 'BG_DEV_COMMENTS' then 'defect_comment_txt'
when 'BG_REPRODUCIBLE' then 'defect_reproduceable_ind'
when 'BG_SEVERITY' then 'defect_severity_name'
when 'BG_PRIORITY' then 'defect_priority_name'
when 'BG_DETECTED_BY' then 'detected_by_user_name'
when 'BG_TEST_REFERENCE' then 'test_id'
when 'BG_CYCLE_REFERENCE' then 'test_set_reference_description_txt'
when 'BG_RUN_REFERENCE' then 'run_id'
when 'BG_STEP_REFERENCE' then 'step_id'
when 'BG_DETECTION_DATE' then 'defect_detected_dttm'
when 'BG_DETECTION_VERSION' then 'detection_version_id'
when 'BG_PLANNED_CLOSING_VER' then 'planned_closing_version_id'
when 'BG_ESTIMATED_FIX_TIME' then 'defect_estimated_fix_minute_cnt'
when 'BG_ACTUAL_FIX_TIME' then 'defect_actual_fix_minute_cnt'
when 'BG_CLOSING_DATE' then 'defect_closed_dt'
when 'BG_CLOSING_VERSION' then 'closing_version_id'
when 'BG_TO_MAIL' then 'defect_mail_to_ind'
when 'BG_USER_01' then 'defect_type'
when 'BG_USER_02' then 'defect_test_phase_name'
when 'BG_USER_07' then 'browser_type'
when 'BG_USER_08' then 'operating_system_type_name'
when 'BG_USER_09' then 'environment_name'
when 'BG_USER_10' then 'test_method_name'
when 'BG_USER_11' then 'defect_resolution_name'
when 'BG_USER_12' then 'development_priority_name'
when 'BG_USER_13' then 'development_center_name'
when 'BG_USER_14' then 'service_center_ticket_nbr'
when 'BG_USER_15' then 'test_execution_name'
when 'BG_USER_16' then 'defect_origin_name'
when 'BG_BUG_VER_STAMP' then 'defect_revision_cnt'
when 'BG_VTS' then 'last_modified_dttm'
end as column_name
, HS_CHANGE_DATE + HS_CHANGE_TIME change_dttm
, HS_CHANGER changer
, HS_NEW_VALUE new_value_text
FROM td.HISTORY
where HS_TABLE_NAME = 'BUG'
)

singhmonica
Starting Member

22 Posts

Posted - 2005-05-06 : 17:27:04

Any comments? I posted the DDLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources