Fastest way

I've got a complex view (lots of inner and outer joins from several tables) in my SQL Server 2000 Database.

In my VB program, I've connected a grid to this view via an Ado Control
but the performances are not so great (even bad).

How can I boost the execution of this view ?

ThxI would look at those joins and see if there is any non-indexed scan first. You can also run the wizard to see if there is any room for improvement. Good luck!|||Hi again Joe from Texas (is it hot there at this moment ?)

I've put indexes on all the needed columns
but I've seen somewhere that we can create indexes on views

would that help ?|||If you look up "Creating an Indexed View" in Books Online, you'll see that there are a lot of restrictions on what types of views can be indexed. In particular, you can't index your view if it contains any of these elements:
Subqueries
Outer joins
Self joins
DISTINCT keyword
SUM functions that reference nullable expressions
.
.
. etc...

If your view is as complex as you say it is, there is a good chance that it cannot be indexed. Also, when you index a view SQL Server creates a permanent virtual table with the results of that view, and which is then updated any time any of the values in the underlying tables are updates. This can slow down other processing.

I think you would be better off rewriting your statement as a stored procedure rather than as a view. Stored procedures are more efficient than views.

Also, post your code and we may be able to find ways to make it more efficient.

blindman

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
>

Fan Trap or Multiple one-to-many joins

I'm having a problem creating a view on some data that involves two one-to-many joins like this:

tbl1 m--> tbl2 <--n tbl3

and I need to create a view on the data from all three tables without duplicates from tbl1 and tbl3.

The problem is that tbl1 and tbl3 are not related at all, except that they are linked by data in tbl2.

Think of it like this: you have a project, which can have multiple consultants, and multiple stakeholders, and the data must be returned in such a way that each consultant and each stakeholder appears once in the output (the project name must appear multiple times of course). The issue is that the following two datasets are logically distinct but semantically identical:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B
--
proj A, consultant B, stakeholder A
proj A, consultant A, stakeholder B

but what I'm aiming for is:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B

I've heard this described as a fan trap, but usual solutions involve reorganzing the data so that tbl3 joins tbl1 joins tbl2, but in my case there is no link there

Incidently, the intended platform for this is DB2 and/or SQL Server. Any help appreciated, thanks.your examples are not very clear

you start out by diagramming tbl1, tbl2, tbl3, and then immediately switch to projects, consultants, and shareholders, without showing the actual data in these tables, just some apparent cross join query results

you might wish to show a more comprehensive example, because so far, it's hard to understand what you're asking|||I've heard this described as many things, most of which aren't polite to repeat. ;)

You're trying to figure out how to build a join to show the relationship between consultants and shareholders, to produce a one-to-one join between two tables that explicitly have no relationship. If you figure out how to make this happen, please let me know... I'm sure that I'll be fascinated by the explanation!

You've got a clear relationship between project and shareholder, and another relationship between project and consultant. You don't explicitly state that there must be a shareholder or a consultant for any given project, and at some point in the project's life I can guarantee that there will not be one of either. You don't explicitly state that there must be one shareholder for every consultant. If you think about these requirements, unless at least one of these requirements is false, you can't get the output you want... There ain't no way to git there from here.

You need to rethink either the specifications or the requirements. Something has got to give because using the definitions that you've given, the present problem can't be solved.

-PatP|||ah, so that's what that is -- i had never heard that terminology before

this pdf is a pretty good explanation --
http://support.businessobjects.com/documentation/installation_resources/5i/tips_and_tricks/pdf/universe_design/ut001.pdf

i would solve this problem with a UNION query --

proj A, consultant A
proj A, consultant B
proj A, stakeholder A
proj A, stakeholder B