Parameter Speed Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parameter Speed Problem

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-06-17 : 13:41:00

I have this query here which takes about 8 seconds:

declare 
	@BranchID	varchar (10),
	@AccountID	varchar (20)
set @BranchID='BROC'
set @AccountID='%'

SELECT DISTINCT
*
FROM         
Claim C Inner JOIN        
ClaimAssist AS ca ON ca.ClaimID = c.ID 
WHERE     
 	(ca.BranchID = @BranchID or @BranchID = '%') 
	AND (@AccountID  = '%')

When I change the select to this, I get under a second:

SELECT DISTINCT
*
FROM         
Claim C Inner JOIN        
ClaimAssist AS ca ON ca.ClaimID = c.ID 
WHERE     
 	(ca.BranchID = @BranchID or @BranchID = '%') 
	AND ('%'  = '%')

Anyone any idea what gives?

I am guessing SQL Server makes up its mind what its going to do before evaluating the parameter values. Any way to get SQL Server to evaluate the parameters first, then make an Execution Plan?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 13:44:46

Have you tried to "optimized for" query hint?

E 12°55'05.63"
N 56°04'39.26"

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-17 : 13:46:09

Have a look at the query plan.
Try clearing cached plans and rerunning.
could try

SELECT DISTINCT
*
FROM
(select distinct *
fron Claim C Inner JOIN
ClaimAssist AS ca ON ca.ClaimID = c.ID
WHERE
(ca.BranchID = @BranchID or @BranchID = '%')
) a
where (@AccountID = '%')

otherwise you can use query hints

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-06-17 : 14:15:05

quote:
Originally posted by Peso

Have you tried to "optimized for" query hint?

I just tried with 'optimized for'. It didn't really help. It worked when the paramater is '%' but then slow when the parameter is anything other.

But then I found the 'option(recompile)'. This works well. So thanks for pointing me in the right direction. Appreciate it.

option(recompile)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources