Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    13

    Unanswered: Local variable slows down SP (?)

    Hi guys I am sitting and testing som variants of this simple SP, and I have an question that I couldent answer with google or any thread in this forum.

    Perhaps I am doing something really easy completly wrong here.

    Why does the local variables in the first code segment slow down the overall execution of the procedure?
    Dont mind the logic why I have them there are only testing som things out.

    If i declare two variables the same way:
    DECLARE @v INT
    SET @v = 100

    When I use it in a WHERE CLAUSE:
    ...WHERE [V] BETWEEN @v AND @x)
    Is there any different then
    ...WHERE [V] BETWEEN 100 AND 200)

    Cant figure this out, why does it hurt the performance so bad? As a C# guy its the same thing ?

    Thanks in advance
    /Johan

    Slow
    Code:
    ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
    	@beginDate DATETIME = null,
    	@endDate DATETIME = null,
    	@beginTime INT,
    	@endTime INT,
    	@subscribers VARCHAR(MAX),
    	@exchanges VARCHAR(MAX) = '1:',
    	@beginDateValue int, 
    	@endDateValue int
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	DECLARE @s INT
    	SET @s = @beginDateValue
    	DECLARE @e INT
    	SET @e = @endDateValue
    	print @s
    	print @e
    	
    	DECLARE @exch TABLE(Item Varchar(50))	
    	INSERT INTO @exch
    	SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
    
    
    	DECLARE @subs TABLE(Item Varchar(19))
    	INSERT INTO @subs
    	SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
    
    	SELECT [id]
    		  ,[Abandon]
    		  ,[Bcap]
    		  ,[BlId]
    		  ,[CallChg]
    		  ,[CallIdentifier]
    		  ,[ChgInfo]
    		  ,[ClId]
    		  ,[CustNo]
    		  ,[Digits]
    		  ,[DigitType]
    		  ,[Dnis1]
    		  ,[Dnis2]
    		  ,[Duration]
    		  ,[FgDani]
    		  ,[HoundredHourDuration]
    		  ,[Name]
    		  ,[NameId]
    		  ,[Npi]
    		  ,[OrigAuxId]
    		  ,[OrigId]
    		  ,[OrigMin]
    		  ,[Origten0]
    		  ,[RecNo]
    		  ,[RecType]
    		  ,[Redir]
    		  ,[TerId]
    		  ,[TermAuxId]
    		  ,[TermMin]
    		  ,[Termten0]
    		  ,[Timestamp]
    		  ,[Ton]
    		  ,[Tta]
    		  ,[Twt]
    	      ,[Level]
    	FROM 
    		[dbo].[Cdr] AS C
    	WHERE
    		(C.[DateValue] BETWEEN @s AND @e)
    	AND
    		(C.[TimeValue] BETWEEN @beginTime AND @endTime)
    	AND
    		EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
    	AND
    	    (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
    	
    END
    Fast
    Code:
    ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
    	@beginDate DATETIME = null,
    	@endDate DATETIME = null,
    	@beginTime INT,
    	@endTime INT,
    	@subscribers VARCHAR(MAX),
    	@exchanges VARCHAR(MAX) = '1:',
    	@beginDateValue int, 
    	@endDateValue int
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	DECLARE @exch TABLE(Item Varchar(50))	
    	INSERT INTO @exch
    	SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
    
    
    	DECLARE @subs TABLE(Item Varchar(19))
    	INSERT INTO @subs
    	SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
    
    	SELECT [id]
    		  ,[Abandon]
    		  ,[Bcap]
    		  ,[BlId]
    		  ,[CallChg]
    		  ,[CallIdentifier]
    		  ,[ChgInfo]
    		  ,[ClId]
    		  ,[CustNo]
    		  ,[Digits]
    		  ,[DigitType]
    		  ,[Dnis1]
    		  ,[Dnis2]
    		  ,[Duration]
    		  ,[FgDani]
    		  ,[HoundredHourDuration]
    		  ,[Name]
    		  ,[NameId]
    		  ,[Npi]
    		  ,[OrigAuxId]
    		  ,[OrigId]
    		  ,[OrigMin]
    		  ,[Origten0]
    		  ,[RecNo]
    		  ,[RecType]
    		  ,[Redir]
    		  ,[TerId]
    		  ,[TermAuxId]
    		  ,[TermMin]
    		  ,[Termten0]
    		  ,[Timestamp]
    		  ,[Ton]
    		  ,[Tta]
    		  ,[Twt]
    	      ,[Level]
    	FROM 
    		[dbo].[Cdr] AS C
    	WHERE
    		(C.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
    	AND
    		(C.[TimeValue] BETWEEN @beginTime AND @endTime)
    	AND
    		EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
    	AND
    	    (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
    	
    END

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    make sure [V] is indexed and read this...

    http://blogs.msdn.com/khen1234/archi...02/424228.aspx
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    With literals, the optimizer can compare the statistics for the between range, and determine whether an index is appropriate. With variables, the optimizer may have trouble. I would bet you will see an index seek in the query plan with the literals, and a table scan in the one with the variables.

  4. #4
    Join Date
    Jan 2005
    Posts
    13
    Thanks, It a little clearer now.

    But what I want to to is to send in a DateTime/string (of datetime) and convert it to an int representation, either of these ways works
    SET @s = FLOOR(CAST(@startDate AS Float))
    or
    SET @s = CAST(DATEADD(dd, 0, DATEDIFF(dd, 0, @startDate)) AS INT)

    and the use the @s variable in my WHERE clause?

    My current solution is to send in the @s value (int rep. of the date) directly to the SP as a parameter but I would like to send a datetime insteead.

    I couldnt figure that out by reading the article/previous post.

    Thanks for your quick replies.

    And the [V] field (see previous post) is of the type int and indexed.

    /Johan

  5. #5
    Join Date
    Dec 2007
    Location
    Baltimore, MD
    Posts
    2
    wendelstam:
    Are you trying to 'cheat' the engine by converting date-time into other values? Are you exploring the power of the DB Engine or trying to fine tune performance? Or are you trying to define activity during a shift? If this will be a frequent task, then you may want to consider modifying the underlying structure of the database to reflect shifts, so you can then select on a starting/ending datetime and a shift. Of course, if you're constrained to NOT modify the structure, then you will suffer this curse (of the designer) for a long time....
    The principles make sense - to use the 'raw' values of how date-time is stored internally, but you may want to consider doing the translation into a pure binary (aka hex) value for that?
    In any event, the advice about the use of indexing previously mentioned should help, as long as the query engine can take advantage of the index. Otherwise it will just add a smidgeon of overhead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •