CODEWITHSUNDEEP
sqlsql-server-2008union
KeDaR
KeDaR

Reputation: 81

How To Improve Performance of Sql Query with Union

Below query takes almost 47-50 seconds on our production server, but when I execute the same on a test server with the same database, it executes in less than a second.

CREATE TABLE [dbo].[Table0]
(
    [UserID] [VARCHAR](20) NOT NULL,
    [Key] [VARCHAR](600) NOT NULL,
    [Type] [VARCHAR](20) NOT NULL,
    [status] [VARCHAR](10) NOT NULL,
    [RecID] [NUMERIC](18, 0) NOT NULL,

    CONSTRAINT [PK_Table0] 
        PRIMARY KEY CLUSTERED ([UserID] ASC, [Key] ASC, [Type] ASC, [status], [RecID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2]
(
     [UserID] [VARCHAR](20) NOT NULL,
     [RecID] [NUMERIC](18, 0) NOT NULL,
     [Activity] [NUMERIC](2, 0) NOT NULL,
     [AwsExist] [NUMERIC](2, 0) NULL,
     [View] [NUMERIC](2, 0) NULL,

     CONSTRAINT [PK_Table2] 
         PRIMARY KEY CLUSTERED ([UserID] ASC, [RecID] ASC, [Activity] ASC)
                     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                           ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3]
(
     [UserID] [VARCHAR](20) NOT NULL,
     [RecId] [NUMERIC](18, 0) NOT NULL,
     [Activity] [NUMERIC](2, 0) NOT NULL,
     [AwsExist] [NUMERIC](2, 0) NULL,
     [RecDate] [DATETIME] NULL,
     [View] [NUMERIC](2, 0) NULL,

     CONSTRAINT [PK_Table3] 
         PRIMARY KEY CLUSTERED ([UserID] ASC, [RecId] ASC, [Activity] ASC)
                     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                           ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

which parameter should I check to resolve the issue? I just want to check if a record exists in one of these table for RecID

 Select UserID From Table0 Where RecId = 56445 And Type = 'D' 
 Union
 Select UserID From Table1 Where RecId = 56445 And View = 0 
 Union
 Select UserID From Table2 Where RecId = 56445 And View = 0

I tried with this code:

DECLARE RecID AS INT
SET RecID = 56445

IF NOT EXISTS (SELECT DISTINCT TOP(1) USerID 
               FROM Table0 
               WHERE RecId = @RecID 
                 AND Type = 'D')
BEGIN
    IF NOT EXISTS (SELECT DISTINCT TOP(1) UserID 
                   FROM Table1 
                   WHERE RecId = @RecID AND View = 0)
    BEGIN
        SELECT DISTINCT TOP(1) UserID 
        FROM Table2 
        WHERE RecId = @RecID AND View = 0
    END
END

but it also takes around 18 seconds.

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This query should be fast -- assuming not many rows are returned:

Select UserID From Table0 Where RecId = 56445 And Type = 'D' 
Union
Select UserID From Table1 Where RecId = 56445 And View = 0 
Union
Select UserID From Table2 Where RecId = 56445 And View = 0;

What you need are indexes:

  • Table0(recid, type, userid)
  • Table1(recid, view, userid)
  • Table2(recid, view, userid)

Upvotes: 1

Divyesh patel
Divyesh patel

Reputation: 987

You may have to use query like:

 Select UserID
 From table0 
 Where exists (
 Select 1 
 From Table0 t0, Table1 t1, Table2 t2
 Where (t0.RecId = 56445 and t0.Type= 'D') OR (t1.RecId = 56445 and t1.View= 0) OR (t2.RecId = 56445 AND t2.View=0))

Upvotes: 0

Related Questions