I have a table in SQL Server that I have inherited from an old system that is still in production, which is structured according to the code below. I created an SP to query the table, as described in the code below the create table statement. My problem is that sporadically calls from .NET to this SP both through Enterprise Library 4 and through the DataReader object are slow. SP is called through the loop structure in the data layer, which indicates the parameters that are included in the SP in order to populate user objects. It is also important to mention that a slow call will not be made each time the loop structure is passed. This is usually normal for most of the day or more, and then starting the presentation, which makes it extremely difficult to debug.
The table in question contains about 5 million rows. For example, slow calls will last 10 seconds, while fast calls will average between 0 and 10 milliseconds. I checked transaction lock / lock during slow calls, none were found. I created several custom data-level performance counters to control call time. Essentially, when performance is poor, it is really bad for this challenge. But when it's good, it's really good. I was able to recreate the problem on several different development machines, but not on our development servers and the intermediate database, which, of course, have more rigid hardware. Typically, the problem is resolved by restarting the SQL server services, but not always. There are indexes in the table for fields,which I request, but there are more indexes than I would like. However, I hesitate to remove any toys or toys with indexes due to the impact this may have on the legacy system. Has anyone experienced such a problem before, or do you have a recommendation for fixing it?
CREATE TABLE [dbo].[product_performance_quarterly](
[performance_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[product_id] [int] NULL,
[month] [int] NULL,
[year] [int] NULL,
[performance] [decimal](18, 6) NULL,
[gross_or_net] [char](15) NULL,
[vehicle_type] [char](30) NULL,
[quarterly_or_monthly] [char](1) NULL,
[stamp] [datetime] NULL CONSTRAINT [DF_product_performance_quarterly_stamp] DEFAULT (getdate()),
[eA_loaded] [nchar](10) NULL,
[vehicle_type_id] [int] NULL,
[yearmonth] [char](6) NULL,
[gross_or_net_id] [tinyint] NULL,
CONSTRAINT [PK_product_performance_quarterly_4_19_04] PRIMARY KEY CLUSTERED
(
[performance_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[product_performance_quarterly] WITH NOCHECK ADD CONSTRAINT [FK_product_performance_quarterlyProduct_id] FOREIGN KEY([product_id])
REFERENCES [dbo].[products] ([product_id])
GO
ALTER TABLE [dbo].[product_performance_quarterly] CHECK CONSTRAINT [FK_product_performance_quarterlyProduct_id]
CREATE PROCEDURE [eA.Analytics.Calculations].[USP.GetCalculationData]
(
@PRODUCTID INT,
@BEGINYEAR INT,
@BEGINMONTH INT,
@ENDYEAR INT,
@ENDMONTH INT,
@QUARTERLYORMONTHLY VARCHAR(1),
@VEHICLETYPEID INT,
@GROSSORNETID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @STARTDATE VARCHAR(6),
@ENDDATE VARCHAR(6),
@vBEGINMONTH VARCHAR(2),
@vENDMONTH VARCHAR(2)
IF LEN(@BEGINMONTH) = 1
SET @vBEGINMONTH = '0' + CAST(@BEGINMONTH AS VARCHAR(1))
ELSE
SET @vBEGINMONTH = @BEGINMONTH
IF LEN(@ENDMONTH) = 1
SET @vENDMONTH = '0' + CAST(@ENDMONTH AS VARCHAR(1))
ELSE
SET @vENDMONTH = @ENDMONTH
SET @STARTDATE = CAST(@BEGINYEAR AS VARCHAR(4)) + @vBEGINMONTH
SET @ENDDATE = CAST(@ENDYEAR AS VARCHAR(4)) + @vENDMONTH
IF @VEHICLETYPEID = '-1' AND @GROSSORNETID = '-1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID IS NULL OR PPQ.VEHICLE_TYPE_ID = '0' OR PPQ.VEHICLE_TYPE_ID = '')
AND (PPQ.GROSS_OR_NET_ID IS NULL OR PPQ.GROSS_OR_NET_ID = '0' OR PPQ.GROSS_OR_NET_ID = '')
ORDER BY PPQ.YEARMONTH ASC
IF @VEHICLETYPEID <> '-1' AND @GROSSORNETID <> '-1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID = @VEHICLETYPEID )
AND (PPQ.GROSS_OR_NET_ID = @GROSSORNETID)
ORDER BY PPQ.YEARMONTH ASC
IF @VEHICLETYPEID = '-1' AND @GROSSORNETID <> '-1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID IS NULL OR PPQ.VEHICLE_TYPE_ID = '0' OR PPQ.VEHICLE_TYPE_ID = '')
AND (PPQ.GROSS_OR_NET_ID = @GROSSORNETID)
ORDER BY PPQ.YEARMONTH ASC
IF @VEHICLETYPEID <> '-1' AND @GROSSORNETID = '-1'
SELECT
PPQ.YEARMONTH, PPQ.PERFORMANCE
FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
WITH (NOLOCK)
WHERE
(PPQ.PRODUCT_ID = @PRODUCTID)
AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
AND (PPQ.VEHICLE_TYPE_ID = @VEHICLETYPEID)
AND (PPQ.GROSS_OR_NET_ID IS NULL OR PPQ.GROSS_OR_NET_ID = '0' OR PPQ.GROSS_OR_NET_ID = '')
ORDER BY PPQ.YEARMONTH ASC
END