Sql-server – Slow query and Insert with trigger

performancesql serversql-server-2008-r2trigger

I have vehicle tracking system application based on PHP, SQL Server 2008 R2 Enterprise Edition with XEON HP Z800 Server. My database is growing day by day in millions and it makes it very slow to select and insert, I have one main table name TraceData that contain all records for each signal from devices every second more records coming in this table, but when we trying to generate History or report for any vehicle it got stuck and take very long some times more then 5 mins the structure of my table is mentioned below: 

CREATE TABLE [dbo].[TraceData](
    [AutoID] [bigint] IDENTITY(1,1) NOT NULL,
    [IMEI] [varchar](20) NOT NULL,
    [Alram] [varchar](20) NULL,
    [GPRMCState] [varchar](10) NULL,
    [Lat] [decimal](18, 6) NULL,
    [NS] [varchar](10) NULL,
    [Lng] [decimal](18, 6) NULL,
    [WE] [varchar](10) NULL,
    [Speed] [decimal](18, 4) NULL,
    [Direction] [decimal](18, 1) NULL,
    [PDOP] [decimal](18, 1) NULL,
    [HDOP] [decimal](18, 1) NULL,
    [VDOP] [decimal](18, 1) NULL,
    [RTC] [datetime] NULL,
    [VCS] [varchar](10) NULL,
    [VBV] [decimal](18, 2) NULL,
    [VCV] [decimal](18, 2) NULL,
    [ADA] [decimal](18, 2) NULL,
    [ADB] [decimal](18, 2) NULL,
    [LACCIL] [varchar](10) NULL,
    [LACCIC] [varchar](10) NULL,
    [Temperature] [decimal](18, 1) NULL,
    [Mileage] [decimal](18, 4) NULL,
    [ModelType] [nvarchar](50) NULL,
    [Serial] [varchar](10) NULL,
    [TrackerTime] [datetime] NULL,
    [ServerTime] [datetime] NULL,
    [Address] [nvarchar](2000) NULL,
    [IO1] [varchar](10) NULL,
    [IO2] [varchar](10) NULL,
    [IO3] [varchar](10) NULL,
    [IO4] [varchar](10) NULL,
    [IO5] [varchar](10) NULL,
    [IO6] [varchar](10) NULL,
    [IO7] [varchar](10) NULL,
    [IO8] [varchar](10) NULL,
    [IO9] [varchar](10) NULL,
    [IO10] [varchar](10) NULL,
    [IO11] [varchar](10) NULL,
    [IO12] [varchar](10) NULL,
    [Ignition] [int] NULL,
    [Multiple] [int] NULL,
    [First] [int] NULL,
    [MultipleSession] [bigint] NULL,
 CONSTRAINT [PK_TraceData] PRIMARY KEY CLUSTERED 
(
    [AutoID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TraceData] ADD  CONSTRAINT [DF_TraceData_Engine]  DEFAULT ((0)) FOR [Ignition]
GO

ALTER TABLE [dbo].[TraceData] ADD  CONSTRAINT [DF_TraceData_Multiple]  DEFAULT ((0)) FOR [Multiple]
GO

ALTER TABLE [dbo].[TraceData] ADD  CONSTRAINT [DF_TraceData_First]  DEFAULT ((0)) FOR [First]
GO

ALTER TABLE [dbo].[TraceData] ADD  CONSTRAINT [DF_TraceData_MultipleSession]  DEFAULT ((0)) FOR [MultipleSession]
GO

I have one trigger on it also that's 

ALTER TRIGGER [dbo].[update_trackers_table] 
   ON  [dbo].[TraceData] 
   AFTER INSERT
AS 

if EXISTS  (SELECT * FROM inserted WHERE IO5 = '1' OR (IO3 ='1' AND ModelType = 'GT08' ))

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

-- Set Replication 
 insert into  TraceDataApp (IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1,
 IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition) SELECT IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1,
 IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition FROM inserted;
-- End Set Replication 

UPDATE trackers 
SET LastAlarm = inserted.Alram,
LastLangtitute=inserted.Lng,
LastLatitute=inserted.Lat,
LastTime =DATEADD(HOUR,4,inserted.TrackerTime),
LastSpeed= (inserted.Speed * 1.852),
TMileage= (trackers.TMileage + inserted.Mileage),
LastDirection=inserted.Direction ,
IGN='On',
LastTraceID=inserted.AutoID 
 FROM trackers,Inserted 
WHERE trackers.IMEI = inserted.IMEI;

END
else
BEGIN 

-- Set Replication 
 insert into  TraceDataApp (IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1,
 IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition) SELECT IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,IO1,
 IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition FROM inserted;
-- End Set Replication 

 --Set Replication 
--insert into  TraceDataApp (AutoID,IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,Address,IO1,
-- IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition) SELECT AutoID,IMEI,Alram,GPRMCState,Lat,NS,Lng,WE,Speed,Direction,Mileage,ModelType,Serial,TrackerTime,ServerTime,Address,IO1,
-- IO2,IO3,IO4,IO5,IO6,IO7,IO8,IO9,IO10,IO11,IO12,Ignition FROM inserted;
-- End Set Replication 

UPDATE trackers 
SET LastAlarm = inserted.Alram,
LastLangtitute=inserted.Lng,
LastLatitute=inserted.Lat,
LastTime =DATEADD(HOUR,4,inserted.TrackerTime),
LastSpeed= (inserted.Speed * 1.852),
TMileage= (trackers.TMileage + inserted.Mileage),
LastDirection=inserted.Direction ,
IGN='Off',
LastTraceID=inserted.AutoID 
 FROM trackers,Inserted 
WHERE trackers.IMEI = inserted.IMEI;

END

any help will be really appreciated.

Best Answer

The trigger looks ok in principle IF you have an index with IMEI as the first index column on the trackers table and if you don't have lots of records with the same IMEI in the trackers table.

However, unless you have additional indexes on the TraceData table that you didn't show us, any search of course is going to be slow, since it always has to scan all rows in the table. If you need to search for certain column values, like a specific vehicle, you have to add a nonclustered index for that column to speed up the search.

Since you are running enterprise edition you might also consider to partition the table and any indexes based on the ID values or an (additional) date column. I assume that old data gets deleted some time and this will be a very lengthy operation if you don't have this data in a partition. It will also reduce the number of index levels and thus speed up search operations. Read this for example.

Also, you may want to place your data on other filegroups than "PRIMARY". Additional Filegroups (and files) can offer performance benefits, can be set to READONLY to host older data (that then doesn't take up valuable backup resources any more) and enable you to do cool things like online restore with enterprise edition. Check out this cool video from Kimberly L. Tripp.

If you already have an index on trackers.IMEI and insert is still slow, consider using Service Broker in the trigger instead of updating the dependent tables directly. Service Broker takes a message with the necessary content to do these updates/inserts later and then immediately gives control back to the caller, processing the message later in a different thread, taking advantage of your multiple cores (which a simple insert cannot do) and thus speeding up the trigger.

Related Question