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
AlexTo 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...
> > 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
> >
> >
>
>

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
AlexTo 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][/vbco
l]
and[vbcol=seagreen]
> 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, b
ut
> I need some advice on it.
> I have a table called tblVisit which has an int identity field [VisitI
D] 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 th
an
> one technician.
> It has ForeignKey constraints for the two reference fields, and Primary Ke
y
> Index (Clustered) on the VisitTechnicianID field, and a normal Index on th
e
> 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 so
me
> 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 record
s
> 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
>
>

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
>
>

Indexed Views

Hi,
I would like to know how indexing works for indexed views under the
following situation.
I have multiple indexed views which has a common base table. But all the
views have different set of data beacuse of certain join conditions with
other tables.
So what happens when i insert a new row into this common base table?
Does it re-index all the indexed views which uses this table?
What other overheads are there for insert/updates/deletes in the tables?
Can i possibly get any kind of SQL profiler trace as to what goes on behind
scene?
Thanks in advance.
Vikramhi Vikram,
I think i didnt get your question right.
View is not a table. its just a query or a virtual table. the indexes come
into force only when u extract data from a view.
over heads on table:
if u are using clustered index, then the data is sorted physically as btree
on the data pages.
if it a non clustered, an entry is made/ changed in the mapping table
you can check the complexity using "Execution Plan"
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Vikram Kamath" wrote:

> Hi,
> I would like to know how indexing works for indexed views under the
> following situation.
> I have multiple indexed views which has a common base table. But all the
> views have different set of data beacuse of certain join conditions with
> other tables.
> So what happens when i insert a new row into this common base table?
> Does it re-index all the indexed views which uses this table?
> What other overheads are there for insert/updates/deletes in the tables?
> Can i possibly get any kind of SQL profiler trace as to what goes on behin
d
> scene?
> Thanks in advance.
> Vikram|||> So what happens when i insert a new row into this common base table?
> Does it re-index all the indexed views which uses this table?
Yes, the clsuter index and data on the indexed views are updated. See
"Creating an Indexed View" in BOL. These paragraphs are from BOL.
*****
Creating a clustered index on a view stores the data as it exists at the
time the index is created. An indexed view also automatically reflects
modifications made to the data in the base tables after the index is created
,
the same way an index created on a base table does. As modifications are mad
e
to the data in the base tables, the data modifications are also reflected in
the data stored in the indexed view. The requirement that the clustered inde
x
of the view be unique improves the efficiency with which SQL Server can find
the rows in the index that are affected by any data modification.
Indexed views can be more complex to maintain than indexes on base tables.
You should create indexes only on views where the improved speed in
retrieving results outweighs the increased overhead of making modifications.
This usually occurs for views that are mapped over relatively static data,
process many rows, and are referenced by many queries.
*****
AMB
"Vikram Kamath" wrote:

> Hi,
> I would like to know how indexing works for indexed views under the
> following situation.
> I have multiple indexed views which has a common base table. But all the
> views have different set of data beacuse of certain join conditions with
> other tables.
> So what happens when i insert a new row into this common base table?
> Does it re-index all the indexed views which uses this table?
> What other overheads are there for insert/updates/deletes in the tables?
> Can i possibly get any kind of SQL profiler trace as to what goes on behin
d
> scene?
> Thanks in advance.
> Vikram|||On Wed, 10 Aug 2005 09:06:32 -0700, Vikram Kamath wrote:

>Hi,
>I would like to know how indexing works for indexed views under the
>following situation.
>I have multiple indexed views which has a common base table. But all the
>views have different set of data beacuse of certain join conditions with
>other tables.
>So what happens when i insert a new row into this common base table?
>Does it re-index all the indexed views which uses this table?
>What other overheads are there for insert/updates/deletes in the tables?
>Can i possibly get any kind of SQL profiler trace as to what goes on behind
>scene?
Hi Vikram,
I don't think that MS has published what exactly happens when an indexed
view is updated. But you could take a good look at the execution plan
and see if that gives any hints.
Here's what I *think* happens. Or rather, how I would have chosen to
implement it if that had been my job.
Suppose you have an indexed view like this:
SELECT Region, COUNT_BIG(*) AS Tally, SUM(Value) AS Total
FROM dbo.MyTable
GROUP BY Region
Now, when rows are inserted to, deleted from or updated in MyTable,
there's no need to recompute the count and the sum - simply adding the
number of new rows and the sum of their values to, and subtracting the
number of deleted rows and the sum of their values from the appropriate
regions would suffice.
But OTOH - maybe the MS engineers found a much better way...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)