Subqueries / Joins ...

[Frank (no reg)]

Hi,

in Bezug auf Access kann ich dir das leider nicht sagen, was SQL Server betrifft, sind die Ausführungspläne und damit die Kosten ziemlich ähnlich. Beide verwenden ein Hash-Match, die Outer-Join Variante mit der doppelten verarbeiteten Zeilenmenge plus einem zusätzlichen Filter-Schritt.

Ausgabe vom Trace:

SELECT userName FROM allUsers  WHERE allUsers.userName
NOT IN (SELECT userName FROM Users)
SQL:StmtCompleted 47 16 91 0

SELECT allUsers.userName FROM allUsers
LEFT OUTER JOIN Users ON allUsers.userName = Users.userName
WHERE Users.userName IS NULL
SQL:StmtCompleted 63 47 91 0

1. Spalte = Statement Duration
2. Spalte = CPU
3./4. Spalte = Logical Reads / Writes

Nachfolgend der TestCode:

  
CREATE DATABASE userTest  
GO  
  
USE userTest  
GO  
  
CREATE TABLE allUsers (  
  userId int NOT NULL IDENTITY(0,1) PRIMARY KEY,  
  userName varchar(50) NOT NULL,  
)  
GO  
  
CREATE TABLE Users (  
  userId int NOT NULL IDENTITY(0,1) PRIMARY KEY,  
  userName varchar(50) NOT NULL,  
)  
GO  
  
ALTER FUNCTION getUserName  
(  
  @intValue int  
)  
RETURNS varchar(50)  
AS BEGIN  
  DECLARE @returnName varchar(50)  
  SET @returnName = ''  
  
  WHILE @intValue <> 0 BEGIN  
    DECLARE @rest int  
    SET @rest = @intValue % 14  
  
    IF @rest < 10  
      SET @returnName = CHAR(48+@rest) + @returnName -- add a number  
    ELSE IF @rest < 14  
      SET @returnName = CHAR(65+(@rest-10)) + @returnName -- add a letter  
  
    SET @intValue = @intValue/14  
  END  
  
  RETURN @returnName  
END  
GO  
  
SELECT dbo.getUserName(156554645)  
  
DECLARE @counter int  
SET @counter = 2990000  
  
WHILE (@counter < 3000000) BEGIN  
  DECLARE @myName varchar(50)  
  SET @myName = dbo.getUserName(@counter)  
  INSERT INTO allUsers (userName) VALUES (@myName)  
  
  IF (@counter < 2995000) BEGIN  
    INSERT INTO Users (userName) VALUES (@myName)  
  END  
  SET @counter = @counter + 1  
END  
  
SELECT userName FROM allUsers  
WHERE allUsers.userName  
NOT IN (SELECT userName FROM Users)  
GO  
  
SELECT allUsers.userName  
FROM allUsers  
LEFT OUTER JOIN Users ON allUsers.userName = Users.userName  
WHERE Users.userName IS NULL  
GO  

Viel Spass beim Nachstellen.

Ciao, Frank