Performance question for begginers

Hi,
On an SQL 2000, which of the following queries will have better performance:
select * from Phones
where ContactID in (select ID From Contacts where ClientID = 14)
Or
select * from Phones
inner join Contacts on Phones.ContactID = Contacts.ID
inner join Clients on Contacts.ClientID = Clients.ID
where ClientID = 14
As far as I know the second is better, but my own tests prove otherwise...
Thanks,gwenda wrote:
> Hi,
> On an SQL 2000, which of the following queries will have better
> performance:
> select * from Phones
> where ContactID in (select ID From Contacts where ClientID = 14)
> Or
> select * from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> As far as I know the second is better, but my own tests prove
> otherwise...
> Thanks,
It depends. First no "SELECT *". It's bad practice, a performance hog, a
network hog, and makes performance tuning impossible. The columns you
select have a lot to do with the overall execution plan SQL Server uses.
In the second example, you're selecting all columns in all three tables.
Re-run the queries with the columns you really want back and report the
results. Also post your indexes, keys, and table DDL.
David Gugick
Imceda Software
www.imceda.com|||SQL Server will try to generate the most efficient execution plan possible
based on your query. Given 2 different queries that are semantically
identical, it is entirely possible that SQL Server will generate the same
efficient execution plan even though the queries are expressed differently.
You'll then get the same performance.
As David pointed out, your queries are semantically different so you're
comparing apples and oranges. The example below shows identical execution
plans with different but semantically identical queries:
CREATE TABLE Contacts
(
ID int NOT NULL
CONSTRAINT PK_Contacts PRIMARY KEY,
ClientID int NOT NULL
)
GO
CREATE INDEX Index1 ON Contacts(ClientID)
CREATE TABLE Phones
(
PhoneID int NOT NULL
CONSTRAINT PK_Phones PRIMARY KEY,
ContactID int NOT NULL
CONSTRAINT FK_Phones_Contacts FOREIGN KEY
REFERENCES Contacts(ID),
)
GO
SELECT p.*
FROM Phones p
WHERE ContactID IN (SELECT ID FROM Contacts WHERE ClientID = 14)
GO
SELECT p.*
FROM Phones p
WHERE EXISTS
(
SELECT *
FROM Contacts c
WHERE c.ID = p.ContactID AND
c.ClientID = 14
)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"gwenda" <nina@.community.nospam> wrote in message
news:5199EF86-020D-4475-92F2-E7B64EDCA2C0@.microsoft.com...
> Hi,
> On an SQL 2000, which of the following queries will have better
> performance:
> select * from Phones
> where ContactID in (select ID From Contacts where ClientID = 14)
> Or
> select * from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> As far as I know the second is better, but my own tests prove otherwise...
> Thanks,
>|||These are two totally different queries. The second involves three tables,
returning columns from all three, while the first one only returns data from
one table and only touches two tables.) If you have good indexes on the
clientId and contactId the first one will be very fast.
The second should be pretty fast, but it is totally about indexes and how
everthing is structured.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"gwenda" <nina@.community.nospam> wrote in message
news:5199EF86-020D-4475-92F2-E7B64EDCA2C0@.microsoft.com...
> Hi,
> On an SQL 2000, which of the following queries will have better
> performance:
> select * from Phones
> where ContactID in (select ID From Contacts where ClientID = 14)
> Or
> select * from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> As far as I know the second is better, but my own tests prove otherwise...
> Thanks,
>|||Hi,
Thank you all for this helpful responses and sorry for not providing all the
needed data. So:
Phones:
CREATE TABLE [Phones] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ContactID] [int] NOT NULL ,
[Type] [tinyint] NOT NULL ,
[Number] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Remarks] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Phones] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Phones_Contacts] FOREIGN KEY
(
[ContactID]
) REFERENCES [Contacts] (
[ID]
)
) ON [PRIMARY]
GO
Contacts:
CREATE TABLE [Contacts] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryCode] [tinyint] NULL ,
[Name] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[JobTitle] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Address] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Email] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[IsEmergency] [bit] NULL ,
[LastUpdated] [datetime] NULL ,
[Remarks] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[ClientID] [int] NULL ,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Contacts_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ID]
)
) ON [PRIMARY]
GO
Clients:
CREATE TABLE [Clients] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (150) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
The two "fixed" queries are:
select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
from Phones p
where ContactID in (select ID From Contacts where ClientID = 14)
OR
select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
from Phones
inner join Contacts on Phones.ContactID = Contacts.ID
inner join Clients on Contacts.ClientID = Clients.ID
where ClientID = 14
The purpose is getting the information from phones in the best performance.
Hope that will help you to help me.
Thanks again,
Gwenda|||The performance of each approach depends on the relative table size (and
number of distinct ID's that "select ID From Contacts where ClientID =
14" produces). If there are just a few distinct ID's for ClientID 14,
then the first approach could be faster. If not, then I would expect the
second approach to be faster. So it really depends. You would have to
test it for yourself.
You could also try the following approach and see how fast that is.
select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
from Phones p
where EXISTS (select 1 From Contacts where ClientID = 14 AND
ID=p.ContactID)
BTW: based on the clustered index that already exists on Contacts(ID),
this query would benefit from a nonclustered index on
Contacts(ClientID).
HTH,
Gert-Jan
gwenda wrote:
> Hi,
> Thank you all for this helpful responses and sorry for not providing all t
he
> needed data. So:
> Phones:
> CREATE TABLE [Phones] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ContactID] [int] NOT NULL ,
> [Type] [tinyint] NOT NULL ,
> [Number] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Remarks] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Phones] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Phones_Contacts] FOREIGN KEY
> (
> [ContactID]
> ) REFERENCES [Contacts] (
> [ID]
> )
> ) ON [PRIMARY]
> GO
> Contacts:
> CREATE TABLE [Contacts] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryCode] [tinyint] NULL ,
> [Name] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
> [JobTitle] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
> [Address] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Email] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
> [IsEmergency] [bit] NULL ,
> [LastUpdated] [datetime] NULL ,
> [Remarks] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [ClientID] [int] NULL ,
> CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Contacts_Clients] FOREIGN KEY
> (
> [ClientID]
> ) REFERENCES [Clients] (
> [ID]
> )
> ) ON [PRIMARY]
> GO
> Clients:
> CREATE TABLE [Clients] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (150) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Description] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> The two "fixed" queries are:
> select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
> from Phones p
> where ContactID in (select ID From Contacts where ClientID = 14)
> OR
> select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
> from Phones
> inner join Contacts on Phones.ContactID = Contacts.ID
> inner join Clients on Contacts.ClientID = Clients.ID
> where ClientID = 14
> The purpose is getting the information from phones in the best performance
.
> Hope that will help you to help me.
> Thanks again,
> Gwenda|||Thanks :)
"Gert-Jan Strik" wrote:

> The performance of each approach depends on the relative table size (and
> number of distinct ID's that "select ID From Contacts where ClientID =
> 14" produces). If there are just a few distinct ID's for ClientID 14,
> then the first approach could be faster. If not, then I would expect the
> second approach to be faster. So it really depends. You would have to
> test it for yourself.
> You could also try the following approach and see how fast that is.
> select p.ID, p.ContactID, p.Type, p.Number, p.Remarks
> from Phones p
> where EXISTS (select 1 From Contacts where ClientID = 14 AND
> ID=p.ContactID)
> BTW: based on the clustered index that already exists on Contacts(ID),
> this query would benefit from a nonclustered index on
> Contacts(ClientID).
> HTH,
> Gert-Jan
> gwenda wrote:
>