Using variable in a query

• rohini-145252

  Old Hand

  Points: 308

  More actions

  November 7, 2006 at 3:47 am

  #115035

  In a stored procedure, when i use variable in the where condition of a query then the performance is very slow. But when i directly subtitute the values, the performance is really good

  For eg:

  declare @CompID varchar(16),@FinYr varchar(15),@JrlID varchar(10)

  set @CompID='BM' 

  set @JrlID='CA_P'                                       

  set @FinYr='2006-2007'

  Select Trans_Header.Company,Journal.JrlID, Trans_Header.TrnDate,Trans_Header.TrnNo FROM         Trans_Header INNER JOIN

                        Journal ON Trans_Header.CompanyID = Journal.CompanyID AND Trans_Header.Finyear = Journal.FinYear AND Trans_Header.JrlID = Journal.JrlID WHERE Trans_Header.CompanyID=@CompID and Trans_Header.Finyear=@FinYr and Journal.JrlID=@JrlID  

  WHen i substitute the variables as

  Select Trans_Header.Company,Journal.JrlID, Trans_Header.TrnDate,Trans_Header.TrnNo FROM         Trans_Header INNER JOIN

                        Journal ON Trans_Header.CompanyID = Journal.CompanyID AND Trans_Header.Finyear = Journal.FinYear AND Trans_Header.JrlID = Journal.JrlID WHERE Trans_Header.CompanyID='BM' and Trans_Header.Finyear='2006-2007' and Journal.JrlID='ca_p'

   

  the query seems to be very faster.

   

  Theres no change in performance when i change Journal.JrlID's value as the datas in Journal table is very less compared to the data in Trans_Header table.

  The Journal table is a master table whereas Trans_Header is a transaction table

   

  Any help appreciated.

   

  Regards,

  Rohini

   

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  November 7, 2006 at 3:56 am

  #670249

  What are the datatypes of the clumns:

  Trans_Header.CompanyID;

  Trans_Header.Finyear;

  Journal.JrlID=@JrlID

  "Varchar" is not an answer.

  _____________
  Code for TallyGenerator

• rohini-145252

  Old Hand

  Points: 308

  More actions

  November 7, 2006 at 4:29 am

  #670253

  The datatypes as

  Trans_header.CompanyID -- varchar(16), Trans_header.Finyear --  varchar(15), Journal.JrlID -- varchar(10)

• Bob Roby

  SSC Journeyman

  Points: 92

  More actions

  November 8, 2006 at 11:43 am

  #670667

  You may be running into a problem with Parameter Sniffing. Good details in this article:

  http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/a61d6b3fd1a32b08?output=gplain

  As a workaround, you could try assigning some local variables with the values passed from the variables passed into the SP. I've seen it make a difference.

  For example:

  CREATE PROCEDURE sp_MyProc

  @SPVAR VARCHAR(10)

  AS

  DECLARE @LOCALVAR VARCHAR(10)

  SET @LOCALVAR = @SPVAR

  SELECT... etc.