What is the most efficient way to find all entries which do overlap with others in the same table? Every entry has a start and end date. For example I have the following database setup:
CREATE TABLE DEMO
(
DEMO_ID int IDENTITY ,
START date NOT NULL ,
END date NOT NULL
};
INSERT INTO DEMO (DEMO_ID, START, END) VALUES (1, '20100201', '20100205');
INSERT INTO DEMO (DEMO_ID, START, END) VALUES (2, '20100202', '20100204');
INSERT INTO DEMO (DEMO_ID, START, END) VALUES (3, '20100204', '20100208');
INSERT INTO DEMO (DEMO_ID, START, END) VALUES (4, '20100206', '20100211');
My query looks as follow:
SELECT DISTINCT *
FROM DEMO A, DEMO B
WHERE A.DEMO_ID != B.DEMO_ID
AND A.START < B.END
AND B.START < A.END
The problem is when my demo table has for example 20'000 rows the query takes too long. My environment is MS SQL Server 2008.
Thanks for any more efficient solution
