Query optimizer question

Hi,
we have a very strange behavior of SQL Server that we try to understand.
We have the following Select statement:
SELECT DISTINCT TOP 100000 Customer.id
FROM Customer LEFT JOIN CustomerAddress CA ON
Customer.idDefaultAddress=CA.id
WHERE 1=1
and Customer.id IN (
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
)
When we run this select we get the desired results from the database.
However if we run only the Select that is in the Where clause, that is:
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
we get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'DCDB' to a column of data
type int.
which is somehow normal as the column ValueAttribute is a varchar column
.
What I do not understand is why in the first situation it works.
Obviously looking at the execution plan you can see that it is made
different in the 2 situation but it's SQL that decides upon what
execution plan it should chose.
Does anyone have any explanation for this behavior or could you point me
to some documentation regarding the way the Query Optimizer works?
Thanks,
Florian
Here is the tables structure: CREATE TABLE [dbo].[Customer] (
[id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[idDefaultShippingAddress] [int] NULL ,
[idDefaultAddress] [int] NULL ,
[idPrincipalContact] [uniqueidentifier] NULL ,
[idSalesPerson] [uniqueidentifier] NULL ,
[idCustomerParent] [uniqueidentifier] NULL ,
[Code] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WebSite] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[InternalNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[DotNetMessenger] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[EntCreationDate] [datetime] NULL ,
[NumberEmployee] [int] NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[BankName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[BankProvinceState] [int] NULL ,
[BankAccountNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[BankRoutingNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[CreditCardType] [int] NULL ,
[CreditCardNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[CreditCardExpMonth] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditCardExpYear] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[idDefaultLanguage] [int] NOT NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idTaxCode] [int] NOT NULL ,
[idCurrency] [int] NOT NULL ,
[idTimeZone] [int] NOT NULL ,
[Address1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Address2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ProvinceState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
,
[ZipPostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[InternalNotes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[ShippingInstruction] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[ISO3166GEO] [int] NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttribute] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idAttribute] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttributeDescription] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomerAttribute] [int] NOT NULL ,
[idCulture] [int] NULL ,
[ValueAttribute] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [PK_AddressCustomer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttribute] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttributeDescription] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [DF_Customer_id] DEFAULT (newid()) FOR [id],
CONSTRAINT [DF_Customer_Name] DEFAULT ('') FOR [Name],
CONSTRAINT [DF_Customer_Phone] DEFAULT ('') FOR [Phone],
CONSTRAINT [DF_Customer_Fax] DEFAULT ('') FOR [Fax],
CONSTRAINT [DF_Customer_WebSite] DEFAULT ('') FOR [WebSite],
CONSTRAINT [DF_Customer_Email] DEFAULT ('') FOR [Email],
CONSTRAINT [DF_Customer_Notes] DEFAULT ('') FOR [InternalNotes],
CONSTRAINT [DF_Customer_DotNetMessenger] DEFAULT ('') FOR
[DotNetMessenger],
CONSTRAINT [DF_Customer_NumberEmployee] DEFAULT (0) FOR
[NumberEmployee],
CONSTRAINT [DF_Customer_BankName] DEFAULT ('') FOR [BankName],
CONSTRAINT [DF_Customer_BankCity] DEFAULT ('') FOR [BankCity],
CONSTRAINT [DF_Customer_BankAccountNumber] DEFAULT ('') FOR
[BankAccountNumber],
CONSTRAINT [DF_Customer_BankRoutingNumber] DEFAULT ('') FOR
[BankRoutingNumber],
CONSTRAINT [DF_Customer_CreditCardNumber] DEFAULT ('') FOR
[CreditCardNumber],
CONSTRAINT [DF_Customer_idDefaultLanguage] DEFAULT (1) FOR
[idDefaultLanguage]
GO
CREATE INDEX & #91;IX_CustomeridDefaultShippingAddress]
ON
[dbo].[Customer]([idDefaultShippingAddress]) WITH FILLFACTOR =
90 ON
[PRIMARY]
GO
CREATE INDEX [IX_CustomerDefaultAddress] ON
[dbo].[Customer]([idDefaultAddress]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [IX_CustomeridPrincipalContact] ON
[dbo].[Customer]([idPrincipalContact]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Customer_Code] ON [dbo].[Customer](
1;Code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Customer] ON [dbo].[Customer]([id]) WITH
FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [DF_CustomerAddress_Address1] DEFAULT ('') FOR [Address1]
,
CONSTRAINT [DF_AddressCustomer_Address2] DEFAULT ('') FOR [Address2]
,
CONSTRAINT [DF_CustomerAddress_City] DEFAULT ('') FOR [City],
CONSTRAINT [DF_CustomerAddress_ProvinceState] DEFAULT ('') FOR
[ProvinceState],
CONSTRAINT [DF_CustomerAddress_Country] DEFAULT ('') FOR [Country],
CONSTRAINT [DF_CustomerAddress_ZipPostalCode] DEFAULT ('') FOR
[ZipPostalCode],
CONSTRAINT [DF_CustomerAddress_InternalNotes] DEFAULT ('') FOR
[InternalNotes],
CONSTRAINT [DF_CustomerAddress_Description] DEFAULT ('') FOR
[Description],
CONSTRAINT & #91;DF_CustomerAddress_ShippingInstructi
on] DEFAULT ('') FOR
[ShippingInstruction]
GO
CREATE INDEX [IX_CustomerAddressidCustomer] ON
[dbo].[CustomerAddress]([idCustomer]) WITH FILLFACTOR = 90 ON &
#91;PRIMARY]
GO
CREATE INDEX [IX_CustomerAddressTaxCode] ON
[dbo].[CustomerAddress]([idTaxCode]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [I_CustomerAddress] ON
[dbo].[CustomerAddress]([Address1], [Address2], [City])
WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [U_CustomerAttribute] UNIQUE NONCLUSTERED
(
[idCustomer],
[idAttribute]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT & #91;DF_CustomerAttributeDescription_Valu
eAttribute] DEFAULT
('') FOR [ValueAttribute]
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]', N'[Customer].[
;Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]', N'[Customer].
1;Status]'
GO
setuser
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]',
N'[CustomerAddress].[Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]',
N'[CustomerAddress].[Status]'
GO
setuser
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [FK_Customer_Culture] FOREIGN KEY
(
[idDefaultLanguage]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress2] FOREIGN KEY
(
[idDefaultShippingAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress3] FOREIGN KEY
(
[idDefaultAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttribute] ADD
CONSTRAINT [FK_CustomerAttribute_Attribute] FOREIGN KEY
(
[idAttribute]
) REFERENCES [dbo].[Attribute] (
[id]
),
CONSTRAINT [FK_CustomerAttribute_Customer] FOREIGN KEY
(
[idCustomer]
) REFERENCES [dbo].[Customer] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] ADD
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cult
ure] FOREIGN KEY
(
[idCulture]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cust
omerAttribute] FOREIGN
KEY
(
[idCustomerAttribute]
) REFERENCES [dbo].[CustomerAttribute] (
[id]
) ON DELETE CASCADE
GOOn Mon, 05 Apr 2004 21:46:46 -0500, Florian Ion wrote:

