ALTER TRIGGER ReIndex
ON dbo.Restaurants
	FOR INSERT, UPDATE, DELETE
AS
	SET NOCOUNT ON
	DECLARE @tbl_upstreams 	TABLE (id_auto INT NOT NULL IDENTITY(1,1), str_upstream NVARCHAR(4000))
	DECLARE @tbl_areas 		TABLE (id_area INT)
	DECLARE @int_loop 		INT
	DECLARE @int_rc			INT
	DECLARE @int_i			INT
	DECLARE @int_d	 		INT
	DECLARE @str_upstream	NVARCHAR(4000)

	SELECT @int_d = COUNT(*) FROM Deleted
	SELECT @int_i = COUNT(*) FROM Inserted

	IF UPDATE(parent) OR UPDATE(active) OR (@int_d > 0 AND NOT (@int_i > 0))
	BEGIN
		IF @int_d > 0
		BEGIN
			IF @int_i > 0 AND NOT UPDATE(parent)
			BEGIN
				INSERT INTO @tbl_upstreams (str_upstream)
				SELECT DISTINCT LTRIM(RTRIM(ISNULL(CAST(r.upstream AS NVARCHAR), '')))
				FROM restaurants AS r
				JOIN deleted AS d
					ON r.[id] = d.[id]
				JOIN inserted AS i
					ON d.[id] = i.[id]
				WHERE i.active = 0
					AND d.active != i.active
			END
			ELSE
			BEGIN
				INSERT INTO @tbl_upstreams (str_upstream)
				SELECT DISTINCT LTRIM(RTRIM(ISNULL(CAST(r.upstream AS NVARCHAR), '')))
				FROM restaurants AS r
				JOIN deleted AS d
					ON r.[id] = d.[id]
			END

			SET @int_rc		= @@ROWCOUNT
			SET @int_loop	= 0

			WHILE @int_loop < @int_rc
			BEGIN
				SET @int_loop = @int_loop + 1
				SET @str_upstream = (SELECT str_upstream FROM @tbl_upstreams WHERE id_auto = @int_loop)
				
				INSERT INTO @tbl_areas 
				SELECT DISTINCT Items
				FROM dbo.Split(@str_upstream, ' ')
				WHERE Items NOT IN (SELECT id_area FROM @tbl_areas)
			END

			DELETE FROM @tbl_areas
			WHERE EXISTS (
				SELECT [id] FROM restaurants AS r
				WHERE r.upstream LIKE '% ' + CAST(id_area AS VARCHAR) + ' %'
					AND r.active != 0
					AND r.[id] NOT IN (SELECT [id] FROM deleted)
				)

			UPDATE Areas SET Site7 = 0
			WHERE [ID] IN (SELECT id_area FROM @tbl_areas)
				AND Site7 != 0

			DELETE FROM @tbl_areas
			DELETE FROM @tbl_upstreams
		END

		IF @int_i > 0
		BEGIN
			IF UPDATE(parent)
			BEGIN
				UPDATE restaurants 
				SET upstream = dbo.GetUpstream(i.parent)
				FROM restaurants AS r
				JOIN inserted AS i
					ON r.[id] = i.[id]

				INSERT INTO @tbl_upstreams (str_upstream)
				SELECT DISTINCT LTRIM(RTRIM(ISNULL(CAST(r.upstream AS NVARCHAR), '')))
				FROM restaurants AS r
				JOIN inserted AS i
					ON r.[id] = i.[id]
				WHERE i.active != 0
			END

			IF @int_d > 0
			BEGIN
				INSERT INTO @tbl_upstreams (str_upstream)
				SELECT DISTINCT LTRIM(RTRIM(ISNULL(CAST(r.upstream AS NVARCHAR), '')))
				FROM restaurants AS r
				JOIN deleted AS d
					ON r.[id] = d.[id]
				JOIN inserted AS i
					ON d.[id] = i.[id]
				WHERE i.active != 0
					AND d.active != i.active
			END
			ELSE
			BEGIN
				INSERT INTO @tbl_upstreams (str_upstream)
				SELECT DISTINCT LTRIM(RTRIM(ISNULL(CAST(r.upstream AS NVARCHAR), '')))
				FROM restaurants AS r
				JOIN inserted AS i
					ON r.[id] = i.[id]
				WHERE i.active != 0
			END

			SET @int_rc		= (SELECT COUNT(str_upstream) FROM @tbl_upstreams)
			SET @int_loop	= 0

			WHILE @int_loop < @int_rc
			BEGIN
				SET @int_loop = @int_loop + 1
				SET @str_upstream = (SELECT TOP 1 str_upstream FROM @tbl_upstreams)
				DELETE FROM @tbl_upstreams WHERE str_upstream = @str_upstream
				
				INSERT INTO @tbl_areas 
				SELECT DISTINCT Items
				FROM dbo.Split(@str_upstream, ' ')
				WHERE Items NOT IN (
					SELECT id_area FROM @tbl_areas
					)
			END

			UPDATE Areas SET Site7 = -1
			WHERE [ID] IN (
				SELECT id_area FROM @tbl_areas
				)
				AND Site7 = 0
		END
	END

quote:

---

AFTER is the default, if FOR is the only keyword specified.

---

quote:

---

Originally posted by kselvia

I would start with replacing dbo.GetUpstream(i.parent) with an inline SELECT if it is not too complex to do it.

If it is to complex, try creating a computed column called UpStream on Resturants that equals dbo.GetUpstream(parent) and index it.

Caveat: Computed columns have cause me grief and it seems that is the consensus of many others as well.

---

quote:

---

Originally posted by kselvia

Also, what does your split function look like? Parsing lists is often unavoidable but if you have an option of turning them into tables that will obviously help. If you can't, fixed length elements can be parsed faster than delimited lists if your data lends itself to doing it that way. Obviously you need a well implemented split() but I suspect it's pretty good.

---

ALTER FUNCTION dbo.Split
(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Idx int not null identity(1,1), Items nvarchar(4000))
AS


    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL ZERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
    WHILE @INDEX !=0 BEGIN	
        	-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
        	SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
        	-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
        	IF @INDEX !=0
        		SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
        	ELSE
        		SELECT @SLICE = @STRING
        	-- PUT THE ITEM INTO THE RESULTS SET
        	INSERT INTO @Results(Items) VALUES(@SLICE)
        	-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
        	SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
        	-- BREAK OUT IF WE ARE DONE
        	IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END