none
Can someone explan this trigger code ? RRS feed

  • Question

  • Hi guys, I have a trigger problem, it will make sql server cpu or io very height,  then slow my server.

    But I can not understand there code, can some one explan for me ?  thanks very much

    I want rewrite these code using the easyest way !!

        MERGE INTO frontend.dbo.veh_trip Target
                                USING ( SELECT a.nostop_course, c.vrm_id, d.driver_id, a.pos_time, a.once_acc_on_time * 10000, a.command_id, a.alarm_type, a.device_status_3
        FROM ( SELECT min(i.id) as rn
            FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
                FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, min(pos_time) AS pos_time
                    FROM inserted
                    WHERE nostop_course IS NOT NULL
                    GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i
            GROUP BY vehicle_id, nostop_course ) b Left join inserted a ON a.id = b.rn LEFT JOIN frontend.dbo.vehicle c ON a.vehicle_id = c.yuwei_vehicle_id LEFT JOIN frontend.dbo.driver d ON a.driver_id = d.yuwei_driver_id LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id = c.vrm_id
        WHERE c.vrm_id IS NOT NULL AND e.status = 'A' ) AS Source (tripID, vrmID, driverID, postime, distance, command_id, alarm_type, accStatus)
                                ON (Target.vrm_id = Source.vrmID AND Target.yuwei_trip_id = Source.tripID)
                                WHEN MATCHED AND Target.end_time<=Source.postime AND Target.tripEnd IS NULL
                                    THEN UPDATE SET Target.driver_id = Source.driverID, Target.end_time = Source.postime, Target.drv_distance = Source.distance, Target.drv_duration = DATEDIFF(second,Target.start_time,CONVERT(datetime2(0),Source.postime))*1000Target.tripEnd = CASE WHEN (Source.accStatus & 8 <= 0THEN 'Y' ELSE NULL ENDTarget.update_ts = getdate(), Target.update_user = 'System'Target.version = Target.version+1
                                WHEN NOT MATCHED BY TARGET AND Source.accStatus & 8 > 0 THEN
                                    INSERT (yuwei_trip_id,vrm_id,driver_id,acc_date,start_time,end_time,drv_distance)
                                    VALUES (Source.tripID, Source.vrmID, Source.driverID, Source.postime, Source.postime, Source.postime, Source.distance);
        MERGE INTO frontend.dbo.veh_trip Target
                                USING ( SELECT a.nostop_course, c.vrm_id, d.driver_id, a.pos_time, a.once_acc_on_time * 10000, a.command_id, a.alarm_type, a.device_status_3
        FROM ( SELECT max(i.id) as rn
            FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
                FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time
                    FROM inserted
                    WHERE nostop_course IS NOT NULL
                    GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i
            GROUP BY vehicle_id, nostop_course ) b Left join inserted a ON a.id = b.rn LEFT JOIN frontend.dbo.vehicle c ON a.vehicle_id = c.yuwei_vehicle_id LEFT JOIN frontend.dbo.driver d ON a.driver_id = d.yuwei_driver_id LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id = c.vrm_id
        WHERE c.vrm_id IS NOT NULL AND e.status = 'A' ) AS Source (tripID, vrmID, driverID, postime, distance, command_id, alarm_type, accStatus)
                                ON (Target.vrm_id = Source.vrmID AND Target.yuwei_trip_id = Source.tripID)
                                WHEN MATCHED AND Target.end_time<=Source.postime AND Target.tripEnd IS NULL
                                    THEN UPDATE SET Target.driver_id = Source.driverID, Target.end_time = Source.postime, Target.drv_distance = Source.distance, Target.drv_duration = DATEDIFF(second,Target.start_time,CONVERT(datetime2(0),Source.postime))*1000Target.tripEnd = CASE WHEN (Source.accStatus & 8 <= 0THEN 'Y' ELSE NULL ENDTarget.update_ts = getdate(), Target.update_user = 'System'Target.version = Target.version+1
                                WHEN NOT MATCHED BY TARGET AND Source.accStatus & 8 > 0 THEN
                                    INSERT (yuwei_trip_id,vrm_id,driver_id,acc_date,start_time,end_time,drv_distance)
                                    VALUES (Source.tripID, Source.vrmID, Source.driverID, Source.postime, Source.postime, Source.postime, Source.distance);
        UPDATE frontend.dbo.mob_device SET lat = a.pos_latitude, lng = a.pos_longitude, last_loc_update_ts = a.pos_time, version = c.version+1 FROM ( SELECT max(i.id) as rn
            FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
                FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time
                    FROM inserted
                    WHERE nostop_course IS NOT NULL
                    GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i
            GROUP BY vehicle_id, nostop_course ) e Left JOIN inserted a ON a.id = e.rn LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id LEFT JOIN frontend.dbo.mob_device c ON b.vrm_id = c.vrm_id WHERE (c.last_loc_update_ts IS NULL OR c.last_loc_update_ts <= a.pos_time) AND b.vrm_id IS NOT NULL AND c.status = 'A';
        UPDATE frontend.dbo.vehicle SET run_distance = a.total_course * 100, run_duration = d.drv_duration, last_run_info_update_ts = a.pos_time, version = b.version+1 FROM ( SELECT max(i.id) as rn
            FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
                FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time
                    FROM inserted
                    WHERE nostop_course IS NOT NULL
                    GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i
            GROUP BY vehicle_id, nostop_course ) e Left JOIN inserted a ON a.id = e.rn LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id LEFT JOIN frontend.dbo.mob_device c ON c.vrm_id = b.vrm_id LEFT JOIN (SELECT vrm_id, SUM(CAST(drv_duration as bigint)) AS drv_duration
            FROM frontend.dbo.veh_trip
            GROUP BY vrm_id) AS d ON d.vrm_id = b.vrm_id WHERE b.run_distance<=(a.total_course * 100AND b.vrm_id IS NOT NULL AND c.status = 'A';
        INSERT INTO frontend.dbo.veh_trip_detail
            (veh_trip_id,seq_no,time,lat,lng)
        SELECT c.veh_trip_id, CASE WHEN d.seq_no IS NULL THEN ROW_NUMBER() OVER(ORDER BY a.pos_time) ELSE d.seq_no+ROW_NUMBER() OVER(ORDER BY a.pos_time) END, a.pos_time, a.pos_latitude, a.pos_longitude
        FROM inserted a LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id LEFT JOIN frontend.dbo.veh_trip c ON ( c.vrm_id = b.vrm_id AND a.nostop_course = c.yuwei_trip_id ) LEFT JOIN ( SELECT veh_trip_id, max(seq_no) as seq_no
            FROM frontend.dbo.veh_trip_detail
            GROUP BY veh_trip_id ) AS d ON c.veh_trip_id = d.veh_trip_id LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id =  b.vrm_id
        WHERE c.veh_trip_id IS NOT NULL AND c.start_time <= CONVERT(datetime2(0),a.pos_time) AND CONVERT(datetime2(0),a.pos_time) <= c.end_time AND e.status = 'A';


    my407sw

    Monday, November 18, 2019 8:59 AM

All replies

  • How much do you know about triggers? Merge statements? Be careful of rewriting something because you do not understand it. You should also be very careful of fixing something that is not broke. If you were to format this code you will see there are 2 merge statements, 2 update statements and an insert statement. If it was me I would make sure I had indexes that supported these statements before you tried to rewrite them. This is my version of formatted code in Notepad++

    -------------------------------------------------- Merge 1
    MERGE INTO frontend.dbo.veh_trip Target
    	USING (	SELECT a.nostop_course, c.vrm_id, d.driver_id, a.pos_time, a.once_acc_on_time * 10000, a.command_id, a.alarm_type, a.device_status_3
    			FROM (	SELECT min(i.id) as rn
    					FROM (	SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
    							FROM inserted AS j 
    							RIGHT JOIN (	SELECT vehicle_id, nostop_course, min(pos_time) AS pos_time
    											FROM inserted
    											WHERE nostop_course IS NOT NULL
    											GROUP BY vehicle_id, nostop_course) AS k ON (	j.vehicle_id = k.vehicle_id 
    																							AND j.nostop_course = k.nostop_course 
    																							AND j.pos_time = k.pos_time)
    						) AS i
            
    			GROUP BY vehicle_id, nostop_course ) b 
    			Left join inserted a ON a.id = b.rn 
    			LEFT JOIN frontend.dbo.vehicle c ON a.vehicle_id = c.yuwei_vehicle_id 
    			LEFT JOIN frontend.dbo.driver d ON a.driver_id = d.yuwei_driver_id 
    			LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id = c.vrm_id
        
    			WHERE c.vrm_id IS NOT NULL 
    			AND e.status = 'A' 
    			) AS Source (tripID, vrmID, driverID, postime, distance, command_id, alarm_type, accStatus)
    						ON (Target.vrm_id = Source.vrmID AND Target.yuwei_trip_id = Source.tripID)
    
    WHEN MATCHED AND Target.end_time<=Source.postime AND Target.tripEnd IS NULL
    	THEN UPDATE SET Target.driver_id = Source.driverID
    	, Target.end_time = Source.postime
    	, Target.drv_distance = Source.distance
    	, Target.drv_duration = DATEDIFF(second,Target.start_time,CONVERT(datetime2(0),Source.postime))*1000
    	, Target.tripEnd = CASE WHEN (Source.accStatus & 8 <= 0) THEN 'Y' ELSE NULL END, Target.update_ts = getdate()
    	, Target.update_user = 'System'
    	, Target.version = Target.version+1
    
    WHEN NOT MATCHED BY TARGET AND Source.accStatus & 8 > 0 THEN
        INSERT (yuwei_trip_id,vrm_id,driver_id,acc_date,start_time,end_time,drv_distance)
        VALUES (Source.tripID, Source.vrmID, Source.driverID, Source.postime, Source.postime, Source.postime, Source.distance)
    ;
    -------------------------------------------------- Merge 2
    MERGE INTO frontend.dbo.veh_trip Target
    USING (	SELECT a.nostop_course, c.vrm_id, d.driver_id, a.pos_time, a.once_acc_on_time * 10000, a.command_id, a.alarm_type, a.device_status_3
    		FROM (	SELECT max(i.id) as rn
    				FROM (	SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
    						FROM inserted AS j 
    						RIGHT JOIN (	SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time
    										FROM inserted
    										WHERE nostop_course IS NOT NULL
    										GROUP BY vehicle_id, nostop_course
    									) AS k ON (	j.vehicle_id = k.vehicle_id 
    												AND j.nostop_course = k.nostop_course 
    												AND j.pos_time = k.pos_time)
    						) AS i
    				GROUP BY vehicle_id, nostop_course 
    			) b 
    		Left join inserted a ON a.id = b.rn 
    		LEFT JOIN frontend.dbo.vehicle c ON a.vehicle_id = c.yuwei_vehicle_id 
    		LEFT JOIN frontend.dbo.driver d ON a.driver_id = d.yuwei_driver_id 
    		LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id = c.vrm_id
    
    		WHERE c.vrm_id IS NOT NULL 
    		AND e.status = 'A' 
    		) AS Source (tripID, vrmID, driverID, postime, distance, command_id, alarm_type, accStatus)
                                ON (Target.vrm_id = Source.vrmID AND Target.yuwei_trip_id = Source.tripID)
    
    WHEN MATCHED AND Target.end_time<=Source.postime AND Target.tripEnd IS NULL
        THEN UPDATE SET Target.driver_id = Source.driverID
    	, Target.end_time = Source.postime
    	, Target.drv_distance = Source.distance
    	, Target.drv_duration = DATEDIFF(second,Target.start_time,CONVERT(datetime2(0),Source.postime))*1000
    	, Target.tripEnd = CASE WHEN (Source.accStatus & 8 <= 0) THEN 'Y' ELSE NULL END, Target.update_ts = getdate()
    	, Target.update_user = 'System'
    	, Target.version = Target.version+1
    
    WHEN NOT MATCHED BY TARGET AND Source.accStatus & 8 > 0 THEN
        INSERT (yuwei_trip_id,vrm_id,driver_id,acc_date,start_time,end_time,drv_distance)
        VALUES (Source.tripID, Source.vrmID, Source.driverID, Source.postime, Source.postime, Source.postime, Source.distance)
    ;
    
    -------------------------------------------------- UPDATE 1
    UPDATE frontend.dbo.mob_device 
    SET lat = a.pos_latitude
    , lng = a.pos_longitude
    , last_loc_update_ts = a.pos_time
    , version = c.version+1 
    
    FROM (	SELECT max(i.id) as rn
            FROM (	SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
    				FROM inserted AS j 
    				RIGHT JOIN (	SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time
    								FROM inserted
    								WHERE nostop_course IS NOT NULL
    								GROUP BY vehicle_id, nostop_course) AS k ON (	j.vehicle_id = k.vehicle_id 
    																				AND j.nostop_course = k.nostop_course 
    																				AND j.pos_time = k.pos_time)
    			) AS i
            GROUP BY vehicle_id, nostop_course 
    	) e 
    Left JOIN inserted a ON a.id = e.rn 
    LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id 
    LEFT JOIN frontend.dbo.mob_device c ON b.vrm_id = c.vrm_id 
    
    WHERE (	c.last_loc_update_ts IS NULL OR c.last_loc_update_ts <= a.pos_time) 
    AND b.vrm_id IS NOT NULL 
    AND c.status = 'A'
    ;
    
    -------------------------------------------------- Update 2
    UPDATE frontend.dbo.vehicle 
    
    SET run_distance = a.total_course * 100
    , run_duration = d.drv_duration
    , last_run_info_update_ts = a.pos_time
    , version = b.version+1 
    
    FROM (	SELECT max(i.id) as rn
            FROM (	SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time
    				FROM inserted AS j 
    				RIGHT JOIN (	SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time
    								FROM inserted
    								WHERE nostop_course IS NOT NULL
    								GROUP BY vehicle_id, nostop_course) AS k ON (	j.vehicle_id = k.vehicle_id 
    																				AND j.nostop_course = k.nostop_course 
    																				AND j.pos_time = k.pos_time)
    			) AS i
            GROUP BY vehicle_id, nostop_course 
    		) e 
    Left JOIN inserted a ON a.id = e.rn 
    LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id 
    LEFT JOIN frontend.dbo.mob_device c ON c.vrm_id = b.vrm_id 
    LEFT JOIN (	SELECT vrm_id, SUM(CAST(drv_duration as bigint)) AS drv_duration
    			FROM frontend.dbo.veh_trip
    			GROUP BY vrm_id) AS d ON d.vrm_id = b.vrm_id 
    WHERE b.run_distance<=(a.total_course * 100) 
    AND b.vrm_id IS NOT NULL 
    AND c.status = 'A'
    ;
    
    -------------------------------------------------- INSERT
    INSERT INTO frontend.dbo.veh_trip_detail(veh_trip_id,seq_no,time,lat,lng)
    SELECT c.veh_trip_id, CASE WHEN d.seq_no IS NULL THEN ROW_NUMBER() OVER(ORDER BY a.pos_time) ELSE d.seq_no+ROW_NUMBER() OVER(ORDER BY a.pos_time) END, a.pos_time, a.pos_latitude, a.pos_longitude
    FROM inserted a 
    LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id 
    LEFT JOIN frontend.dbo.veh_trip c ON ( c.vrm_id = b.vrm_id AND a.nostop_course = c.yuwei_trip_id ) 
    LEFT JOIN (	SELECT veh_trip_id, max(seq_no) as seq_no
    			FROM frontend.dbo.veh_trip_detail
    			GROUP BY veh_trip_id 
    			) AS d ON c.veh_trip_id = d.veh_trip_id 
    LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id =  b.vrm_id
    
    WHERE c.veh_trip_id IS NOT NULL 
    AND c.start_time <= CONVERT(datetime2(0),a.pos_time) 
    AND CONVERT(datetime2(0),a.pos_time) <= c.end_time 
    AND e.status = 'A'
    ;

    Monday, November 18, 2019 1:59 PM
  • Well, when it's IO, then the question is: is write or read IO?

    Your reads should be optimizable, cause at the first glance the following sub-query is executed four times:

              FROM   (   SELECT   MIN(i.id) AS rn
                         FROM     (   SELECT j.id ,
                                             j.vehicle_id ,
                                             j.nostop_course ,
                                             j.pos_time
                                      FROM   inserted j
                                             RIGHT JOIN (   SELECT   vehicle_id ,
                                                                     nostop_course ,
                                                                     MIN(pos_time) AS pos_time
                                                            FROM     inserted
                                                            WHERE    nostop_course IS NOT NULL
                                                            GROUP BY vehicle_id ,
                                                                     nostop_course ) k ON (   j.vehicle_id = k.vehicle_id
                                                                                              AND j.nostop_course = k.nostop_course
                                                                                              AND j.pos_time = k.pos_time )) i
    Here you should test against materializing this to temporary table or table variable.

    Monday, November 18, 2019 2:08 PM
  • Hi my407sw,

    Thank you for your posting . 

    In your original script , I found that you are using 'inserted' table . However I could not find any triggers . Could you please explain more ?

    Also , you use Source table many times , and I will suggest you to put the data in a temp table.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 19, 2019 7:06 AM
  • thanks for all your reply, all suggest are very precious for me. Let me describe the usage scenario.

    mainserver_his1911 database receive gps data every 3 or 5 sec, the table tb_gpsinfo store the data, and have a trigger, like below, then write and make some compute to another database named fronetend. Because we have large amount of information, when the trigger fire, SQL Server will consumption CPU and IO. I know the trigger write not good, and want rewrite it, how can I do?

    USE [mainserver_his1911]
    GO
    
    /****** Object:  Trigger [dbo].[trg_tb_gpsinfo_ins]    Script Date: 2019/11/20 上午 08:35:50 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TRIGGER [dbo].[trg_tb_gpsinfo_ins]
    						ON [dbo].[tb_gpsinfo]
    						AFTER INSERT
    						AS
    						BEGIN
    							SET NOCOUNT ON;
    							MERGE INTO frontend.dbo.veh_trip Target
    							USING ( SELECT a.nostop_course, c.vrm_id, d.driver_id, a.pos_time, a.once_acc_on_time * 10000, a.command_id, a.alarm_type, a.device_status_3 FROM ( SELECT min(i.id) as rn FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, min(pos_time) AS pos_time FROM inserted WHERE nostop_course IS NOT NULL GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i GROUP BY vehicle_id, nostop_course ) b Left join inserted a ON a.id = b.rn LEFT JOIN frontend.dbo.vehicle c ON a.vehicle_id = c.yuwei_vehicle_id LEFT JOIN frontend.dbo.driver d ON a.driver_id = d.yuwei_driver_id LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id = c.vrm_id WHERE c.vrm_id IS NOT NULL AND e.status = 'A' ) AS Source (tripID, vrmID, driverID, postime, distance, command_id, alarm_type, accStatus)
    							ON (Target.vrm_id = Source.vrmID AND Target.yuwei_trip_id = Source.tripID)
    							WHEN MATCHED AND Target.end_time<=Source.postime AND Target.tripEnd IS NULL
    								THEN UPDATE SET Target.driver_id = Source.driverID, Target.end_time = Source.postime, Target.drv_distance = Source.distance, Target.drv_duration = DATEDIFF(second,Target.start_time,CONVERT(datetime2(0),Source.postime))*1000, Target.tripEnd = CASE WHEN (Source.accStatus & 8 <= 0) THEN 'Y' ELSE NULL END, Target.update_ts = getdate(), Target.update_user = 'System', Target.version = Target.version+1
    							WHEN NOT MATCHED BY TARGET AND Source.accStatus & 8 > 0 THEN
    								INSERT (yuwei_trip_id,vrm_id,driver_id,acc_date,start_time,end_time,drv_distance)
    								VALUES (Source.tripID, Source.vrmID, Source.driverID, Source.postime, Source.postime, Source.postime, Source.distance);
    							MERGE INTO frontend.dbo.veh_trip Target
    							USING ( SELECT a.nostop_course, c.vrm_id, d.driver_id, a.pos_time, a.once_acc_on_time * 10000, a.command_id, a.alarm_type, a.device_status_3 FROM ( SELECT max(i.id) as rn FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time FROM inserted WHERE nostop_course IS NOT NULL GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i GROUP BY vehicle_id, nostop_course ) b Left join inserted a ON a.id = b.rn LEFT JOIN frontend.dbo.vehicle c ON a.vehicle_id = c.yuwei_vehicle_id LEFT JOIN frontend.dbo.driver d ON a.driver_id = d.yuwei_driver_id LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id = c.vrm_id WHERE c.vrm_id IS NOT NULL AND e.status = 'A' ) AS Source (tripID, vrmID, driverID, postime, distance, command_id, alarm_type, accStatus)
    							ON (Target.vrm_id = Source.vrmID AND Target.yuwei_trip_id = Source.tripID)
    							WHEN MATCHED AND Target.end_time<=Source.postime AND Target.tripEnd IS NULL
    								THEN UPDATE SET Target.driver_id = Source.driverID, Target.end_time = Source.postime, Target.drv_distance = Source.distance, Target.drv_duration = DATEDIFF(second,Target.start_time,CONVERT(datetime2(0),Source.postime))*1000, Target.tripEnd = CASE WHEN (Source.accStatus & 8 <= 0) THEN 'Y' ELSE NULL END, Target.update_ts = getdate(), Target.update_user = 'System', Target.version = Target.version+1
    							WHEN NOT MATCHED BY TARGET AND Source.accStatus & 8 > 0 THEN
    								INSERT (yuwei_trip_id,vrm_id,driver_id,acc_date,start_time,end_time,drv_distance)
    								VALUES (Source.tripID, Source.vrmID, Source.driverID, Source.postime, Source.postime, Source.postime, Source.distance);
    							UPDATE frontend.dbo.mob_device SET lat = a.pos_latitude, lng = a.pos_longitude, last_loc_update_ts = a.pos_time, version = c.version+1 FROM ( SELECT max(i.id) as rn FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time FROM inserted WHERE nostop_course IS NOT NULL GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i GROUP BY vehicle_id, nostop_course ) e Left JOIN inserted a ON a.id = e.rn LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id LEFT JOIN frontend.dbo.mob_device c ON b.vrm_id = c.vrm_id WHERE (c.last_loc_update_ts IS NULL OR c.last_loc_update_ts <= a.pos_time) AND b.vrm_id IS NOT NULL AND c.status = 'A';
    							UPDATE frontend.dbo.vehicle SET run_distance = a.total_course * 100, run_duration = d.drv_duration, last_run_info_update_ts = a.pos_time, version = b.version+1 FROM ( SELECT max(i.id) as rn FROM (SELECT j.id, j.vehicle_id, j.nostop_course, j.pos_time FROM inserted AS j RIGHT JOIN (SELECT vehicle_id, nostop_course, max(pos_time) AS pos_time FROM inserted WHERE nostop_course IS NOT NULL GROUP BY vehicle_id, nostop_course) AS k ON (j.vehicle_id = k.vehicle_id AND j.nostop_course = k.nostop_course AND j.pos_time = k.pos_time)) AS i GROUP BY vehicle_id, nostop_course ) e Left JOIN inserted a ON a.id = e.rn LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id LEFT JOIN frontend.dbo.mob_device c ON c.vrm_id = b.vrm_id LEFT JOIN (SELECT vrm_id, SUM(CAST(drv_duration as bigint)) AS drv_duration FROM frontend.dbo.veh_trip GROUP BY vrm_id) AS d ON d.vrm_id = b.vrm_id WHERE b.run_distance<=(a.total_course * 100) AND b.vrm_id IS NOT NULL AND c.status = 'A';
    							INSERT INTO frontend.dbo.veh_trip_detail(veh_trip_id,seq_no,time,lat,lng)
    								SELECT c.veh_trip_id,CASE WHEN d.seq_no IS NULL THEN ROW_NUMBER() OVER(ORDER BY a.pos_time) ELSE d.seq_no+ROW_NUMBER() OVER(ORDER BY a.pos_time) END,a.pos_time,a.pos_latitude,a.pos_longitude FROM inserted a LEFT JOIN frontend.dbo.vehicle b ON a.vehicle_id = b.yuwei_vehicle_id LEFT JOIN frontend.dbo.veh_trip c ON ( c.vrm_id = b.vrm_id AND a.nostop_course = c.yuwei_trip_id ) LEFT JOIN ( SELECT veh_trip_id, max(seq_no) as seq_no FROM frontend.dbo.veh_trip_detail GROUP BY veh_trip_id ) AS d ON c.veh_trip_id = d.veh_trip_id LEFT JOIN frontend.dbo.mob_device e ON e.vrm_id =  b.vrm_id WHERE c.veh_trip_id IS NOT NULL AND c.start_time <= CONVERT(datetime2(0),a.pos_time) AND CONVERT(datetime2(0),a.pos_time) <= c.end_time AND e.status = 'A';
    						END
    GO
    
    ALTER TABLE [dbo].[tb_gpsinfo] ENABLE TRIGGER [trg_tb_gpsinfo_ins]
    GO
    
    
    


    my407sw

    Wednesday, November 20, 2019 12:49 AM
  • I want rewrite the trigger using the easiest t-sql, no merge, no inserted, can some one help me ? and get me some suggest to improve performance, thanks very much !!

    my407sw

    Wednesday, November 20, 2019 1:13 AM
  • Again, you're using four time the same structured sub-query. Materialize it in to a temporary table or table variables.

    SELECT *
    FROM   (   SELECT   MIN(i.id) AS rn
               FROM     (   SELECT j.id ,
                                   j.vehicle_id ,
                                   j.nostop_course ,
                                   j.pos_time
                            FROM   inserted j
                                   RIGHT JOIN (   SELECT   vehicle_id ,
                                                           nostop_course ,
                                                           MIN(pos_time) AS pos_time
                                                  FROM     inserted
                                                  WHERE    nostop_course IS NOT NULL
                                                  GROUP BY vehicle_id ,
                                                           nostop_course ) k ON (   j.vehicle_id = k.vehicle_id
                                                                                    AND j.nostop_course = k.nostop_course
                                                                                    AND j.pos_time = k.pos_time )) i
               GROUP BY vehicle_id ,
                        nostop_course ) b;
    
    SELECT *
    FROM   (   SELECT   MAX(i.id) AS rn
               FROM     (   SELECT j.id ,
                                   j.vehicle_id ,
                                   j.nostop_course ,
                                   j.pos_time
                            FROM   inserted j
                                   RIGHT JOIN (   SELECT   vehicle_id ,
                                                           nostop_course ,
                                                           MAX(pos_time) AS pos_time
                                                  FROM     inserted
                                                  WHERE    nostop_course IS NOT NULL
                                                  GROUP BY vehicle_id ,
                                                           nostop_course ) k ON (   j.vehicle_id = k.vehicle_id
                                                                                    AND j.nostop_course = k.nostop_course
                                                                                    AND j.pos_time = k.pos_time )) i
               GROUP BY vehicle_id ,
                        nostop_course ) b;
    
    SELECT *
    FROM   (   SELECT   MAX(i.id) AS rn
               FROM     (   SELECT j.id ,
                                   j.vehicle_id ,
                                   j.nostop_course ,
                                   j.pos_time
                            FROM   inserted j
                                   RIGHT JOIN (   SELECT   vehicle_id ,
                                                           nostop_course ,
                                                           MAX(pos_time) AS pos_time
                                                  FROM     inserted
                                                  WHERE    nostop_course IS NOT NULL
                                                  GROUP BY vehicle_id ,
                                                           nostop_course ) k ON (   j.vehicle_id = k.vehicle_id
                                                                                    AND j.nostop_course = k.nostop_course
                                                                                    AND j.pos_time = k.pos_time )) i
               GROUP BY vehicle_id ,
                        nostop_course ) e;
    
    SELECT *
    FROM   (   SELECT   MAX(i.id) AS rn
               FROM     (   SELECT j.id ,
                                   j.vehicle_id ,
                                   j.nostop_course ,
                                   j.pos_time
                            FROM   inserted j
                                   RIGHT JOIN (   SELECT   vehicle_id ,
                                                           nostop_course ,
                                                           MAX(pos_time) AS pos_time
                                                  FROM     inserted
                                                  WHERE    nostop_course IS NOT NULL
                                                  GROUP BY vehicle_id ,
                                                           nostop_course ) k ON (   j.vehicle_id = k.vehicle_id
                                                                                    AND j.nostop_course = k.nostop_course
                                                                                    AND j.pos_time = k.pos_time )) i
               GROUP BY vehicle_id ,
                        nostop_course ) e;

    The other thing you should review: You architecture.

    It sounds like you should look into stream processing and event sourcing.

    A poor mans's implementation could be using a queue where the incoming data is stored and your system polls this data in a regular interval or when a certain row threshold is reached. This would allow to do the necessary INSERTs and UPDATEs in a stored procedure, instead of triggers. Which should be avoided in such a scenario.

    Wednesday, November 20, 2019 8:53 AM
  • Ahh, after doing this, your don't even need tables, as your only looking for scalars, e.g.

    DECLARE @MaxID INT;
    DECLARE @MinID INT;
    
    DECLARE @Intermediate TABLE (
        vehicle_id INT NOT NULL ,
        nostop_course BIT NOT NULL ,
        max_pos_time TIME NOT NULL ,
        min_pos_time TIME NOT NULL ,
        PRIMARY KEY (
            vehicle_id ,
            nostop_course )
    );
    
    INSERT INTO @Intermediate ( vehicle_id ,
                                nostop_course ,
                                max_pos_time ,
                                min_pos_time )
                SELECT   vehicle_id ,
                         nostop_course ,
                         MAX(pos_time) AS max_pos_time ,
                         MIN(pos_time) AS min_pos_time
                FROM     inserted
                WHERE    nostop_course IS NOT NULL
                GROUP BY vehicle_id ,
                         nostop_course;
    
    SET @MaxID = (   SELECT   MAX(j.id)
                     FROM     inserted j
                              RIGHT JOIN @Intermediate k ON j.vehicle_id = k.vehicle_id
                                                            AND j.nostop_course = k.nostop_course
                                                            AND j.pos_time = k.max_pos_time
                     GROUP BY j.vehicle_id ,
                              j.nostop_course );
    SET @MinID = (   SELECT   MIN(j.id)
                     FROM     inserted j
                              RIGHT JOIN @Intermediate k ON j.vehicle_id = k.vehicle_id
                                                            AND j.nostop_course = k.nostop_course
                                                            AND j.pos_time = k.min_pos_time
                     GROUP BY j.vehicle_id ,
                              j.nostop_course );

    Wednesday, November 20, 2019 9:09 AM
  • I hate triggers because you don't see its consequences in an execution plan. 

    Example, if I were write: INSERT TableA VALUES(), and there is an insert trigger for TableA, I don't see the trigger code in the Estimated Execution Plan.

    There is the DMV sys.dm_exec_trigger_stats, but I want to see it from SSMS without executing.

    If you are absolutely sure this trigger can only be activated from a single procedure, I would pull out the code and put it in that proc (negating the need for a trigger)

    I does seem Stefan's has a very good decomposition of the function of this code


    jchang

    Thursday, November 21, 2019 2:29 PM