>We have the following Select statement:
>SELECT DISTINCT TOP 100000 Customer.id
>FROM Customer LEFT JOIN CustomerAddress CA ON
>Customer.idDefaultAddress=CA.id
>WHERE 1=1
> and Customer.id IN (
> select idCustomer
> from CustomerAttribute left JOIN CustomerAttributeDescription ON
>CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
> where idattribute = 13844 AND ValueAttribute = 6
> )
Why are you using left join in the subquery? The test for
ValueAttribute = '6' (what you should use for varchar data - or use
CAST (ValueAttribute AS int) if you're not sure the '6' is formatted
consistently) will discard any rows that the left join included, so
you might just as well change it to an inner join. Good chance that
this will improve perfoprmance on this query!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query optimization question...

I'm trying to optimize some queries on an existing system, and I'm
noticing some odd behavior. I'm performing a join between several
tables, the final table being joined by the optimizer has a clustered
index on the field that it is using to join to the rest of the query,
but for some reason SQL Server doesn't seem to actually use this index
(it's doing an index scan instead of an index seek). Is there some
reason why SQL Server would not use a valid Clustered Index? I've
dropped and readded the index, but that doesn't seem to help. I don't
know if it would be relevant, but the tables I'm working on are fairly
fat (2 to 7K bytes/row).

This is happening for several tables. I've been able to get around it
for some of the tables by creating a non-clustered index on all the
fields that are being queried so that the leaf pages don't need to be
loaded, but this isn't a valid solution for all of the tables I'm
struggling with.

Any ideas? (and no, they aren't willing to redesign any of the
tables)."Mathew Relick" <ticars@.yahoo.com> wrote in message
news:dd84d8a7.0311181017.39d1c69@.posting.google.co m...
> I'm trying to optimize some queries on an existing system, and I'm
> noticing some odd behavior. I'm performing a join between several
> tables, the final table being joined by the optimizer has a clustered
> index on the field that it is using to join to the rest of the query,
> but for some reason SQL Server doesn't seem to actually use this index
> (it's doing an index scan instead of an index seek). Is there some
> reason why SQL Server would not use a valid Clustered Index? I've
> dropped and readded the index, but that doesn't seem to help. I don't

This can happen if your table statistics are out of date. If SQL Server
determines that the join will require more than x % of the table to be
retrieved, then doing a clustered index scan may be faster than doing a
clustered index seek (faster because an index scan can read the rows in a
page sequentially, whereas an index seek has to traverse the B-tree
structure. Sequential read is faster because you cut down on seek time, and
also because you may be able to read more than one page in a single I/O
operation, since the data is sequential.) Try updating statistics and see
if it helps:

UPDATE STATISTICS <table_name> WITH FULLSCAN
GO

> know if it would be relevant, but the tables I'm working on are fairly
> fat (2 to 7K bytes/row).
> This is happening for several tables. I've been able to get around it
> for some of the tables by creating a non-clustered index on all the
> fields that are being queried so that the leaf pages don't need to be
> loaded, but this isn't a valid solution for all of the tables I'm
> struggling with.

This is because when you create a new index, new statistics are generated
for that index, so you have the most up-to-date statistics with your new
index. Same if you rebuild your existing indexes.

HTH,
Dave

>
> Any ideas? (and no, they aren't willing to redesign any of the
> tables).|||"Dave Hau" <nospam_dave_nospam_123@.nospam_netscape_nospam.net_ nospam> wrote
in message news:gCvub.33788$yj4.5497@.newssvr27.news.prodigy.c om...
> "Mathew Relick" <ticars@.yahoo.com> wrote in message
> news:dd84d8a7.0311181017.39d1c69@.posting.google.co m...
> > I'm trying to optimize some queries on an existing system, and I'm
> > noticing some odd behavior. I'm performing a join between several
> > tables, the final table being joined by the optimizer has a clustered
> > index on the field that it is using to join to the rest of the query,
> > but for some reason SQL Server doesn't seem to actually use this index
> > (it's doing an index scan instead of an index seek). Is there some
> > reason why SQL Server would not use a valid Clustered Index? I've
> > dropped and readded the index, but that doesn't seem to help. I don't
> This can happen if your table statistics are out of date. If SQL Server
> determines that the join will require more than x % of the table to be
> retrieved, then doing a clustered index scan may be faster than doing a
> clustered index seek (faster because an index scan can read the rows in a
> page sequentially, whereas an index seek has to traverse the B-tree
> structure. Sequential read is faster because you cut down on seek time,
and
> also because you may be able to read more than one page in a single I/O
> operation, since the data is sequential.) Try updating statistics and see
> if it helps:
> UPDATE STATISTICS <table_name> WITH FULLSCAN
> GO
> > know if it would be relevant, but the tables I'm working on are fairly
> > fat (2 to 7K bytes/row).
> > This is happening for several tables. I've been able to get around it
> > for some of the tables by creating a non-clustered index on all the
> > fields that are being queried so that the leaf pages don't need to be
> > loaded, but this isn't a valid solution for all of the tables I'm
> > struggling with.
> This is because when you create a new index, new statistics are generated
> for that index, so you have the most up-to-date statistics with your new
> index. Same if you rebuild your existing indexes.

I'm going to chime in because I think Dave has some good points here.
However, as I understand it, the original poster did rebuild the clustered
index, so there may be more to this problem than meets the eye.

One other thing that can happen is that the optimizer decides it's faster to
do a scan instead of a seek. This is particularly true if the result it
expects to return is a large percentage of the index. (i.e. if you have 100
rows and will return 80).

I'll be honest, I'm not sure exactly how this applies with a clustered
index.

> HTH,
> Dave
> > Any ideas? (and no, they aren't willing to redesign any of the
> > tables).