Multitable UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multitable UNION

Author

Topic

RichardA7
Starting Member

3 Posts

Posted - 2009-06-26 : 04:28:57

We have a db with Employees and Case files and some tables are logging activities that the emplyees perform. An Employee can be deleted only when all activities that Employee has been involved in are deleted. To be able to test if an Employee is deletable a huge query is created.

This query looks like:

SELECT eID
FROM employees
WHERE eID NOT IN (
SELECT x
FROM tableY
UNION
SELECT x
FROM tableZ
UNION
...

and so on for 27 tebles, some containing more than 500 000 rows. This query often makes a timeout (takes more than 30 sec).

All tables are indexed.

Is there any smarter/faster way to perform such a query?

Richard Andersson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 04:31:32

[code]CREATE TABLE #Temp
(
eID INT PRIMARY KEY
)

INSERT #Temp
SELECT x
FROM TableY

INSERT #Temp
SELECT x
FROM TableZ

SELECT eID
FROM Employees AS e
WHERE NOT EXISTS (SELECT * FROM #Temp AS t WHERE t.eID = e.eID)

DROP TABLE #Temp[/code]

E 12°55'05.63"
N 56°04'39.26"

RichardA7
Starting Member

3 Posts

Posted - 2009-06-26 : 06:01:49

When an employeeID exists in more than one table, the INSERT #Temp produces an error because you can't have two primary keys with the same value.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:15:25

Drop the PRIMARY KEY. I typed that by habit only.

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-26 : 06:15:52

[code]
CREATE TABLE #Temp
(
eID INT PRIMARY KEY
)

INSERT #Temp
SELECT x
FROM TableY

INSERT #Temp
SELECT x
FROM TableZ z
LEFT JOIN #temp t ON t.eid = z.x
WHERE t.eid IS NULL

SELECT eID
FROM Employees AS e
WHERE NOT EXISTS (SELECT * FROM #Temp AS t WHERE t.eID = e.eID)

DROP TABLE #Temp
[/code]

RichardA7
Starting Member

3 Posts

Posted - 2009-06-26 : 07:13:27

After tests on a minor db it looks like the first suggestion made a 20% decrease on the execution time.
The suggestion to add LEFT JOINS was a boost and made the execution time app. 50% of the original.

Thank you very much for the help, Peso and bklr!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources