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

CLustering in SQL server 2000

Hi
We have a DELL PVC 2000 that we used for testing Linux clustering Oracle.
Now I wanted to test it for SQL server clustering.
My question is what do I need on OS for SQL server 2000 cluster to work and
can you provide me useful URL for the same.
Thanks
MangeshHi
Windows Server Enterprise Edition
http://www.microsoft.com/windowsser...crosoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:B03B4EE7-4DD8-44D9-B9AC-390725638915@.microsoft.com...
> Hi
> We have a DELL PVC 2000 that we used for testing Linux clustering Oracle.
> Now I wanted to test it for SQL server clustering.
> My question is what do I need on OS for SQL server 2000 cluster to work
and
> can you provide me useful URL for the same.
> Thanks
> Mangesh

CLustering in SQL server 2000

Hi
We have a DELL PVC 2000 that we used for testing Linux clustering Oracle.
Now I wanted to test it for SQL server clustering.
My question is what do I need on OS for SQL server 2000 cluster to work and
can you provide me useful URL for the same.
Thanks
Mangesh
Hi
Windows Server Enterprise Edition
http://www.microsoft.com/windowsserv...g/default.mspx
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:B03B4EE7-4DD8-44D9-B9AC-390725638915@.microsoft.com...
> Hi
> We have a DELL PVC 2000 that we used for testing Linux clustering Oracle.
> Now I wanted to test it for SQL server clustering.
> My question is what do I need on OS for SQL server 2000 cluster to work
and
> can you provide me useful URL for the same.
> Thanks
> Mangesh

clustered index rebuilds and performance hit

Hi
We're about to implement some clustered index changes on tables with 10s of
millions of rows.
I would like to know what the implications will be on:
SELECT
INSERT
UPDATE
DELETE
during the duration of the index changes/rebuilds. We have to plan for this
and want our users to know exactly what to expect i.e. what they will and
will not be able to do in the database.
thanks..u
--
-- cranfield, DBAYou mention both "change" and rebuild. Which is it? Removing one clustered i
ndex and adding it back
on some other column? Or defragmenting the clustered index?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> Hi
> We're about to implement some clustered index changes on tables with 10s o
f
> millions of rows.
> I would like to know what the implications will be on:
> SELECT
> INSERT
> UPDATE
> DELETE
> during the duration of the index changes/rebuilds. We have to plan for th
is
> and want our users to know exactly what to expect i.e. what they will and
> will not be able to do in the database.
> thanks..u
> --
> -- cranfield, DBA|||if you drop and recreate OR if you use DBCC DBREINDEX, then the table will
be exclusively locked for the duration.
IF you just defrag it via DBCC IndexDefrag, then there will only be minor
impact.
Greg Jackson
PDX, Oregon|||Hi Tibor
We are removing the clustered index and creating a new one on a different ke
y.
We will be creating the old clustered index as non-clustered. These changes
are required due to a logic change in our application.
thanks for the reply.
alan cranfield
"Tibor Karaszi" wrote:

> You mention both "change" and rebuild. Which is it? Removing one clustered
index and adding it back
> on some other column? Or defragmenting the clustered index?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
>
>|||Any time you drop, create or re-create a clustered index the table will be
unavailable for the duration of the event. Your best bet is to follow these
steps:
1. stop all access to this table
2. drop all the nonclustered indexes
3. drop the clustered index
4. Create the new clustered index
5. Recreate the nonclustered indexes
If you just drop the clustered index first it will recreate all the
nonclustered indexes. Then when you create a new clustered index it will
rebuild all the nonclustered indexes again.
Andrew J. Kelly SQL MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:18AB918A-44F4-4B91-8F40-1CFA8B5DEC40@.microsoft.com...[vbcol=seagreen]
> Hi Tibor
> We are removing the clustered index and creating a new one on a different
> key.
> We will be creating the old clustered index as non-clustered. These
> changes
> are required due to a logic change in our application.
> thanks for the reply.
> alan cranfield
> "Tibor Karaszi" wrote:
>

clustered (A/P) servers behind firewall problem

Hi!
We have clustered servers behind firewall. The active
node sends back the package but the virtual server was
the addressed. The firewall rejects the package. What can
be done? Is it by design of MSCS? You have to allow all
nodes IP (even though only 1 is active) at the firewall?
Thanks,
kob ukiWhat "package" are you referring to ?
DTS ?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Sorry for my wording. ) I meant IP packets.

>--Original Message--
>What "package" are you referring to ?
>DTS ?
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Yes. I believe that the source IP may be the node of the machine in
control.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.