CODEWITHSUNDEEP
sqlsql-serverdatabaseperformancesql-server-2012
Nayan Rudani
Nayan Rudani

Reputation: 1189

Same SQL Server query taking different times to execute (where statement same but syntax is different)

The two queries shown here are taking different times to execute. Both have the same WHERE condition and difference is only that where condition syntax is different.

The second query is generated from dynamic query.

Let me know if you know reason for the same.

And also let me know which one is best for performance.

Query #1 (taking 1.5 second to execute)

DECLARE @caseDetailId  VARCHAR(MAX) = '16',
        @patientId VARCHAR(8000) = NULL,
        @isActive NVARCHAR(4000) = NULL,
        @description NVARCHAR(4000) = NULL,
        @clientId VARCHAR(8000) = '1021',
        @machineId VARCHAR(8000) = NULL,
        @oldSystemId VARCHAR(8000) = NULL,
        @isDeleted NVARCHAR(4000) = NULL,
        @userId INT,
        @langId VARCHAR(10) = NULL,
        @page INT = 0,
        @size INT = 0,
        @orderBy VARCHAR(400) = NULL

--Query 1
SELECT * 
FROM CaseDetail 
WHERE 1 = 1
  AND (@isDeleted IS NULL OR [IsDeleted] = @isDeleted)
  AND (@clientId IS NULL OR [ClientId] = @clientId)
  AND (@caseDetailId IS NULL OR [CaseDetailId] IN (SELECT id 
                                                   FROM dbo.Fnsplit(@caseDetailId,',')))
  AND (@patientId IS NULL OR [PatientId] IN (SELECT id 
                                             FROM dbo.Fnsplit(@patientId,',')))
  AND (@isActive IS NULL OR [IsActive] IN (@isActive))
  AND ((@description IS NULL )
        OR (@description IS NOT NULL AND [Description] LIKE  '%'+@description+'%'))

Query #2 (taking 0.016 second to execute):

DECLARE @caseDetailId  VARCHAR(MAX) = '16',
        @patientId VARCHAR(8000) = NULL,
        @isActive NVARCHAR(4000) = NULL,
        @description NVARCHAR(4000) = NULL,
        @clientId VARCHAR(8000) = '1021',
        @machineId VARCHAR(8000) = NULL,
        @oldSystemId VARCHAR(8000) = NULL,
        @isDeleted NVARCHAR(4000) = NULL,
        @userId INT,
        @langId VARCHAR(10) = NULL,
        @page INT = 0,
        @size INT = 0,
        @orderBy VARCHAR(400) = NULL

--Query 2
SELECT * 
FROM CaseDetail 
WHERE 1 = 1
  AND CaseDetail.CaseDetailId IN (SELECT Id FROM dbo.Fnsplit(@CaseDetailId,','))
  AND CaseDetail.ClientId IN (SELECT Id FROM dbo.Fnsplit(@clientId,','))

Upvotes: 0

Views: 121

Answers (2)

Hadi Ehterami
Hadi Ehterami

Reputation: 83

You must know this notes:

  1. OR in the WHERE clauser is a very bad practice.
  2. Compare a value with NULL had affectt on performance.
  3. Sub Query with IN has very heavy overhead to performace.

Upvotes: 1

Angel M.
Angel M.

Reputation: 1358

The OR clause in the WHERE condition is a very bad practise in general with a few exceptions. The SQL Optimizer is unable to find a good plan to use an index, so, it has to do a full table scan (read the full table). So, both provided queries have this difference and in the second case is when you report a good speed. Try to reformulate the first query without the OR conditions in the WHERE and should fix the performance issue.

Upvotes: 1

Related Questions