Faster JOINs?

I've licensed a read-only database (no insertions, updates, or data
integrity issues) that comes in an Access MDB and so when I move the many
tables and data over to SQL Server, it's basically a flat file database
without indexes, relationships, constraints, etc. although the table rows
are in order. There are many relationships between the tables and the
queries I will be using of course use JOINs.
Although I don't know for sure, I intend to create indexes with SQL Server
on the tables because I would think this would speed up the queries
considerably (although the tables are in order). However, my question is,
does it make any sense to specify and create hundreds of relationships
between about a hundred tables as I'll be using JOINs quite a bit? Is it
faster to have these relationships already specified in the database, rather
than when the queries (soon to be SPs) execute?
Thanks for any discussion."Don Miller" <nospam@.nospam.com> wrote in message
news:%23bbZ7m2pFHA.3516@.TK2MSFTNGP15.phx.gbl...
> I've licensed a read-only database (no insertions, updates, or data
> integrity issues) that comes in an Access MDB and so when I move the many
> tables and data over to SQL Server, it's basically a flat file database
> without indexes, relationships, constraints, etc. although the table rows
> are in order. There are many relationships between the tables and the
> queries I will be using of course use JOINs.
> Although I don't know for sure, I intend to create indexes with SQL Server
> on the tables because I would think this would speed up the queries
> considerably (although the tables are in order). However, my question is,
> does it make any sense to specify and create hundreds of relationships
> between about a hundred tables as I'll be using JOINs quite a bit? Is it
> faster to have these relationships already specified in the database,
> rather
> than when the queries (soon to be SPs) execute?
>
The relationships will guide the index creation. An index is required on
the primary key side of the relationship, and suggested on the foreign key
side. If you create the relationships and supporting indexes, it may speed
up joins quite a bit.
David|||Ok, first:
The tables are only physically in order. This means naught to the
optimizer. You must add indexes to get performance out of any "ordering"
If you aren't going to update the database the go wild with indexes. as much
as you need.
Only if you can do it relatively painlessly. The optimizer won't use the
foreign keys for much if anything, but it will make it easier to build a
diagram of your tables and is great documentation. If FK's are named the
same as PK's then you could build a script for doing this pretty quickly
using the information schema.
Consider contacting the liscenser (sp?) and see if they want this. Maybe
you might make a buck for your troubles.
> The relationships will guide the index creation. An index is required on
> the primary key side of the relationship, and suggested on the foreign key
> side. If you create the relationships and supporting indexes, it may
> speed up joins quite a bit.
Definitely the primary keys, and most assuredly on the foreign key columns.
They may or may not be useful, depending on how the data is used, but for a
read only database, who cares. Indexes only hurt modification performance.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OIgQc%232pFHA.208@.TK2MSFTNGP10.phx.gbl...
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:%23bbZ7m2pFHA.3516@.TK2MSFTNGP15.phx.gbl...
> The relationships will guide the index creation. An index is required on
> the primary key side of the relationship, and suggested on the foreign key
> side. If you create the relationships and supporting indexes, it may
> speed up joins quite a bit.
> David
>|||First of all, thanks for taking the time to answer my questions. I'm
intrigued (and would like to save myself a lot of time) by your statement:

> If FK's are named the
> same as PK's then you could build a script for doing this pretty quickly
> using the information schema.
They are. Of course there are many FKs in many tables for one PK, I'm not
sure I know how to use the "information schema" or sysobjects? to keep them
straight. Also, is it possible to use a script to do the indexing as well
(e.g. every column with an "*ID" in the column name should be indexed)?
It sounds like one script could do all of this quickly. Could you point me
to examples of how this could be done? Thanks again.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uGK72f6pFHA.1028@.TK2MSFTNGP09.phx.gbl...
> Ok, first:
> The tables are only physically in order. This means naught to the
> optimizer. You must add indexes to get performance out of any "ordering"
>
is,
> If you aren't going to update the database the go wild with indexes. as
much
> as you need.
>
it
> Only if you can do it relatively painlessly. The optimizer won't use the
> foreign keys for much if anything, but it will make it easier to build a
> diagram of your tables and is great documentation. If FK's are named the
> same as PK's then you could build a script for doing this pretty quickly
> using the information schema.
>
many
rows
> Consider contacting the liscenser (sp?) and see if they want this. Maybe
> you might make a buck for your troubles.
>
on
key
> Definitely the primary keys, and most assuredly on the foreign key
columns.
> They may or may not be useful, depending on how the data is used, but for
a
> read only database, who cares. Indexes only hurt modification
performance.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
convincing."
> (Oscar Wilde)
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:OIgQc%232pFHA.208@.TK2MSFTNGP10.phx.gbl...
many
rows
is,
it
on
key
>|||There are information_schema views for tables, columns etc:
select * from information_schema.tables or .columns
From there you just make queries like:
select 'create index ' + table_name + '_' + column_name + ' on ' +
table_name + '(' + column_name + ')'
from information_schema.columns
where column_name like '%id'
The more complex you want them, the harder the script is to write, but they
payoff is pretty good if the output is complex.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Don Miller" <nospam@.nospam.com> wrote in message
news:uWOb7e%23pFHA.1028@.TK2MSFTNGP09.phx.gbl...
> First of all, thanks for taking the time to answer my questions. I'm
> intrigued (and would like to save myself a lot of time) by your statement:
>
> They are. Of course there are many FKs in many tables for one PK, I'm not
> sure I know how to use the "information schema" or sysobjects? to keep
> them
> straight. Also, is it possible to use a script to do the indexing as well
> (e.g. every column with an "*ID" in the column name should be indexed)?
> It sounds like one script could do all of this quickly. Could you point me
> to examples of how this could be done? Thanks again.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uGK72f6pFHA.1028@.TK2MSFTNGP09.phx.gbl...
> is,
> much
> it
> many
> rows
> on
> key
> columns.
> a
> performance.
> --
> convincing."
> many
> rows
> is,
> it
> on
> key
>