Best way to index my simple join table:

Hi,
I was hoping that someone would give me advice on the best way to index
my tables, for this situtation.
I have two tables, tblVisit and tblTechnician.
tblVisit has this primary key
VisitID int Identity(1,1)
tblTechnician has this primary key:
TechnicianID int Identity(1,1)
They are joined by a third table tblVisitTechnician which links the
two, enabling a visit to have more than one technician allocated:
tblVisitTechnician is structured:
VisitTechnicianID int Identity(1,1) - PrimaryKey
VisitRef int
TechnicianRef int
and both VisitRef and TechnicianRef are both setup as foriegn keys.
The problem I have is that any query I run which uses this join tbale
seems to get slowed down significantly.
What is the best to index these tables, to get the best performance?
Thanks
Alex StevensThat not that much information for suggesting something to you... What does
the query plan show you ?
To extract the query plan for a query, issue the follwing statement in QA:
SET SHOWPLAN_TEXT ON
<Youqruery>
SET SHOWPLAN_TEXT OFF
Like that:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select * from Orders
GO
SET SHOWPLAN_ALL OFF
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Chubby Arse" <alexstevens@.gcc.co.uk> schrieb im Newsbeitrag
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||I don't think you need the VisitTechnicianID column, (VisitRef,
TechnicianRef) is the real primary key of this table, and I doubt that
adding an identity column to a table like this adds any value. As you
probably query on both Visit and Technician regularly, I would do it as
follows (using my preferred naming conventions):
CREATE TABLE VisitsTechnicians (
VisitID int NOT NULL,
TechnicianID int NOT NULL,
CONSTRAINT PK_VisitsTechnicians PRIMARY KEY (VisitID, TechnicianID),
CONSTRAINT FK_VisitsTechnicians__tblVisit
FOREIGN KEY (VisitID )
REFEENCES tblVisit (VisitID ),
CONSTRAINT FK_VisitsTechnicians__tblTechnician
FOREIGN KEY (TechnicianID )
REFEENCES tblVisit (TechnicianID )
)
CREATE NONCLUSTERED INDEX ix_VisitsTechnicians__TechnicianID__VisitID
ON VisitsTechnicians (VisitID , TechnicianID )
--
Jacco Schalkwijk
SQL Server MVP
"Chubby Arse" <alexstevens@.gcc.co.uk> wrote in message
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||It's *probably* slow bevause there's no index on the FKs in the
tblVisitTechnician table.
Use the two FKs (VsiitRef, TechnicianRef) as composite Primary Key (You
probably don' need VisitTechnicianID).
1/. This will constrain table from having multiple records for same visit
and same Technician (Can one technician be included on same visit twice?
2. It will also improve query performance when querying on VisitRef.
3. If yo also query tblVisitTechnician table on TechnicianRef alone, add
anothe non-clustered index on TechnicianRef aline
"Chubby Arse" wrote:
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>