Trying to avoid an cluster index scan and use best practices for application code

Question

Trying to avoid an cluster index scan and use best practices for application code

NewbieCoding

Say Hey Kid

Points: 689
More actions
August 12, 2009 at 8:23 am

#140805

CREATE PROCEDURE [dbo].[p_getCallStats]
@Col1 int = null,
@col2 datetime = NULL,
@col3 int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Statements for procedure here
SELECT[col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
FROM [dbo].
WHERE
(@col1 IS NULL OR [col1] = @col1)
AND
(@col2 IS NULL OR [col2] = @col2)
AND
(@col3 IS NULL OR [col3] = @col3)
END
The where clause is forcing a clustered index scan. I want to optimize it so that it does a seek instead.
Any help is greatly appreciated. :w00t:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

NewbieCoding (8/12/2009)
CREATE PROCEDURE [dbo].[p_getCallStats]
@Col1 int = null,
@col2 datetime = NULL,
@col3 int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Statements for procedure here
SELECT[col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
FROM [dbo].
WHERE
(@col1 IS NULL OR [col1] = @col1)
AND
(@col2 IS NULL OR [col2] = @col2)
AND
(@col3 IS NULL OR [col3] = @col3)
END
The where clause is forcing a clustered index scan. I want to optimize it so that it does a seek instead.
Any help is greatly appreciated. :w00t:

One choice whould be to write this using dynamic sql and use sp_executesql. Another choice I have seen use is this:

WHERE

[col1] = coalesce(@col1, [col1])

AND

[col2] = coalesce(@col2,[col12])

AND

[col3] = coalesce(@col3,[col3])

I don't make any promises that the above will accomplish what you are looking for.

Grady Christie SSC Veteran Points: 258 More actions · Answer 2

It look like you need either col1,col2,col3 to all equal the parameters or where

any one of col1,col2,col3 is null.

To avoid using OR, perhaps try union as such:

--//

select col1,col2,col3 from [dbo.] where @col1=@col1 and col2=@col2 and col3=@col3

union all

select col1,col2,col3 from [dbo.] where col1 is null

union all

select col1,col2,col3 from [dbo.] where col2 is null

union all

select col1,col2,col3 from [dbo.] where col3 is null

--//

Feel feel to try it out. Hope it helps. Grady Christie

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass