Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
Pieter
On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:

> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA
|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net. ..
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:

> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
--
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>> Hi,
>> My application consist of 2 big parts, which work together but are in
>> some
>> way seperate.
>> In case I should store the 2 parts in different databases (but on the
>> same
>> server), will I lose performance when doing a query that joins tables
>> from
>> both databases? And what about views?
>> Any help our hints would be really appreciated.
>> Thanks a lot in advance,
>> Pieter
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does it store all the results to tempdb database when I query against a large table which joins

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

It doesn't store all the results - but it may store some intermediate results if it needs to.

You really shouldn't be putting tempdb on your local system drive. tempdb always has a lot of activity (typically the busiest database in any system), and ideally should be on its own disk - a fast one.

If you have over 4 million records in a single table, 1GB doesn't sound too big for tempdb. But I appreciate this doesn't fix your immediate problem.

Rob|||

Hi, Rob,

Thank you for your kind advices.

But how can we change the storage location of the tempdb database? It looks like tempdb database can not be detached.

I am looking forward to hearing from you further for your kind advices.

With best regards,

Yours sincerely,

|||

And also, though I have moved the data files of tempdb to other places, but when I restarted the database engine, the system automatically generates the data files of tempdb again back to its root data files?

Please give me some more advices for this.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||I believe moving tempdb is as simple as altering the file paths and restarting SQL Server. It'll create the new files automatically. I'm not sure if it removes the old ones automatically, so you may have to to some brief housekeeping once the database is up and running in its new location.

ALTER DATABASE tempdb MODIFY FILE (NAME = logical_filename, FILENAME = 'new_physical_file_path')

Just make sure you do the log in addition to all the data files you have.
|||

Hi, davidbrit2,

Thank you for your kind attention.

But I failed to modify the files paths for the data files of the tempdb database, I did restart the service, but the data files of tempdb again went back to its original file paths. Therefore I does not seem like we are able to change the file paths of the data files of tempdb.

Any more ideas? Thanks a lot in advance.

With best regards,

Yours sincerely,

|||You need to alter the database to tell it to have its files in a different location. But then you need to restart SQL Server (ie, the service - use SQL Configuration Manager) to actually have it start using those new locations. Once you've restarted SQL, you should see the new files start to be used. Then you can delete the old ones.

Hope this helps,

Rob|||

Hello Helen999888,

Let me start with giving you a brief of what is tempdb.
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions
|||

Hi, HS_DJ,

Thanks a lot for your very kind and helpful advices. Got it done now.

With best regards,

Yours sincerely,

Does It Matter...

MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it very clear to anyone
who inherits the code (or myself, when I go senile) which criteria are for
the relationship and which criteria are meant to limit the end result.
For OUTER JOINs, it can certainly matter, but it depends on your desired
result. You may exclude rows by moving criteria from ON to WHERE or vice
versa. I don't know of any situations in INNER JOIN where this is true, but
I bet Itzik or Steve will reproduce one if it exists.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you
place additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows
before joining them? or should I put anything that is not pertinent to the
join itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Pre SQL 2000 SP4 I would say "Yes" it does matter.
Post SP4 I would say "No" it doesn't matter.
SP4 has given some huge performance gains at my site.
However, try it for yourself. Use Profiler / view the Execution plan etc.
To establish your version use SELECT @.@.VERSION.
http://www.aspfaq.com/SQL2000Builds.asp
--
HTH. Ryan
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.764
@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Thanks Aaron,
Actually I'm more concerned with performance at this point.
Any thoughts on that...
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:OUZNW4nfGH
A.2032@.TK2MSFTNGP02.phx.gbl...
> For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it
> very clear to anyone who inherits the code (or myself, when I go senile) w
hich criteria are for the relationship and which
> criteria are meant to limit the end result.
> For OUTER JOINs, it can certainly matter, but it depends on your desired r
esult. You may exclude rows by moving criteria from ON
> to WHERE or vice versa. I don't know of any situations in INNER JOIN wher
e this is true, but I bet Itzik or Steve will reproduce
> one if it exists.
>
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.7
64@.TK2MSFTNGP03.phx.gbl...
> MSSQl 2000
> When creating a select statement with joins... does it matter where you pl
ace additional where-clause criteria.
> Considering the two examples below, is it more efficient to place addition
al filtering criteria within the join section.? Does it
> weed out extra rows before joining them? or should I put anything that is
not pertinent to the join itself down below in its own
> where clause?
> EXAMPLE 1
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
> inner join table_c C ON C.column_2 = B.column_2
>
> EXAMPLE 2
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> inner join table_c C ON C.column_2 = B.column_2
> WHERE
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
>
>
>
>|||I think as a rule, the optimizer will do the same thing regardless, although
as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When optimizing
the engine will check only so many possible paths before determining which
one to use, so on larger more complex queries the order of the joins and
criteria can determine which paths get evaluated before it gives up and
chooses one.
I think the bottom line is theoretically it doesn't matter, but the only way
to be totally certain is to test it out both ways. Not just the location of
the criteria, but the order of the tables as well. For simpler queries with
a handful of joins and filter criteria, when the optimizer can afford to
calculate every possibility, it should work the same. If you have dozens of
tables and just as many filters involved, it is worth playing with different
scenarios to see if it makes a difference. With more complex queries the
optimizer can find literally billions of possible execution plans, and
influencing it to look at the right ones can be hit or miss.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron,
> Actually I'm more concerned with performance at this point.
> Any thoughts on that...
> Robert
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
the filtering criteria in the WHERE clause. This makes it
which criteria are for the relationship and which
result. You may exclude rows by moving criteria from ON
where this is true, but I bet Itzik or Steve will reproduce
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
place additional where-clause criteria.
additional filtering criteria within the join section.? Does it
is not pertinent to the join itself down below in its own
'01/31/06'
>|||I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what the others have said. MS considers is more correct
to place the items on the join itself, as this will help SQL to choose the
best execution plan. The idea is that placing more items on the join, means
that the selected table will return less results, before the Join is
executed.
The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the optimising and tuning course for SQL 2005!
My personal view is to use the Join in preference to the Where clause, I
find that it helps to make the syntax clearer and easier to understand.
Regards
Colin Dawson
www.cjdawson.com
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>I think as a rule, the optimizer will do the same thing regardless,
>although
> as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When
> optimizing
> the engine will check only so many possible paths before determining which
> one to use, so on larger more complex queries the order of the joins and
> criteria can determine which paths get evaluated before it gives up and
> chooses one.
> I think the bottom line is theoretically it doesn't matter, but the only
> way
> to be totally certain is to test it out both ways. Not just the location
> of
> the criteria, but the order of the tables as well. For simpler queries
> with
> a handful of joins and filter criteria, when the optimizer can afford to
> calculate every possibility, it should work the same. If you have dozens
> of
> tables and just as many filters involved, it is worth playing with
> different
> scenarios to see if it makes a difference. With more complex queries the
> optimizer can find literally billions of possible execution plans, and
> influencing it to look at the right ones can be hit or miss.
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> the filtering criteria in the WHERE clause. This makes it
> which criteria are for the relationship and which
> result. You may exclude rows by moving criteria from ON
> where this is true, but I bet Itzik or Steve will reproduce
> news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
> place additional where-clause criteria.
> additional filtering criteria within the join section.? Does it
> is not pertinent to the join itself down below in its own
> '01/31/06'
>|||> The blurb says that the Join syntax is evaluated before the Where syntax.
That is only the logical order. For inner joins, it doesn't matter, and I do
n't even think that the
optimizer know what join type you expressed (the query is transformed into a
tree structure before
the optimizer gets hold of it). The optimizer is free to transform the query
in any way as long as
it returns the same information as if it executed the query as per the rules
for the logical order.

> The course is the optimizing and tuning course for SQL 2005!
Interesting. Which one? There are two such courses, one for "admins" and one
for "developers". Also,
can you point to the module and perhaps even page number and I'll have a loo
k at how they phrase it.

> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what
>the others have said. MS considers is more correct to place the items on t
he join itself, as this
>will help SQL to choose the best execution plan. The idea is that placing
more items on the join,
>means that the selected table will return less results, before the Join is
executed.
> The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the
> optimising and tuning course for SQL 2005!
> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||Hello Tibor
The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
The bit that I was referring two use in Unit 3. Specifically the Query
logical flow diagram on page 2.
Basically it shows the flow as
From & Join --> Where --> Select -- > .... (lots more stuff)
From experience I do agree that it doesn't seem to matter as the query
optimiser does make changes to the query as typed, into how it wants to
produce the results.
Regards
Colin Dawson
www.cjdawson.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> That is only the logical order. For inner joins, it doesn't matter, and I
> don't even think that the optimizer know what join type you expressed (the
> query is transformed into a tree structure before the optimizer gets hold
> of it). The optimizer is free to transform the query in any way as long as
> it returns the same information as if it executed the query as per the
> rules for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and
> one for "developers". Also, can you point to the module and perhaps even
> page number and I'll have a look at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>|||My personal preference is to use the primary key/foreign key relation
columns in the join clause, and all other predicates in the where
clause. This is a very consistent syntax that underscores the table
relations and automatically moves all filters to the where clause.
As mentioned before it is a different story for outer joins...
Gert-Jan
Tibor Karaszi wrote:
>
> That is only the logical order. For inner joins, it doesn't matter, and I
don't even think that the
> optimizer know what join type you expressed (the query is transformed into
a tree structure before
> the optimizer gets hold of it). The optimizer is free to transform the que
ry in any way as long as
> it returns the same information as if it executed the query as per the rul
es for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and o
ne for "developers". Also,
> can you point to the module and perhaps even page number and I'll have a l
ook at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...|||Colin,

> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
Thanks. I had a quick look through the courses after I posted prior reply, a
nd I guessed this was
the one. The important part here is that it is the *logical* flow. Quote fro
m the same page:
"
Note that
although there is a guaranteed logical order, this is not true of the actual
physical order. The
query
processor can process the query in a different order but still ensure the sa
me results, if it can
find a
more efficient method for doing so.
"
If the optimizer had to respect the logical flow, then almost every query wo
uld give us horrendous
performance:
FROM, grab all columns, and even cross join if old style join syntax
WHERE remove the rows that doesn't satisfies the conditions (including the j
oin if old-style join)
GROUP BY
HAVING
SELECT, until now we had all the columns from all the tables
ORDER BY, not until now could we sort the rows
TOP, ouch, all rows had to be sorted until we throw away all but "top n".
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:VsIcg.74297$wl.32163@.text.news.blueyonder.co.uk...
> Hello Tibor
> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
> Basically it shows the flow as
> From & Join --> Where --> Select -- > .... (lots more stuff)
>
> From experience I do agree that it doesn't seem to matter as the query opt
imiser does make changes
> to the query as typed, into how it wants to produce the results.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
>

Does a View of an Indexed Table has a View?

Hi,
I have a View, which is actually a Union of some joins of some Tables. All
these Tables have indexes.
Will these indexes speed up a query when doing a Select on the View? Or will
the view behave as a non-indexed table? Should I take in account a lack of
performance when using the view?
any answers, explanations, workarounds for this are welcome!
Thanks a lot in advance,
Pieter
Pieter wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some
> Tables. All these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View?
> Or will the view behave as a non-indexed table? Should I take in
> account a lack of performance when using the view?
Indexes are used if appropriate for your query criteria. You can see this
by looking at the execution plan with QA.
Kind regards
robert
|||Hi Pieter
Views are virtual tables and they are not actual tables. When you try to
query a view, u will actually be executing a query.
The query will use the same indexes that a table is using.
However u can create an index on views, creating an index on a view will
effect that query only and not the main table
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
"Pieter" wrote:

> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or will
> the view behave as a non-indexed table? Should I take in account a lack of
> performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
>
|||Pieter,

> Will these indexes speed up a query when doing a Select on the View?
The answer is "it depends". i.e., what columns are you asking for? what
rows? do the index(es) match the row criteria? are the columns highly
selective? etc.. etc...
If the optimizer determines that an index(es) can assist in accessing the
data from the underlying tables when the view is queried, then the optimizer
will likely use the index(es). Use the Display Estimated Execution Plan
(Ctrl+L) to determine the predicted access method.
Creating an index on a view (clustered) will make a copy of the data. This
approach is useful when the underlying data does not change very often and
aggregates are involved.
HTH
Jerry
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:Olj4WGX2FHA.2436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or
> will the view behave as a non-indexed table? Should I take in account a
> lack of performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
|||Ok thanks a lot for the info!
One more question: In the enterprise Manager the option "All Tasks" ->
"Manage Indexes" is disabled when right-clicking on the View. Is the only
way to manage indexes on a View via the query analyzer?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:EFC3D7B8-44AE-4721-9875-9EFF46F72190@.microsoft.com...[vbcol=seagreen]
> Hi Pieter
> Views are virtual tables and they are not actual tables. When you try to
> query a view, u will actually be executing a query.
> The query will use the same indexes that a table is using.
> However u can create an index on views, creating an index on a view will
> effect that query only and not the main table
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
>
> "Pieter" wrote:

Does a View of an Indexed Table has a View?

Hi,
I have a View, which is actually a Union of some joins of some Tables. All
these Tables have indexes.
Will these indexes speed up a query when doing a Select on the View? Or will
the view behave as a non-indexed table? Should I take in account a lack of
performance when using the view?
any answers, explanations, workarounds for this are welcome!
Thanks a lot in advance,
PieterPieter wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some
> Tables. All these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View?
> Or will the view behave as a non-indexed table? Should I take in
> account a lack of performance when using the view?
Indexes are used if appropriate for your query criteria. You can see this
by looking at the execution plan with QA.
Kind regards
robert|||Hi Pieter
Views are virtual tables and they are not actual tables. When you try to
query a view, u will actually be executing a query.
The query will use the same indexes that a table is using.
However u can create an index on views, creating an index on a view will
effect that query only and not the main table
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Pieter" wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or will
> the view behave as a non-indexed table? Should I take in account a lack of
> performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
>|||Pieter,
> Will these indexes speed up a query when doing a Select on the View?
The answer is "it depends". i.e., what columns are you asking for? what
rows? do the index(es) match the row criteria? are the columns highly
selective? etc.. etc...
If the optimizer determines that an index(es) can assist in accessing the
data from the underlying tables when the view is queried, then the optimizer
will likely use the index(es). Use the Display Estimated Execution Plan
(Ctrl+L) to determine the predicted access method.
Creating an index on a view (clustered) will make a copy of the data. This
approach is useful when the underlying data does not change very often and
aggregates are involved.
HTH
Jerry
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:Olj4WGX2FHA.2436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or
> will the view behave as a non-indexed table? Should I take in account a
> lack of performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>|||Ok thanks a lot for the info!
One more question: In the enterprise Manager the option "All Tasks" ->
"Manage Indexes" is disabled when right-clicking on the View. Is the only
way to manage indexes on a View via the query analyzer?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:EFC3D7B8-44AE-4721-9875-9EFF46F72190@.microsoft.com...
> Hi Pieter
> Views are virtual tables and they are not actual tables. When you try to
> query a view, u will actually be executing a query.
> The query will use the same indexes that a table is using.
> However u can create an index on views, creating an index on a view will
> effect that query only and not the main table
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Pieter" wrote:
>> Hi,
>> I have a View, which is actually a Union of some joins of some Tables.
>> All
>> these Tables have indexes.
>> Will these indexes speed up a query when doing a Select on the View? Or
>> will
>> the view behave as a non-indexed table? Should I take in account a lack
>> of
>> performance when using the view?
>> any answers, explanations, workarounds for this are welcome!
>> Thanks a lot in advance,
>> Pieter
>>

Does a View of an Indexed Table has a View?

Hi,
I have a View, which is actually a Union of some joins of some Tables. All
these Tables have indexes.
Will these indexes speed up a query when doing a Select on the View? Or will
the view behave as a non-indexed table? Should I take in account a lack of
performance when using the view?
any answers, explanations, workarounds for this are welcome!
Thanks a lot in advance,
PieterPieter wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some
> Tables. All these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View?
> Or will the view behave as a non-indexed table? Should I take in
> account a lack of performance when using the view?
Indexes are used if appropriate for your query criteria. You can see this
by looking at the execution plan with QA.
Kind regards
robert|||Hi Pieter
Views are virtual tables and they are not actual tables. When you try to
query a view, u will actually be executing a query.
The query will use the same indexes that a table is using.
However u can create an index on views, creating an index on a view will
effect that query only and not the main table
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Pieter" wrote:

> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or wi
ll
> the view behave as a non-indexed table? Should I take in account a lack of
> performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
>|||Pieter,

> Will these indexes speed up a query when doing a Select on the View?
The answer is "it depends". i.e., what columns are you asking for? what
rows? do the index(es) match the row criteria? are the columns highly
selective? etc.. etc...
If the optimizer determines that an index(es) can assist in accessing the
data from the underlying tables when the view is queried, then the optimizer
will likely use the index(es). Use the Display Estimated Execution Plan
(Ctrl+L) to determine the predicted access method.
Creating an index on a view (clustered) will make a copy of the data. This
approach is useful when the underlying data does not change very often and
aggregates are involved.
HTH
Jerry
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:Olj4WGX2FHA.2436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or
> will the view behave as a non-indexed table? Should I take in account a
> lack of performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>|||Ok thanks a lot for the info!
One more question: In the enterprise Manager the option "All Tasks" ->
"Manage Indexes" is disabled when right-clicking on the View. Is the only
way to manage indexes on a View via the query analyzer?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:EFC3D7B8-44AE-4721-9875-9EFF46F72190@.microsoft.com...[vbcol=seagreen]
> Hi Pieter
> Views are virtual tables and they are not actual tables. When you try to
> query a view, u will actually be executing a query.
> The query will use the same indexes that a table is using.
> However u can create an index on views, creating an index on a view will
> effect that query only and not the main table
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Pieter" wrote:
>