COALESCE in a WHERE clause

Hi
We have recently been experiencing a performance problem that appears to
involve COALESCE in a WHERE clause. It started to happen recently when one
of the affected tables grew a bit. Given the following tables (each is
about 200,000 rows and 10 or 12 columns) and view:
CREATE TABLE Table1 (
keycol1 VARCHAR(10) NOT NULL PRIMARY KEY
,col1 VARCHAR(5) NOT NULL
...
);
CREATE TABLE Table2 (
keycol1 VARCHAR(10) NOT NULL
REFERENCES Table1 (keycol1)
ON UPDATE CASCADE
ON DELETE CASCADE
,keycol2 VARCHAR(6) NOT NULL
,col2 VARCHAR(20) NOT NULL
...
,PRIMARY KEY (keycol1, keycol2)
);
CREATE VIEW View1
AS
SELECT T2.keycol1, T2.keycol2, T1.col1, T2.col2
FROM Table2 AS T2
JOIN Table1 AS T1 ON T1.keycol1 = T2.keycol2
The following procedure (I removed the CREATE PROCEDURE for simplicity) is
almost instantaneous, with subsecond response time.
DECLARE @.param AS VARCHAR(10);
SET @.param = 'abcdefgh';
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE keycol1 = @.param;
However, when using a COALESCE in the WHERE (because a NULL is possible)
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE keycol1 = COALESCE(@.param, keycol1);
All of the sudden the query starts to crawl (average 30 seconds or more).
It seems to be centered on the check for NULL in the parameter, because if I
change it to:
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE @.param IS NULL;
It is still slow. Any ideas?
JoeThis is because when you use ANY function operating on a column, in a Where
Clause, the Query Processor can no longer use an index for the query. So
then it has t oread the entire table, to run the Coalesce(@.Param, keycol1)
function on every row. The index does not have the value of
COALESCE(@.param, keycol1) in it, it just has teh value of keyCol1 in it...
Change the query to
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE @.Param Is Null Or keycol1 = @.param
And it should be able to use the index again...
"J. M. De Moor" wrote:

> Hi
> We have recently been experiencing a performance problem that appears to
> involve COALESCE in a WHERE clause. It started to happen recently when on
e
> of the affected tables grew a bit. Given the following tables (each is
> about 200,000 rows and 10 or 12 columns) and view:
> CREATE TABLE Table1 (
> keycol1 VARCHAR(10) NOT NULL PRIMARY KEY
> ,col1 VARCHAR(5) NOT NULL
> ...
> );
> CREATE TABLE Table2 (
> keycol1 VARCHAR(10) NOT NULL
> REFERENCES Table1 (keycol1)
> ON UPDATE CASCADE
> ON DELETE CASCADE
> ,keycol2 VARCHAR(6) NOT NULL
> ,col2 VARCHAR(20) NOT NULL
> ...
> ,PRIMARY KEY (keycol1, keycol2)
> );
> CREATE VIEW View1
> AS
> SELECT T2.keycol1, T2.keycol2, T1.col1, T2.col2
> FROM Table2 AS T2
> JOIN Table1 AS T1 ON T1.keycol1 = T2.keycol2
> The following procedure (I removed the CREATE PROCEDURE for simplicity) is
> almost instantaneous, with subsecond response time.
> DECLARE @.param AS VARCHAR(10);
> SET @.param = 'abcdefgh';
> SELECT keycol1, keycol2, col1, col2 FROM View1
> WHERE keycol1 = @.param;
> However, when using a COALESCE in the WHERE (because a NULL is possible)
> SELECT keycol1, keycol2, col1, col2 FROM View1
> WHERE keycol1 = COALESCE(@.param, keycol1);
> All of the sudden the query starts to crawl (average 30 seconds or more).
> It seems to be centered on the check for NULL in the parameter, because if
I
> change it to:
> SELECT keycol1, keycol2, col1, col2 FROM View1
> WHERE @.param IS NULL;
> It is still slow. Any ideas?
> Joe
>
>|||Did you get a chance to go through some alternatives suggested at:
http://www.sommarskog.se/dyn-search.html
Anith|||Anith
Terrific article...especially the bag of tricks in the end. Thanks.
Joe