Improving SQL Server / SQL Performance
I have a table in SQL Server that looks like this:
CREATE TABLE [dbo].[FCT_RawEvents](
[EquipID] [int] NOT NULL,
[EventTimeStamp] [int] NOT NULL,
[EventMilliSeconds] [smallint] NULL,
[EventID] [int] NOT NULL,
[EventOn] [bit] NOT NULL,
[JobID] [int] NULL,
[FirstEvent] [bit] NULL,
[OperatorId] [int] NULL,
[Suppressed] [bit] NULL,
[ManualOverride] [bit] NULL
)
This contains events that are enabled or disabled (EventOn = True, EventOn = False). Now I need to get all events that are "active" (not suppressed) at a given time. I have a SQL that works, however, since this table has millions of rows, it is quite slow (10 seconds for 5 equipIds).
Here he is:
DECLARE @StartDateTime datetime = '2013/01/01'
DECLARE @csvEquipIds nvarchar(MAX) = '5,6,7,8'
DECLARE @StartTimeStamp int = dbo.GetSecondsFromDate(@StartDateTime)
DECLARE @StartMilliSeconds smallint = DATEPART(Ms, @StartDateTime)
DECLARE @EquipIds TABLE (EquipId int)
INSERT INTO @EquipIds(EquipId) SELECT EquipID FROM dbo.getEquipmentIDs(null,@csvEquipIds)
SELECT dbo.getDateFromSeconds(EventTimeStamp), * FROM
( SELECT re.EquipID,EventTimeStamp,EventMilliSeconds,EventID,eventon,
ROW_NUMBER() OVER (PARTITION BY re.EquipId,EventID ORDER BY EventTimeStamp DESC,EventMilliSeconds DESC) AS RowNo
FROM dbo.FCT_RawEvents re
JOIN @EquipIds eq
ON eq.EquipId = re.EquipID
WHERE (re.EventTimeStamp < @StartTimeStamp OR(re.EventTimeStamp = @StartTimeStamp AND re.EventMilliSeconds <= @StartMilliSeconds)) AND re.EventID > 0
AND (re.Suppressed IS NULL)-- OR re.Suppressed = 0)
) ev
WHERE RowNo = 1 AND EventOn = 1
ORDER BY EquipID,EventID, EventTimeStamp desc, EventMilliSeconds desc
The execution plan shows that 80% of the time is spent on sorting, which is a function of the section / order window.
I am in no way an INDEX expert, but added:
CREATE CLUSTERED INDEX [IX_Clustered] ON [dbo].[FCT_RawEvents]
(
[EquipID] ASC,
[EventTimeStamp] DESC,
[EventMilliSeconds] DESC,
[EventID] ASC,
[EventOn] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_EquipEventTime] ON [dbo].[FCT_RawEvents]
(
[EquipID] ASC,
[EventID] ASC,
[EventTimeStamp] DESC,
[EventMilliSeconds] DESC
)
INCLUDE ( [EventOn]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Suppressed] ON [dbo].[FCT_RawEvents]
(
[Suppressed] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
It looks like the sort is looking through most of the table where I really want it to look backward until it finds the first matching event.
Any pointers would be much appreciated either with Index or SQL enhancement.
source to share
After comments:
-
Try replacing the table variable with a temporary table: table variables have no statistics, there may be temporary tables.
-
The second index looks redundant.
-
Try replacing the scalar function.
-
Examine the selectivity of your columns
EquipID, EventTimeStamp, EventMilliSeconds, EventID, EventOn
and create the index in order of highest selectivity to lowest. Selectivity is a measure of the number of duplicate values โโin a column. It ranges from any duplicates (high selectivity) to all the same values โโ(zero selectivity). Ideally, the index should have columns in the order of highest selectivity.
For example, the selectivity of your column EquipId
would be
(SELECT COUNT(DISTINCT EquipId) FROM dbo.FCT_RawEvents) /
(SELECT COUNT(*) FROM dbo.FCT_RawEvents)
- Make sure your statistics are out of date.
source to share