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.
| Author |
Topic |
|
dim
Yak Posting Veteran
57 Posts |
 Posted - 2011-01-24 : 17:58:13
|
| Hi,I have a datbase table which stores data at a detail level ( each day has more than 100 rows ). The table captures data for the time period 2 years worth data with 3018918 rows. I have a view which pulls data from this table. At present it does take some time to retrieve data. Is there a possible way to improve the performance so that the data retireval is faster?Thank you,dimDp |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2011-01-24 : 18:24:57
|
| Hi,I guess I have just one index on the table defined. [PK_Service_Metric] PRIMARY KEY CLUSTERED USE [AFRTY]GO/****** Object: Table [dbo].[Services_Metrics] Script Date: 01/24/2011 18:16:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Services_Metrics]( [metric_key] [int] IDENTITY(1,1) NOT NULL, [date_key] [int] NOT NULL, [Center_Key] [int] NOT NULL, [Driver_Key] [int] NOT NULL, [P_Type_Key] [char](2) NOT NULL, [S_Type_Key] [varchar](2) NOT NULL, [F_Key] [int] NOT NULL, [Res_Key] [int] NOT NULL, [Status_Reason_Key] [int] NOT NULL, [Agent_Key] [int] NULL, [Call_Key] [int] NULL, [transaction_audit_key] [int] NOT NULL, [Battery_Calls] [int] NULL CONSTRAINT [DF_Battery_Cell] DEFAULT ((0)), [Battery_Sales] [int] NULL CONSTRAINT [DF_Battery_Sales] DEFAULT ((0)), [Warranty_Battery_Sales] [int] NULL CONSTRAINT [DF_Warranty_Battery_Sales] DEFAULT ((0)), [RE_OL] [int] NULL CONSTRAINT [DF_RE_OL] DEFAULT ((0)), [RE_OL_Average_Of_Total] [int] NULL CONSTRAINT [DF_RE_OL_AVG_OF_Total] DEFAULT ((0)), [Under_30_Minutes] [int] NULL CONSTRAINT [DF_Under_30_Mins] DEFAULT ((0)), [Number_of_OTA_Calls] [int] NULL DEFAULT ((0)), [Number_of_6LD_Calls] [int] NULL DEFAULT ((0)), [Number_of_GOA_Calls] [int] NULL DEFAULT ((0)), [Number_of_Paid_Calls] [int] NULL CONSTRAINT [DF_Number_Of_Paid_Calls] DEFAULT ((0)), [Number_of_Total_Calls] [int] NULL DEFAULT ((0)), [RA_Paid_Call_Volume] [int] NULL CONSTRAINT [DF_RA_Paid_Call_Volume] DEFAULT ((0)), [Roadside_Assistance_Responce_Time] [int] NULL CONSTRAINT [DF_Roadside_Assitance_Resonce_Time] DEFAULT ((0)), [RA_Quaility_Scores] [int] NULL CONSTRAINT [DF_RA_Quaility_Scores] DEFAULT ((0)), [COR_Car_Counts] [int] NULL CONSTRAINT [DF_COR_Car_Cnts] DEFAULT ((0)), [Battery_Installations] [int] NULL CONSTRAINT [DF_Batter_Installations] DEFAULT ((0)), [Number_of_Glass_Jobs] [int] NULL CONSTRAINT [DF_Number_Of_Glass_Jobs] DEFAULT ((0)), [Extended_Warranty_Sales] [int] NULL CONSTRAINT [DF_Extended_Warranty_Sales] DEFAULT ((0)), [New_Car_Sales] [int] NULL CONSTRAINT [DF_New_Car_Sales] DEFAULT ((0)), [Used_Car_Sales] [int] NULL CONSTRAINT [DF_Used_Car_Sales] DEFAULT ((0)), [CC_Activations] [int] NULL CONSTRAINT [DF_CC_Activations] DEFAULT ((0)), [Number_of_calls_without_cancel] [int] NULL DEFAULT ((0)), [TLC Code Desc] [varchar](20) NULL, [Received time] [datetime] NULL, [On Location time] [datetime] NULL, [Call Cleared time] [datetime] NULL, CONSTRAINT [PK_Service_Metric] PRIMARY KEY CLUSTERED ( [metric_key] ASC, [transaction_audit_key] ASC, [Driver_Key] ASC, [Center_Key] ASC, [date_key] ASC, [P_Type_Key] ASC, [S_Type_Key] ASC, [F_Key] ASC, [Res_Key] ASC, [Status_Reason_Key] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Let me knowDp |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2011-01-24 : 18:33:17
|
| Hi,The view is nothing but a select of the above table. No computations at all. I was concerned whether we can reduce the time by applying appropriate index at the table level to reduce the time.Dp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2011-01-24 : 18:44:12
|
| Hi,This is the query:SELECT dbo.vw2_Dim_Date.Date, dbo.vw2_Dim_Facilities.Facility_Code, dbo.vw2_Dim_Facilities.Facility_Name, dbo.vw2_Dim_Facilities.Facility_Code + ' ' + dbo.vw2_Dim_Facilities.Facility_Name, dbo.vw2_Dim_Fleet_Driver.Fleet_Driver_Last_Name + ' ' + dbo.vw2_Dim_Fleet_Driver.Fleet_Driver_First_Name + ' , ' + dbo.vw2_Dim_Fleet_Driver.Fleet_Driver_Code, dbo.vw2_Dim_Fleet_Driver.Fleet_Driver_Code, dbo.vw2_Dim_Call_Types.Call_Type_Code, dbo.vw2_Dim_Call_Types.Call_Type_Description, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver."TLC Code Desc", dbo.vw2_Dim_Resolutions.Resolution_Code, dbo.vw2_Dim_Resolutions.Resolution_Description, dbo.vw2_Dim_Cost_Centers.Cost_Center_Code, dbo.vw2_Dim_Cost_Centers.Cost_Center_Description, dbo.vw2_Dim_Cost_Centers.Cost_Center_Location, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Recd_Time, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Call_Clear_Time, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.On_Loc_Time, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Difference_OL_Cleared_Time, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Difference_Received_Time_Cleared_Time, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Received_Time_Minutes, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.On_Location_Time_Minutes, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Call_Cleared_Time_Minutes, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Difference_Received_Time_Cleared_Time_Minutes, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Difference_On_Location_Time_Cleared_Time_Minutes, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.RE_OL_Average_Of_Total, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Number_of_Total_Calls, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Under_30_Minutes, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Number_of_OTG_Calls, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Number_of_6L_Calls, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Number_of_GOA_Calls, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Battery_Calls, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Battery_Sales, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Warranty_Battery_Sales, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Number_of_calls_without_cancelFROM dbo.vw2_Dim_Date, dbo.vw2_Dim_Facilities, dbo.vw2_Dim_Fleet_Driver, dbo.vw2_Dim_Call_Types, dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver, dbo.vw2_Dim_Resolutions, dbo.vw2_Dim_Cost_CentersWHERE ( dbo.vw2_Dim_Date.date_key=dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.date_key ) AND ( dbo.vw2_Dim_Fleet_Driver.Fleet_Driver_Key=dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Fleet_Driver_Key ) AND ( dbo.vw2_Dim_Facilities.Facility_Key=dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Facility_Key ) AND ( dbo.vw2_Dim_Resolutions.Resolution_Key=dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Resolution_Key ) AND ( dbo.vw2_Dim_Call_Types.Call_Type_Key=dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Call_Type_Key ) AND ( dbo.vw2_Dim_Cost_Centers.Cost_Center_Key=dbo.vw2_Fact_Automotive_Service_Call_Details_By_Driver.Cost_Center_Key ) AND ( dbo.vw2_Dim_Fleet_Driver.Fleet_Driver_Code <> '?' AND dbo.vw2_Dim_Date.Date >= '01/01/2009' AND dbo.vw2_Dim_Date.Date <= '01/01/2010' )Dp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2011-01-25 : 20:25:04
|
| Hi tkizer....yes I indexed the join condition and it kind of improved the result retrieval. Also would it help to do the same for the underlying view?Dp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
SQL Optimization