My company has a view that we feed some reports through. I was presented with the issue that it was timing out by one of the managers who uses the report. After some investigation, I found it was taking 5 minutes to return 93,000 records from the stored procedure, and 3 minutes to return 68,000 records doing a select statement from the view with 1 where clause added (for year specification).
After running the select with the execution plan turned on, I went from 3 minutes to 16 seconds on the 68,000 records by adding an index that was missing. Is still far too slow for me to be okay with as this shouldn't take more than a second or 2.
Execution plan has 84% cost in my clustered index scan. Found another question here asking about how they can reduce that and tried the solution but it had no effect for me. I am not a DBA but a asp.net developer so I do not know the small details of indexing and optimizing.
The query is not doing a select * as others have suggested not to do as seen below:
SELECT
[Customer_Type]
,[Customer]
,[Unit_Number]
,[Cert_Number]
,[Effective_Date]
,[Expiration_Date]
,[Coverage]
,[CoveragePercentThefts]
,[Amount]
,[Facility_Name]
,[Facility_Policy_Number]
,[Accounting_Period]
,[Entry_Date]
,[Entry_Month]
,[Entry_Year]
,[FTD]
,[Month_FTD]
,[Year_FTD]
,[Entry_By]
,[Net_payment]
,[transaction_code]
,[Facility_Address]
,[Facility_City]
,[Facility_State]
,[Full_Name]
,[CalculatedNet]
,[PctTenantsInsured]
FROM
[Policy_Tracking].[dbo].[vw_Facility_Detail_Report_V4]
WHERE
Year_FTD = '2016'
Would anyone mind helping me get this down to a better number? I appreciate the help.
EDIT:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Facility_Detail_Report_V4]
AS
SELECT
dbo.Customer_Types.Customer_Type,
CASE
WHEN Customer_Types.Customer_Type = 'I'
THEN CUSTOMERS.First_Name + ' ' + CUSTOMERS.Last_Name
WHEN Customer_Types.Customer_Type = 'O'
AND CUSTOMERS.Organization_Name > ''
THEN CUSTOMERS.Organization_Name
ELSE CUSTOMERS.First_Name + ' ' + CUSTOMERS.Last_Name
END AS Customer,
dbo.POLICYS.Unit_Number, dbo.POLICYS.Cert_Number,
dbo.POLICYS.Effective_Date, dbo.POLICYS.Expiration_Date,
dbo.Coverages.Coverage, dbo.CoveragePercentThefts.Description AS CoveragePercentThefts,
dbo.PAYMENTS_Transactions.Amount, dbo.FACILITYS.Facility_Name,
dbo.FACILITYS.Facility_Policy_Number,
dbo.PAYMENTS_Transactions.Accounting_Period,
dbo.PAYMENTS_Transactions.Mod_Date AS Entry_Date,
MONTH(dbo.PAYMENTS_Transactions.Mod_Date) AS Entry_Month,
YEAR(dbo.PAYMENTS_Transactions.Mod_Date) AS Entry_Year,
dbo.PAYMENTS_Transactions.Transaction_Date AS FTD,
MONTH(dbo.PAYMENTS_Transactions.Transaction_Date) AS Month_FTD,
YEAR(dbo.PAYMENTS_Transactions.Transaction_Date) AS Year_FTD,
dbo.PAYMENTS_Transactions.Mod_ID AS Entry_By,
dbo.siteLink_Statement.dcNetPayment AS Net_payment,
dbo.PAYMENTS_Transactions.Payment_Tran_Code_ID AS transaction_code,
dbo.FACILITYS.Address AS Facility_Address,
dbo.FACILITYS.City AS Facility_City, dbo.FACILITYS.State AS Facility_State,
dbo.CUSTOMERS.Full_Name,
dbo.PAYMENTS_Transactions.Amount - dbo.FACILITYS.Commission_Rate * .01 * dbo.PAYMENTS_Transactions.Amount AS CalculatedNet,
dbo.siteLink_Statement.dcPctTenantsInsured AS PctTenantsInsured
FROM
dbo.Customer_Types
RIGHT OUTER JOIN
dbo.CUSTOMERS ON dbo.Customer_Types.ID = dbo.CUSTOMERS.Customer_Type_ID
RIGHT OUTER JOIN
dbo.Policy_Facility_Customer
INNER JOIN
dbo.POLICYS ON dbo.Policy_Facility_Customer.Cert_Number = dbo.POLICYS.Cert_Number
INNER JOIN
dbo.FACILITYS ON dbo.POLICYS.Facility_Policy_Number = dbo.FACILITYS.Facility_Policy_Number
AND dbo.Policy_Facility_Customer.Facility_ID = dbo.FACILITYS.ID
LEFT OUTER JOIN
dbo.Coverages ON dbo.POLICYS.Coverage_ID = dbo.Coverages.ID
LEFT OUTER JOIN
dbo.CoveragePercentThefts ON dbo.POLICYS.Coverage_Percent_Theft_ID = dbo.CoveragePercentThefts.ID
ON dbo.CUSTOMERS.ID = dbo.Policy_Facility_Customer.Customer_ID
RIGHT OUTER JOIN
dbo.PAYMENTS_Transactions ON dbo.POLICYS.Cert_Number = dbo.PAYMENTS_Transactions.Cert_Number
LEFT OUTER JOIN
dbo.siteLink_Statement ON dbo.FACILITYS.Facility_Policy_Number = dbo.siteLink_Statement.SSFNumber
AND MONTH(dbo.PAYMENTS_Transactions.Accounting_Period) = MONTH(DATEADD(m, - 1, dbo.siteLink_Statement.zCreationDate))
AND YEAR(dbo.PAYMENTS_Transactions.Accounting_Period) = YEAR(DATEADD(m, - 1, dbo.siteLink_Statement.zCreationDate))
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1[34] 4[49] 3) )"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1[50] 2[25] 3) )"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1[56] 3) )"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[15] 4[76] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1[28] 4) )"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = -100
Left = 0
End
Begin Tables =
Begin Table = "Customer_Types"
Begin Extent =
Top = 20
Left = 680
Bottom = 128
Right = 836
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "CUSTOMERS"
Begin Extent =
Top = 20
Left = 446
Bottom = 128
Right = 620
End
DisplayFlags = 280
TopColumn = 6
End
Begin Table = "Policy_Facility_Customer"
Begin Extent =
Top = 22
Left = 40
Bottom = 130
Right = 191
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "POLICYS"
Begin Extent =
Top = 38
Left = 387
Bottom = 146
Right = 606
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "FACILITYS"
Begin Extent =
Top = 188
Left = 164
Bottom = 296
Right = 353
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "Coverages"
Begin Extent =
Top = 155
Left = 639
Bottom = 263
Right = 790
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "CoveragePercentThefts"
Begin Extent =
Top = 330
Left = 38
Bottom = 438
Right = 240
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Facility_Detail_Report_V4'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "PAYMENTS_Transactions"
Begin Extent =
Top = 432
Left = 484
Bottom = 540
Right = 682
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "siteLink_Statement"
Begin Extent =
Top = 240
Left = 1076
Bottom = 369
Right = 1276
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 20
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 19890
Alias = 1410
Table = 1500
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Facility_Detail_Report_V4'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Facility_Detail_Report_V4'
GO
