Indexing - Need a susgestion for my approach.

Hi All,
I have a situation which I think could be resolved with proper indexing, but
I need some advice on it.
I have a table called tblVisit which has an int identity field [VisitID] and
contains 329611 records.
I have a table called tblTechnician which has an int indentity field
[TechnicianID] and contains 200 records
I have a table which marries up the two tables above called
tblVisitTechnician which has:
VisitTechncianID int Identity field (Primary Key)
VisitRef int - Foriegn Key for tblVisit
TechnicianRef int - Foreign Key for tblTechnician
and has 329416 records - This allows me to have a visit which has more than
one technician.
It has ForeignKey constraints for the two reference fields, and Primary Key
Index (Clustered) on the VisitTechnicianID field, and a normal Index on the
VisitRef field.
Queries that use these tables, tend to take a little while to run. How do I
use Indexes to optimise this type of table relationship?
I suspect that the Clustered index on the Primary key is wrong, but can some
of you Guru's out there lend me your considerable knowledge.
Thanks
Alex
To add:
To give you an idea of speed, I ran this query which returns 329416 records
and took three minutes to run:
SELECT *
FROM tblVisit
JOIN tblVisitTechnician ON VisitID = VisitRef
JOIN tblTechnician ON tblVisitTechnician.TechnicianRef = TechnicianID
The number of returned records figures OK, as it is the same as the number
of records in the tblVisitTechnician table - this is what I would expect.
Thanks
Alex
"Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
news:eupQ5tDvEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have a situation which I think could be resolved with proper indexing,
but
> I need some advice on it.
> I have a table called tblVisit which has an int identity field [VisitID]
and
> contains 329611 records.
> I have a table called tblTechnician which has an int indentity field
> [TechnicianID] and contains 200 records
> I have a table which marries up the two tables above called
> tblVisitTechnician which has:
> VisitTechncianID int Identity field (Primary Key)
> VisitRef int - Foriegn Key for tblVisit
> TechnicianRef int - Foreign Key for tblTechnician
> and has 329416 records - This allows me to have a visit which has more
than
> one technician.
> It has ForeignKey constraints for the two reference fields, and Primary
Key
> Index (Clustered) on the VisitTechnicianID field, and a normal Index on
the
> VisitRef field.
> Queries that use these tables, tend to take a little while to run. How do
I
> use Indexes to optimise this type of table relationship?
> I suspect that the Clustered index on the Primary key is wrong, but can
some
> of you Guru's out there lend me your considerable knowledge.
> Thanks
> Alex
>
|||Hi Alex,
I would discourage you to use "tbl" prefixes on table names. They serve
no purpose since a table can never be mistaken for a column or something
like that. The prefix just makes it harder to read.
Similar criticism towards the naming of the columns. If the column in
table Visit is called VisitID, then it is a good practice to also call
this column VisitID in the VisitTechnician table (and not VisitRef).
Again, the naming you used will confuse those who have to work with this
database.
Then your design. VisitTechnician is just a relation table. It consist
entirely of foreign key references. The Primary Key should be (VisitID,
TechnicianID). In my opinion, you should drop the VisitTechnicianID
column. It serves no purpose, has no meaning and adds no value.
If you make (VisitID, TechnicianID) the Primary Key, it will
automatically be (uniquely) indexed (like all Primary Key constraints in
SQL-Server). For performance reasons, you may want to add a unique index
on (TechnicianID, VisitID). This way, SQL-Server can choose between:
- joining Visit -> VisitTechnician -> Technician or
- joining Technician -> VisitTechnician -> Visit
And to conclude: I would discourage the use of "SELECT * " in production
code. It would be better to explicitely name the column you need.
Hope this helps,
Gert-Jan
Alex Stevens wrote:
> Hi All,
> I have a situation which I think could be resolved with proper indexing, but
> I need some advice on it.
> I have a table called tblVisit which has an int identity field [VisitID] and
> contains 329611 records.
> I have a table called tblTechnician which has an int indentity field
> [TechnicianID] and contains 200 records
> I have a table which marries up the two tables above called
> tblVisitTechnician which has:
> VisitTechncianID int Identity field (Primary Key)
> VisitRef int - Foriegn Key for tblVisit
> TechnicianRef int - Foreign Key for tblTechnician
> and has 329416 records - This allows me to have a visit which has more than
> one technician.
> It has ForeignKey constraints for the two reference fields, and Primary Key
> Index (Clustered) on the VisitTechnicianID field, and a normal Index on the
> VisitRef field.
> Queries that use these tables, tend to take a little while to run. How do I
> use Indexes to optimise this type of table relationship?
> I suspect that the Clustered index on the Primary key is wrong, but can some
> of you Guru's out there lend me your considerable knowledge.
> Thanks
> Alex
|||I'm no expert, but did implement something similar and am getting good
performance. If a techinician can't appear twice on a visit, then simplify
the tblVisitTechnician table:
VisitRef int - Foriegn Key for tblVisit
TechnicianRef int - Foreign Key for tblTechnicia
The primary key would be a combination of the two fields (visitRef 1st) and
should be clustered.
Then create a non-clustered index on the table by "TechnicianRef" to allow
you to quickly answer queries like "show me the visits that technician bob
has been on".
One nice feature you did have with using the clustered Identity column in
tblVisitTechnician is that your join table would not get very framented
(unless you are deleting records). With my suggestion you may need to defrag
the clustered and nonclustered index from time to time to get the best query
performance.
Good luck,
Phil Mattson
"Alex Stevens" wrote:

> To add:
> To give you an idea of speed, I ran this query which returns 329416 records
> and took three minutes to run:
> SELECT *
> FROM tblVisit
> JOIN tblVisitTechnician ON VisitID = VisitRef
> JOIN tblTechnician ON tblVisitTechnician.TechnicianRef = TechnicianID
> The number of returned records figures OK, as it is the same as the number
> of records in the tblVisitTechnician table - this is what I would expect.
> Thanks
> Alex
>
>
> "Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
> news:eupQ5tDvEHA.2624@.TK2MSFTNGP11.phx.gbl...
> but
> and
> than
> Key
> the
> I
> some
>
>