Indexing question

We have SQL 2000 database with 4 heaviliy used tables. The primary table is
an orders table with FK's linking orders to the 3 other tables and all link
to the PK in those tables. We have several views that display data joined
on these FK to PK links. Are the FK's on the orders table speeding up views
if most of the criteria for search is based on columns in the orders table
or are they just extra overhead? Thanks.
David
Hi David
Foreign keys have nothing to do with speeding up views and
performance. The role of foreign key is to maintain data integrity.
For example if you have a foreign key between Orders and Customer
tables, then you won't be able to insert an order to a none existing
customer (or to delete a customer that has an order).
If you want to speed up the view you could try creating indexes on
the columns that create the foreign key. If we fallow the previous
example, an index in Order table on CustomerID column might help a view
that join Orders and Customers table.
Adi
On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
> We have SQL 2000 database with 4 heaviliy used tables. The primary table is
> an orders table with FK's linking orders to the 3 other tables and all link
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up views
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David
|||By default, defining a foreign key does not create any underlying indexes, so
there should not be any performance impact in the views. Depending on your
data distribution, it is possible that indexing the foreign key columns in
the child tables may increase performance on read operations.
I hope that this helps somehow.
"David" wrote:

> We have SQL 2000 database with 4 heaviliy used tables. The primary table is
> an orders table with FK's linking orders to the 3 other tables and all link
> to the PK in those tables. We have several views that display data joined
> on these FK to PK links. Are the FK's on the orders table speeding up views
> if most of the criteria for search is based on columns in the orders table
> or are they just extra overhead? Thanks.
> David
>
>
|||Hello,
I do have indexes on the orders table for the FK that links the other tables
to their PK. I am having a problem with 2 of the tables (customer and
insurance) that are locking and preventing inserts and updates.
David
"Adi" <adicohn@.hotmail.com> wrote in message
news:1169737988.232529.120600@.q2g2000cwa.googlegro ups.com...
> Hi David
> Foreign keys have nothing to do with speeding up views and
> performance. The role of foreign key is to maintain data integrity.
> For example if you have a foreign key between Orders and Customer
> tables, then you won't be able to insert an order to a none existing
> customer (or to delete a customer that has an order).
> If you want to speed up the view you could try creating indexes on
> the columns that create the foreign key. If we fallow the previous
> example, an index in Order table on CustomerID column might help a view
> that join Orders and Customers table.
> Adi
> On Jan 25, 4:26 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>
|||Hi David
The insert to the Order table can be blocked if a table that is
referenced by Orders table is being locked. You should try and find
out why Customer and Insurance tables are locked for a long time. Do
you know which SQL Statement is locking those tables? If you know
which process is causing the blocking, then you can get the details
about the what the process is doing with dbcc inputbuffer and with
fn_get_sql() function.
Adi
On Jan 25, 9:36 pm, "David" <dlch...@.lifetimeinc.com> wrote:[vbcol=seagreen]
> Hello,
> I do have indexes on the orders table for the FK that links the other tables
> to their PK. I am having a problem with 2 of the tables (customer and
> insurance) that are locking and preventing inserts and updates.
> David
> "Adi" <adic...@.hotmail.com> wrote in messagenews:1169737988.232529.120600@.q2g2000cwa.go oglegroups.com...
>
>
>
>