Month: August 2008 Page 2 of 5

I’ve been assigned a task to delete data from a table with a subtree structure which means the foreign key can be contained in another rows in the same table. It’s a self contained foreign key on the same table.

Theoritically when it comes to my mind, i was thinking of recursive delete. But how????I’ve tried to google about it, I found this article from Microsoft. These are coming from Microsoft Article as well.

These are the dummy data that you can play around with in your DB

CREATE TABLE Employees
(empid int NOT NULL,
 mgrid int NULL,
 empname varchar(25) NOT NULL,
 salary money NOT NULL,
 CONSTRAINT PK_Employees_empid PRIMARY KEY(empid))

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 1, NULL, 'Nancy',  $10000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 2,    1, 'Andrew',  $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 3,    1, 'Janet',   $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 4,    1, 'Margaret',$5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 5,    2, 'Steven',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 6,    2, 'Michael', $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 7,    3, 'Robert',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 8,    3, 'Laura',   $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 9,    3, 'Ann',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(10,    4, 'Ina',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(11,    7, 'David',   $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(12,    7, 'Ron',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(13,    7, 'Dan',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(14,   11, 'James',   $1500.00)

I’m going to delete employee no 3(Janet) which means it need to delete 7(Robert),8(Laura),9(Ann) and 11(David),12(Ron),13(Dan), 14(James) based on the managerial structure level.

What you need to do is to create a trigger for cascading delete

CREATE TRIGGER trg_d_employees_on_delete_cascade ON Employees FOR DELETE
AS

IF EXISTS(SELECT *
          FROM
              Employees AS E
            JOIN
              deleted   AS D ON E.mgrid = D.empid)

  DELETE FROM Employees
  FROM
      Employees AS E
    JOIN
      deleted   AS D ON E.mgrid = D.empid

  GO

It will self join based on the deleted table. It’s not finish yet, you need to set the recursive trigger to active by executing command like

ALTER DATABASE DMO
SET RECURSIVE_TRIGGERS ON

Action Time:

DELETE FROM employees WHERE empid = 3

and you will see that it deletes all the subordinates of Janet

In this post, I will discuss on how to optimize join. Based on my experience. When you join record you need to join as fewer as possible otherwise it will result in performance. And please make sure you put the index on the key that you are using for join.

Within SQL Server 2005, You can use temporary view, It is not a table variable and it is not a temporary table. You also need to try to avoid cursor as well if possible.

First sample is a traditional join where I joined all the tables that required. In this sample EvenResult table has 20 million rows and I need to count the evenresult table based on the EventID that I get from the join criteria.

1. Join all tables./Traditional Way and it takes about 21secs

    SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            INNER JOIN
                EventResults er
            ON
                E.EventID = er.EventID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))

2. Using Temporary Views, In this query. I try to filter down all the required eventID based on the criteria and insert it into a temporary view(tblEvents). Once i get the filtered result then I joined it with EventResults table and It takes only 16secs considering the number of records.

WITH tblEvents(EventID) AS
(
            SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))
)

            SELECT
                COUNT(*) As Total
            FROM
                tblEvents te
            INNER JOIN
                EventResults er
            ON
                te.EventID = er.EventID
            GROUP BY
                er.MemberID
            HAVING
                COUNT(*) >= @MinGame
            AND
                COUNT(*) <= @MaxGame