ANSI NULLS performance impact

Could someone explain why having the ANSI NULLS database option off causes
the following piece of SQL to run substantially slower. When the option is
on, in my applications ODBC settings, the query will return in less than a
second; however if the option is off then the query will take 28 seconds to
execute.
The execution plans are identical in both cases.
The application I am running is connecting via ODBC to SQL Server2000
database.
I was interested as to why this option had such a big impact on performance
- could someone explain why?
SELECT
instrument.instrument_id,
instrument.name
FROM
instrument
WHERE
(instrument.instrument_id IN ('4056719', '4072168', '4072919' .... { about
700 items in this list} ... ))
AND ((instrument.instrument_subtype_customer in ('FB', 'OB', 'SB', 'CO',
...{more items } ... 'SZ', 'WZ', 'PO', 'FS'))
and ((instrument.exchange_customer in ('EA1', 'EB1', 'LF1', 'FC1',
'EC1',...{more items } ... 'NA1', 'NAL', 'NAI')))
and not ((instrument.instrument_subtype_customer = 'DC')))
Execution plan
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument]))
|--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000]))
|--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000]))
| |--Index
S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[pk_instrument_instrument_id]),
SEEK:([instrument].[instrument_id] >= '4000998' AND
[instrument].[instrument_id] <= '4000998' OR [instrument].[instrument_id] >=
'4002121' AND
| |--Index
S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_exchange_customer]),
SEEK:([instrument].[exchange_customer] >= 'EA1' AND
[instrument].[exchange_customer] <= 'EA1' OR [instrument].[exchange_customer]
>= 'EB1'
|--Index
S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_subtype_customer]),
SEEK:([instrument].[instrument_subtype_customer] >= 'CO' AND
[instrument].[instrument_subtype_customer] <= 'CO' OR
[instrument].[instrument_subtWeird stuff can happen when setting ansi_nulls to off.
A NULL Puzzle
http://groups-beta.google.com/group...aca91a7912bdc76
AMB
"dave" wrote:

> Could someone explain why having the ANSI NULLS database option off causes
> the following piece of SQL to run substantially slower. When the option is
> on, in my applications ODBC settings, the query will return in less than a
> second; however if the option is off then the query will take 28 seconds t
o
> execute.
> The execution plans are identical in both cases.
> The application I am running is connecting via ODBC to SQL Server2000
> database.
> I was interested as to why this option had such a big impact on performanc
e
> - could someone explain why?
>
> SELECT
> instrument.instrument_id,
> instrument.name
> FROM
> instrument
> WHERE
> (instrument.instrument_id IN ('4056719', '4072168', '4072919' .... { abo
ut
> 700 items in this list} ... ))
> AND ((instrument.instrument_subtype_customer in ('FB', 'OB', 'SB', 'CO',
> ...{more items } ... 'SZ', 'WZ', 'PO', 'FS'))
> and ((instrument.exchange_customer in ('EA1', 'EB1', 'LF1', 'FC1',
> 'EC1',...{more items } ... 'NA1', 'NAL', 'NAI')))
> and not ((instrument.instrument_subtype_customer = 'DC')))
>
> Execution plan
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument]))
> |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
> RESIDUAL:([Bmk1000]=[Bmk1000]))
> |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
> RESIDUAL:([Bmk1000]=[Bmk1000]))
> | |--Index
> S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[pk_instrument_instrument_id]),
> SEEK:([instrument].[instrument_id] >= '4000998' AND
> [instrument].[instrument_id] <= '4000998' OR [instrument].[instrument_id] >=
> '4002121' AND
> | |--Index
> S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_exchange_customer]),
> SEEK:([instrument].[exchange_customer] >= 'EA1' AND
> [instrument].[exchange_customer] <= 'EA1' OR [instrument].[exchange_customer]
> |--Index
> S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_subtype_customer]),
> SEEK:([instrument].[instrument_subtype_customer] >= 'CO' AND
> [instrument].[instrument_subtype_customer] <= 'CO' OR
> [instrument].[instrument_subt
>