Unable to validate count of an SQL Query-CodePudding

I am trying to validate count of a table, but the query continuously running for 20 mins. What could be wrong?

IF((select COUNT(1) from test.[dbo].[as_EmployeeData] (nolock) ed
JOIN test.[dbo].DepartmentData dd ON ed.EmployeeId= dd.DepartmentData
AND dd.Name = 'IT' AND dd.Status = 'Completed')= 0
)
BEGIN
PRINT 'Successful'
END
ELSE
BEGIN
PRINT 'Failed'
END

Whereas if I run the below query I get the result as 0

select COUNT(1) from test.[dbo].[as_EmployeeData] (nolock) ed
JOIN test.[dbo].DepartmentData dd ON ed.EmployeeId= dd.DepartmentData
AND dd.Name = 'IT' AND dd.Status= 'Completed'

CodePudding user response：

One option is to stick it in a variable:

DECLARE @myCount INT = (SELECT COUNT(1) 
                        FROM test.[dbo].[as_EmployeeData] ed
                             INNER JOIN test.[dbo].DepartmentData dd 
                                        ON dd.DepartmentData = ed.EmployeeId 
                                           AND dd.Name = 'IT' 
                                           AND dd.Status= 'Completed');

IF @myCount != 0
BEGIN 
    PRINT 'Successful'
END
ELSE
BEGIN
    PRINT 'Failed'
END

However, the COUNT will gradually become more and more inefficient as more and more data is entered.

Your best bet is to do an EXISTS check:

IF EXISTS (SELECT TOP 1 1 
           FROM test.[dbo].[as_EmployeeData] ed
                INNER JOIN test.[dbo].DepartmentData dd 
                           ON dd.DepartmentData = ed.EmployeeId 
                              AND dd.Name = 'IT' 
                              AND dd.Status= 'Completed')
BEGIN
     PRINT 'Successful'
END
ELSE 
BEGIN
     PRINT 'Failed'
END

That said - I can't see the logical reason for such a query; what else are you doing in the if/else block?

CodePudding user response：

you could try to do a top 1 in stead of a count, and then use an exists

IF ( exists( select top 1 1
             from test.[dbo].[as_EmployeeData] ed
               JOIN test.[dbo].DepartmentData dd 
                 ON ed.EmployeeId = dd.DepartmentData
                AND dd.Name = 'IT' 
                AND dd.Status = 'Completed'
           )
   )
BEGIN
  PRINT 'Successful'
END
ELSE
BEGIN
  PRINT 'Failed'
END

CodePudding user response：

IF NOT EXISTS(SELECT 1 
            FROM test.[dbo].[as_EmployeeData] AS ED WITH(NOLOCK)
                INNER JOIN test.[dbo].DepartmentData AS DD WITH(NOLOCK) ON ED.EmployeeId = DD.DepartmentData
            WHERE DD.[Name] = 'IT' AND DD.[Status] = 'Completed')
BEGIN
    PRINT 'Successful'
END
ELSE
BEGIN
    PRINT 'Failed'
END