Use of Variables in Where Clause in SQL Server

Question

When I am using Delcared Variables in Where clause Query takes time but when I use constant values it did not.

With Variable -- 15 sec

DECLARE @V_EXTERNALCONTRACTNBR varchar(100),
        @V_REGIONALID int

SET @V_EXTERNALCONTRACTNBR = '310046'
SET @V_REGIONALID = 10

SELECT
  C.CONTRACT_ID AS CONTRACTID,
  C.CONTRACT_STS AS CONTRACTSTATUS,
  C.LIVE_STS AS LIVESTS,
  CC.BUSINESS_PARTNER_ID AS BUSINESSPARTNERID
FROM CONT C (NOLOCK)
INNER JOIN CUST_CONT CC (NOLOCK)
  ON C.REGIONAL_ID = CC.REGIONAL_ID
  AND C.CONTRACT_ID = CC.CONTRACT_ID
WHERE C.REGIONAL_ID = @V_REGIONALID
AND CC.REL_ROLE_CDE = 'B'
AND C.EXTERNAL_CONTRACT_NBR = @V_EXTERNALCONTRACTNBR

With Conatant values -1 sec

SELECT
  C.CONTRACT_ID AS CONTRACTID,
  C.CONTRACT_STS AS CONTRACTSTATUS,
  C.LIVE_STS AS LIVESTS,
  CC.BUSINESS_PARTNER_ID AS BUSINESSPARTNERID
FROM CONT C (NOLOCK)
INNER JOIN CUST_CONT CC (NOLOCK)
  ON C.REGIONAL_ID = CC.REGIONAL_ID
  AND C.CONTRACT_ID = CC.CONTRACT_ID
WHERE C.REGIONAL_ID = 10
AND CC.REL_ROLE_CDE = 'B'
AND C.EXTERNAL_CONTRACT_NBR = '310046'

Without a lot more information to go on, not a lot we can do to help here. Also, what is the reason for your use of `NOLOCK`? You're happy to receive incorrect data from your query? — Thom A, Dec 21 '17 at 12:33
https://dba.stackexchange.com/questions/192378/why-is-query-choosing-horrible-execution-plan/192382#192382 — Martin Smith, Dec 21 '17 at 12:41
You can skip (Nolock).But use of variables in where clause make it a time consuming.i have posted both the queries (with contant and with variable for reference). — Ghulam Haider, Dec 21 '17 at 12:44

Use of Variables in Where Clause in SQL Server

0 Answers0