IF ,or chained IF...ELSE IF | Which is faster? 3

[Qmoto]

Here's an snippet of the code I'm using... which do you think would be faster - or does it really make a difference?

IF (@FORMVAL NOT IN (SELECT KanbanID FROM KBDATA))
	BEGIN	
	DECLARE	@ReturnVal decimal(3,0)
	SET	@ReturnVal = '-1' 
	RETURN	@ReturnVal
	END

IF (SELECT KBCLR FROM KBDATA WHERE KanbanID = @FORMVAL) NOT IN ('OR')
	BEGIN
	SET	@ReturnVal = '-3' 
	RETURN	@ReturnVal
	END	

IF (SELECT STATUS FROM KBDATA WHERE KanbanID = @FORMVAL) = '1'
	BEGIN
	SET	@ReturnVal = '75' 
	RETURN	@ReturnVal
	END

[COLOR=green]Code currently in use...[/color]

IF (@FORMVAL NOT IN (SELECT KanbanID FROM KBDATA))
	BEGIN	
	DECLARE	@ReturnVal decimal(3,0)
	SET	@ReturnVal = '-1' 
	RETURN	@ReturnVal
	END

[COLOR=blue]ELSE IF[/color] (SELECT KBCLR FROM KBDATA WHERE KanbanID = @FORMVAL) NOT IN ('OR')
	BEGIN
	SET	@ReturnVal = '-3' 
	RETURN	@ReturnVal
	END	

[COLOR=blue]ELSE IF[/color] (SELECT STATUS FROM KBDATA WHERE KanbanID = @FORMVAL) = '1'
	BEGIN
	SET	@ReturnVal = '75' 
	RETURN	@ReturnVal
	END

The NOT IN statement scans the entire table to see if the KanbanID is actually in the table, and I'm guessing this is where the largest amount of time is used up.

Is there a quicker way to do this, or is this fairly efficient?

Note: The KBDATA table only has about 8000 records, but it grows almost daily, usually only by a couple hundred or so, but I have no idea how much it will grow before we have all the Kanban's we need.... *shrug*

What are your thoughts?

 

[SQLSister]

not in is almost always a bad choice performance wise
try
(select count(*) FROM KBDATA WHERE KanbanID = @FORMVAL)>0

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[vbkris]

or:

EXISTS(select * FROM KBDATA WHERE KanbanID = @FORMVAL)>0


Known is handfull, Unknown is worldfull

 

[Qmoto]

Excellent - Thanks for the suggestions!

I'm goin to give Both of you stars for the help

Any thoughts on chained IF...ELSE IF statments vs standalone IF statements? Is there a speed difference?

 

[SQLDenis]

BTW if you skip the first condition you will get an error since @ReturnVal is only declared in the first if
declare it outside the if blocks


--Only in the first IF
DECLARE @ReturnVal decimal(3,0)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mhoyt]

Would it make sense to use a CASE statement instead?

 

[SQLDenis]

what about this?

1 select instead of (potentially) 3

DECLARE @v1 VARCHAR(50),@v2 VARCHAR(50),@v3 VARCHAR(50)
DECLARE    @ReturnVal decimal(3,0)

SELECT @v1 =KanbanID,@v2 =KBCLR,@v3 =STATUS
FROM KBDATA 
WHERE KanbanID = @FORMVAL
AND KanbanID IS NOT NULL




IF @v1 IS NULL
BEGIN
SET    @ReturnVal = '-1' 
    RETURN    @ReturnVal
END

IF @v2 IS NULL
BEGIN
    SET    @ReturnVal = '-3' 
    RETURN    @ReturnVal
END  

IF @v3 IS NULL
  BEGIN
    SET    @ReturnVal = '75' 
    RETURN    @ReturnVal
END

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Forgot something

DECLARE @v1 VARCHAR(50),@v2 VARCHAR(50),@v3 VARCHAR(50)
DECLARE    @ReturnVal decimal(3,0)

SELECT @v1 =KanbanID,@v2 =KBCLR,@v3 =STATUS
FROM KBDATA 
WHERE KanbanID = @FORMVAL
AND KanbanID IS NOT NULL




IF @v1 IS NULL
BEGIN
SET    @ReturnVal = '-1' 
    RETURN    @ReturnVal
END

IF @v2 <> 'OR'
BEGIN
    SET    @ReturnVal = '-3' 
    RETURN    @ReturnVal
END  

IF @v3 = '1'
  BEGIN
    SET    @ReturnVal = '75' 
    RETURN    @ReturnVal
END

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Qmoto]

--> SQLDenis

BTW if you skip the first condition you will get an error since @ReturnVal is only declared in the first if
declare it outside the if blocks

That's what I thought would happen at first too. However, the code works that way becuase no matter what the first IF condition gets evaluated, which causes the variable to be decalred... That was an unintentional side effect, but it works none the less

--> mhoyt

Would it make sense to use a CASE statement instead?

In this situation, I'm not sure that a case statment would do the trick, although it might. I just figured since there is actually a fair amount of code inside and beyond what I've shown that the if statment would work better... I'm not sure, I may play with the case statement idea and see where it leads - thanks for the suggestion!

--> SQLDenis

what about this?

1 select instead of (potentially) 3

I'm not sure why this idea didn't occur to me - I could just as easily select all three pieces of information at once. I think this is probably the best solution!

Thanks for the replies everyone!