Percentage of total: joins

Showing posts with label joins. Show all posts

Wednesday, March 21, 2012

Performance cost of joins

We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||

It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.

Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:

-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC

|||

its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Performance cost of joins

|||

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Performance cost of joins

|||

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Performance considerations UDF vs Joins

Hi all,
New to SQL, but getting into it, converting Access queries to SQL.
I have a query in access which includes the following
SELECT
...
Case when A.Z is not null then C1.Y else C2.Y END AS MNO
...
FROM (A INNER JOIN
(B LEFT JOIN C C1 ON B.Z = C1.PK)
ON A.PK = B.PK)
LEFT JOIN C C2 ON A.Z = C2.PK;
This works fine in SQL Server, but I've also written a UDF which is
passed A.Z and returns MNO through direct reference to table C
(SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
I;m only working on a small DB, so performance is not an issue, but
I'm curious as to which should have better performance considerations
so I know which way to take things. The UDF certainly makes things
tidyier, so I will stick with it unless there's a reason not to.
Thanks,
MattMatt
> (SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
Does ther UDF accept a parameter?
(Untested)
SELECT .......
CASE
WHEN EXISTS (
SELECT * FROM C WHERE PK = Value)
THEN C1.Y
ELSE C2.Y
END AS MNO
...
FROM (A INNER JOIN
(B LEFT JOIN C C1 ON B.Z = C1.PK)
ON A.PK = B.PK)
LEFT JOIN C C2 ON A.Z = C2.PK;
PS. Have a look at execution plan of yours query?
You can make it as a stored procedure which will accept a parameter so I
gave an idea .
"Matt Bolton" <m3it@.technologist.com> wrote in message
news:8kdrv0tr5l9dlk16vl0dl97hsj959u1rfv@.
4ax.com...
> Hi all,
> New to SQL, but getting into it, converting Access queries to SQL.
> I have a query in access which includes the following
> SELECT
> ...
> Case when A.Z is not null then C1.Y else C2.Y END AS MNO
> ...
> FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
> This works fine in SQL Server, but I've also written a UDF which is
> passed A.Z and returns MNO through direct reference to table C
> (SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
> I;m only working on a small DB, so performance is not an issue, but
> I'm curious as to which should have better performance considerations
> so I know which way to take things. The UDF certainly makes things
> tidyier, so I will stick with it unless there's a reason not to.
> Thanks,
> Matt|||Yes, accepts A.Z and B.Z (If A.Z is null, then uses B.Z to get
required value).
I'll try looking at execution plan - forgot about that option. I
gather there are no general rules with this sort of thing, since
execution plan really answers the question definitively.
Thanks,
Matt
On Mon, 31 Jan 2005 07:23:20 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Matt
>Does ther UDF accept a parameter?
>(Untested)
>SELECT .......
>CASE
> WHEN EXISTS (
> SELECT * FROM C WHERE PK = Value)
> THEN C1.Y
> ELSE C2.Y
> END AS MNO
> ...
> FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
>
>PS. Have a look at execution plan of yours query?
>You can make it as a stored procedure which will accept a parameter so I
>gave an idea .
>
>"Matt Bolton" <m3it@.technologist.com> wrote in message
> news:8kdrv0tr5l9dlk16vl0dl97hsj959u1rfv@.
4ax.com...
>|||On Mon, 31 Jan 2005 15:56:44 +1100, Matt Bolton wrote:

>I have a query in access which includes the following
>SELECT
> ...
> Case when A.Z is not null then C1.Y else C2.Y END AS MNO
> ...
>FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
>This works fine in SQL Server, but I've also written a UDF which is
>passed A.Z and returns MNO through direct reference to table C
>(SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
>I;m only working on a small DB, so performance is not an issue, but
>I'm curious as to which should have better performance considerations
>so I know which way to take things. The UDF certainly makes things
>tidyier, so I will stick with it unless there's a reason not to.
Hi Matt,
I'll come to your question shortly. First another remark.
Are you sure you made no errors when copying the query in your message? It
seems to me that if A.Z is not null, you'd want to select C2.Y, not C1.Y,
as C2.Y is joined on A.Z and C1.Y on B.Z. The code you posted will display
the description for B.Z from the lookup table if A.Z has a value and will
display NULL is A.Z has no value (as C2.Y will be NULL in that case). In
the rest of the message, I'll assume that you meant it to be the other way
around.
WRT the question: performance will probably be better if you stick to the
current version. The UDF you use is scalar (you don't post the complete
code, but this is what I conclude from what you do post). This means that
it will be called once for each row in the result set. And for each call,
a seperate read in the lookup table has to be performed. For the query
above, the execution plan MIGHT be the same - but it need not. The
optimizer will consider the various indexes available, check some
statistics and then create an execution plan with the best expected
exectution speed. Of course, as Uri indicated, actually testing the
different versions is the best way to know for certasin which will be the
fastest.
You mention "tidyier". This is of course very much based on personal
preferences, so take the following with a grain of salt - but I think that
you could make your current query tidyier without using a UDF, simply by
exchanging the order in which the tables appear in the FROM clause:
SELECT ....
CASE WHEN A.Z IS NOT NULL THEN C1.Y ELSE C2.Y END AS MNO,
-- or: COALESCE (C1,Y, C2.Y) -- if this can be used depends on your data
...
FROM A
INNER JOIN B
ON B.PK = A.PK
LEFT JOIN C AS C1
ON C1.PK = A.Z
LEFT JOIN C AS C2
ON C2.PK = B.Z
If you only need the C table for the lookup in the select and don't use
any value from C in the rest of the query, you could also consider the
following alternative (but do test it for performance impact!):
SELECT ....
(SELECT C.Y
FROM C
WHERE C.PK = COALESCE (A.Z, B.Z)) AS MNO,
...
FROM A
INNER JOIN B
ON B.PK = A.PK
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Yes, you're correct about C1 and C2 being swapped. Thanks for the
suggestions - the COALESCE (C1,Y, C2.Y) is simplest and works.
I agree that my join clause is a bit untidy - it's the output of a VB
module I use to make writing complex queries easier - but it needs a
bit of tidying itself.
I'd got the impression from some of the reading I've done here that I
should try to do less calculation in queries and as much as possible
in SP/UDF, and the main thrust of my question was to confirm that -
which you (and others) haven't - so either way I now have a clearer
understanding of where my project's headed.
Thanks,
Matt
On Mon, 31 Jan 2005 09:23:29 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Mon, 31 Jan 2005 15:56:44 +1100, Matt Bolton wrote:
>
>Hi Matt,
>I'll come to your question shortly. First another remark.
>Are you sure you made no errors when copying the query in your message? It
>seems to me that if A.Z is not null, you'd want to select C2.Y, not C1.Y,
>as C2.Y is joined on A.Z and C1.Y on B.Z. The code you posted will display
>the description for B.Z from the lookup table if A.Z has a value and will
>display NULL is A.Z has no value (as C2.Y will be NULL in that case). In
>the rest of the message, I'll assume that you meant it to be the other way
>around.
>WRT the question: performance will probably be better if you stick to the
>current version. The UDF you use is scalar (you don't post the complete
>code, but this is what I conclude from what you do post). This means that
>it will be called once for each row in the result set. And for each call,
>a seperate read in the lookup table has to be performed. For the query
>above, the execution plan MIGHT be the same - but it need not. The
>optimizer will consider the various indexes available, check some
>statistics and then create an execution plan with the best expected
>exectution speed. Of course, as Uri indicated, actually testing the
>different versions is the best way to know for certasin which will be the
>fastest.
>You mention "tidyier". This is of course very much based on personal
>preferences, so take the following with a grain of salt - but I think that
>you could make your current query tidyier without using a UDF, simply by
>exchanging the order in which the tables appear in the FROM clause:
>SELECT ....
> CASE WHEN A.Z IS NOT NULL THEN C1.Y ELSE C2.Y END AS MNO,
>-- or: COALESCE (C1,Y, C2.Y) -- if this can be used depends on your data
> ....
>FROM A
>INNER JOIN B
> ON B.PK = A.PK
>LEFT JOIN C AS C1
> ON C1.PK = A.Z
>LEFT JOIN C AS C2
> ON C2.PK = B.Z
>If you only need the C table for the lookup in the select and don't use
>any value from C in the rest of the query, you could also consider the
>following alternative (but do test it for performance impact!):
>SELECT ....
> (SELECT C.Y
> FROM C
> WHERE C.PK = COALESCE (A.Z, B.Z)) AS MNO,
> ....
>FROM A
>INNER JOIN B
> ON B.PK = A.PK
>Best, Hugo|||On Tue, 01 Feb 2005 10:01:25 +1100, Matt Bolton wrote:
(snip)
>I'd got the impression from some of the reading I've done here that I
>should try to do less calculation in queries and as much as possible
>in SP/UDF,
(snip)
Hi Matt,
In SP: yes. Not because SP will perform better than queries, but for two
other reasons:
1. Security: don't give any user any rights to modify data in any table;
instead, give them only execute permission on stored procedures that will
perform the desired modifications in a controleed manner. Or take it a
step further: don't give any rights to view the data in the tables or
views at all; give them some more stored procedures to get the requested
data.
2. Indirect performance benefit: because you can control the quality of
the SQL in the procedures and fine-tune your indexes to the contents of
these procedures, you can gain performance. You'll never have that level
of control if you allow application programmers or even end users to
create their own ad-hoc queries.
In UDF: no. Especially scalar UDF's are a performance drag, because they
ahve to be executed once for each row. A scalar UDF that reads data from a
table will allways be outperformed by incorporating the logic into the
query. Of course, there may be other reasons that make the UDF the best
choice, but you do take the performance hit.
Table-valued UDF are not nearly as bad. In fact, a query using inline
table-valued UDFs will often result in the same execution plan as an
equivalent query that replaces the UDF by incorporating it's logic into
the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks again Hugo for your assistance.
Matt
On Tue, 01 Feb 2005 00:56:48 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Tue, 01 Feb 2005 10:01:25 +1100, Matt Bolton wrote:
>(snip)
>(snip)
>Hi Matt,
>In SP: yes. Not because SP will perform better than queries, but for two
>other reasons:
>1. Security: don't give any user any rights to modify data in any table;
>instead, give them only execute permission on stored procedures that will
>perform the desired modifications in a controleed manner. Or take it a
>step further: don't give any rights to view the data in the tables or
>views at all; give them some more stored procedures to get the requested
>data.
>2. Indirect performance benefit: because you can control the quality of
>the SQL in the procedures and fine-tune your indexes to the contents of
>these procedures, you can gain performance. You'll never have that level
>of control if you allow application programmers or even end users to
>create their own ad-hoc queries.
>
>In UDF: no. Especially scalar UDF's are a performance drag, because they
>ahve to be executed once for each row. A scalar UDF that reads data from a
>table will allways be outperformed by incorporating the logic into the
>query. Of course, there may be other reasons that make the UDF the best
>choice, but you do take the performance hit.
>Table-valued UDF are not nearly as bad. In fact, a query using inline
>table-valued UDFs will often result in the same execution plan as an
>equivalent query that replaces the UDF by incorporating it's logic into
>the query.
>Best, Hugosql

Monday, March 12, 2012

Performance and inter-database joins

We have ALOT of procs with joins of many tables spanning 2-4 databases at
times. Many of these procs are hit HARD during our busiest times. This
seems to me that it would be not the best way to do things. I understand
that sometimes there may be needs to go to other db's for data but shouldn't
that be an exception and not the normal rule?
Myself I'm pretty convinced that we don't have enough reasons to have the 5
different databases we have. They would all fit nicely into one db and
still only be 5-6GB...there are heavy dependencies between any combination
of these databases which seems to tell me they really should be one...
Any thoughts here? Am I concerned about performance unnecessarily? Our
server is running fine but our user base is growing consistently and I'd
like to keep it that way.
Thanks!Tim Greenwood wrote:
> We have ALOT of procs with joins of many tables spanning 2-4 databases at
> times. Many of these procs are hit HARD during our busiest times. This
> seems to me that it would be not the best way to do things. I understand
> that sometimes there may be needs to go to other db's for data but shouldn't
> that be an exception and not the normal rule?
> Myself I'm pretty convinced that we don't have enough reasons to have the 5
> different databases we have. They would all fit nicely into one db and
> still only be 5-6GB...there are heavy dependencies between any combination
> of these databases which seems to tell me they really should be one...
> Any thoughts here? Am I concerned about performance unnecessarily? Our
> server is running fine but our user base is growing consistently and I'd
> like to keep it that way.
> Thanks!
>
There is no performance penalty for cross-database queries, to my
knowledge. Cross-SERVER queries, on the other hand, can suffer
significant penalties.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
>> There is no performance penalty for cross-database queries, to my
>> knowledge.
Unless he tests out his queries and compare them, we cannot be sure. On
complex queries, esp. ones that involve larger underlying datasets, the
performance could be very different due to significant changes in disk I/O.
--
Anith|||> On complex queries, esp. ones that involve larger underlying datasets, the performance could be
> very different due to significant changes in disk I/O.
But that wouldn't be specific to inter-database traffic, right? That would be determined by file
configuration. I.e., one could use filegroups so that the file placement over the tables is the same
as when using several databases and we get the same result.
(I realize this is a bit theoretical, but my point is that the optimizer has the same information
and options whether or not we go across database boundary - assuming in the same instance of
course).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%233O3hvm3GHA.2152@.TK2MSFTNGP06.phx.gbl...
> Tracy,
>> There is no performance penalty for cross-database queries, to my knowledge.
> Unless he tests out his queries and compare them, we cannot be sure. On complex queries, esp. ones
> that involve larger underlying datasets, the performance could be very different due to
> significant changes in disk I/O.
> --
> Anith
>|||>> But that wouldn't be specific to inter-database traffic, right? That
>> would be determined by file configuration. I.e., one could use filegroups
>> so that the file placement over the tables is the same as when using
>> several databases and we get the same result.
Can we have two databases placed on the same filegroup? Otherwise, it would
have to be distinct physical file access. You are right in that one could
have the underlying files/filesgroup spread out similarly, but then it is
hard to prove one way or the other which is why he'll have to test out his
queries and compare them.
>> ..but my point is that the optimizer has the same information and options
>> whether or not we go across database boundary - assuming in the same
>> instance of course
Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential physical I/O information in execution plans, esp. if
the multiple files are distributed over the network or even on external
drives, or am I wrong here?
--
Anith|||> Can we have two databases placed on the same filegroup?
Not unless you go back to 6.5 ;-)
>> ..but my point is that the optimizer has the same information and options whether or not we go
>> across database boundary - assuming in the same instance of course
> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
Hmm, you confuse me a bit here. My original point was the optimizer has the same information
regardless of whether the tables involved are in the same database or are from several databases. At
least, that is how I believe it work. Also, to the best of my knowledge, the optimizer does not
factor disk layout or characteristica when creating an execution plan. Perhaps I should have said:
You can define a database using file groups so you get the same structure as if you had that set of
tables spread over several databases. (Assuming you don't introduce any table partitioning when
spreading over several databases.) If you do end up with a similar file placement of the tables, the
optimizer should produce similar plans.
Above is speculation to some degree. Who knows, perhaps the optimizer will take into account if, for
instance, a table is partitioned over different filegroups compared to the same filegroup (just an
example)?
However, there are more important factors, IMO. Having a related set of tables in the same database
has many advantages, IMO. Backup is only one of them, IMO a major one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u41w4$X4GHA.696@.TK2MSFTNGP06.phx.gbl...
>> But that wouldn't be specific to inter-database traffic, right? That would be determined by file
>> configuration. I.e., one could use filegroups so that the file placement over the tables is the
>> same as when using several databases and we get the same result.
> Can we have two databases placed on the same filegroup? Otherwise, it would have to be distinct
> physical file access. You are right in that one could have the underlying files/filesgroup spread
> out similarly, but then it is hard to prove one way or the other which is why he'll have to test
> out his queries and compare them.
>> ..but my point is that the optimizer has the same information and options whether or not we go
>> across database boundary - assuming in the same instance of course
> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
> --
> Anith
>|||>> Can we have two databases placed on the same filegroup?
>> Not unless you go back to 6.5 ;-)
Somebody kill me.......! Actually I meant a single file, which I assume is
not possible. ( or is it? )
>> ..but my point is that the optimizer has the same information and
>> options whether or not we go across database boundary - assuming in
>> the same instance of course
>> Sure, as far as the query optimizations go, agreed. But it cannot
>> possibly factor in all potential physical I/O information in execution
>> plans, esp. if the multiple files are distributed over the network or
>> even on external drives, or am I wrong here?
>> Hmm, you confuse me a bit here.
I was just emphasising on the fact that physical I/O could be a contributing
factor to performance differences. If the databases are on distinct files
( distributed or otherwise ) then it can contribute to the overall
performance of queries when the underlying implementation access distinct
physical files as opposed to a single one.
However I do appreciate your point. It can be the other around as well.
>> My original point was the optimizer has the same information regardless
>> of whether the tables involved are in the same database or are from
>> several databases. At least, that is how I believe it work. Also, to the
>> best of my knowledge, the optimizer does not factor disk layout or
>> characteristica when creating an execution plan.
>> Perhaps I should have said:
>> You can define a database using file groups so you get the same structure
>> as if you had that set of tables spread over several databases. (Assuming
>> you don't introduce any table partitioning when spreading over several
>> databases.) If you do end up with a similar file placement of the tables,
>> the optimizer should produce similar plans.
Agreed. On the same token if the underlying file placement of the files are
different, the performance could be different as well.
>> Above is speculation to some degree. Who knows, perhaps the optimizer
>> will take into account if, for instance, a table is partitioned over
>> different filegroups compared to the same filegroup (just an example)?
.. which is all the more reason for the OP to test out his queries and see
it for himself.
>> However, there are more important factors, IMO. Having a related set of
>> tables in the same database has many advantages, IMO. Backup is only one
>> of them, IMO a major one.
Indeed
--
Anith|||Thanks for the comments, Anith. Seems we are in agreement here, even if it took a couple of posts to
determine... :-)
On more thing, to answer one of your outstanding questions:
>> Can we have two databases placed on the same filegroup?
>> Not unless you go back to 6.5 ;-)
> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
To be honest, I read your original question as "file". No, you cannot, as of 7.0, share the same
file over several databases. One file is owned by a database (a true subset of the database).
The old architecture was different, where you first created a database device (the file) and then
allocated storage ("segment", similar to a file group) from that file for the database. Thus, you
could end up with two databases using storage from the same file. This model doesn't really add
anything useful in the PC world, especially since we don't tend to use RAW devices. So, I'm glad MS
made the storage architecture much cleaner and simpler in the new architecture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWVx4mY4GHA.292@.TK2MSFTNGP02.phx.gbl...
>> Can we have two databases placed on the same filegroup?
>> Not unless you go back to 6.5 ;-)
> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
>>> ..but my point is that the optimizer has the same information and options whether or not we
>>> go across database boundary - assuming in the same instance of course
>> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all
>> potential physical I/O information in execution plans, esp. if the multiple files are
>> distributed over the network or even on external drives, or am I wrong here?
>> Hmm, you confuse me a bit here.
> I was just emphasising on the fact that physical I/O could be a contributing factor to performance
> differences. If the databases are on distinct files ( distributed or otherwise ) then it can
> contribute to the overall performance of queries when the underlying implementation access
> distinct physical files as opposed to a single one.
> However I do appreciate your point. It can be the other around as well.
>> My original point was the optimizer has the same information regardless of whether the tables
>> involved are in the same database or are from several databases. At least, that is how I
>> believe it work. Also, to the best of my knowledge, the optimizer does not factor disk layout or
>> characteristica when creating an execution plan.
>> Perhaps I should have said:
>> You can define a database using file groups so you get the same structure as if you had that set
>> of tables spread over several databases. (Assuming you don't introduce any table partitioning
>> when spreading over several databases.) If you do end up with a similar file placement of the
>> tables, the optimizer should produce similar plans.
> Agreed. On the same token if the underlying file placement of the files are different, the
> performance could be different as well.
>> Above is speculation to some degree. Who knows, perhaps the optimizer will take into account if,
>> for instance, a table is partitioned over different filegroups compared to the same filegroup
>> (just an example)?
> .. which is all the more reason for the OP to test out his queries and see it for himself.
>> However, there are more important factors, IMO. Having a related set of tables in the same
>> database has many advantages, IMO. Backup is only one of them, IMO a major one.
> Indeed
> --
> Anith
>

Performance and inter-database joins

Friday, March 9, 2012

Performance - Left Outer Joins VS Updates

We currently are in a debate on what is quicker, doing a query in a SP
that has Nth left outer joins or doing a insert followed by update
queries. let me lay out a simple scenerio. Please advise, the DBA
group is set on saying example 2 is better (they keep telling me Im
doing too many table scans).
Table "Store" has 1 Record
StoreNum: 1
Table "StoreDate" has many records that are associated to the Store
table; we will say 3 records fo this example
StoreNum: 1
DateType:1
DateValue: 1/1/2001
StoreNum: 1
DateType:2
DateValue: 2/2/2002
StoreNum: 1
DateType:3
DateValue: 3/3/2003
EXAMPLE 1
Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)
Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, SD1.DateValue As OpenDate, SD2.DateValue As
CloseDate, SD3.DateValue As LastInspectionDate
>From Store S Left Outer Join StoreDate SD1 ON
S.StoreNum = SD1.StoreNum And SD1.DateType = 1 Left Outer Join
StoreDate SD2 ON
S.StoreNum = SD2.StoreNum And SD2.DateType = 2 Left Outer Join
StoreDate SD3 ON
S.StoreNum = SD3.StoreNum And SD3.DateType = 3
EXAMPLE 2
Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)
Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, null As OpenDate, null As CloseDate, null As
LastInspectionDate
>From Store S
Update X
set opendate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 1
Update X
set closedate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 2
Update X
set lastinspectiondate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 3Did you test it for yourself with SET STATISTICS IO ON?
You could easily improve Ex 2 by combining the three UPDATEs but I
would try something different from either solution:
INSERT INTO X (storenum, opendate, closedate, lastinspectiondate)
SELECT storenum,
MAX(CASE WHEN datetype = 1 THEN datevalue END),
MAX(CASE WHEN datetype = 2 THEN datevalue END),
MAX(CASE WHEN datetype = 3 THEN datevalue END)
FROM Store
WHERE datetype BETWEEN 1 AND 3
GROUP BY storenum ;
David Portas
SQL Server MVP
--|||The examples are not equivalent. The first example will insert the number
of rows in Store, leaving existing rows in the inserted table unaffected.
The second example will insert the same number of rows, but will update
EVERY row in the inserted table (that joins successfully). I can't imagine
that the insert/update logic is "superior" in any way. Is there an
assumption that the inserted table is empty?
I'll ignore the potential logic flaws based on the design assumptions (e.g.,
store has a 1:1 relationship with storedate for rows where type = 3). The
3rd datetime column named "LastInspectionDate" seems to contradict the
assumed 1:1 relationship.
As David indicated, you don't even need to join store to storedate to
generate the correct information (unless there is some "irregular" aspect to
your design/schema that was not mentioned).

Performance - Joins vs Filters

Hi
I reckon this is a "how long's a piece of string"-type of question but I'll
try it anyway. If you could provide any pointers, even if it is not a direct
answer then I'd be really grateful.
I've written an app generates SQL. I'm joining many tables and it's stable.
However, I now need to enhance it some more and link in another table. I
have the option of extending the WHERE clause instead of modifying the
joining mechanism in the FROM clause. Extending the WHERE clause means
adding a subselect and the way to do this is *far* easier to implement than
to rework the joining mechanisms to include an extra table - most especially
for Left Joins.
So my preference would be just extend the filter but I'm not sure about the
impact on performance. Will left joining from the additional table (and
extending the filter) be significantly faster than SubSelecting from it and
using an IN?
Thanks
Simonit depends. You need to do your own benchmarks|||Recently, I developed a data warehouse and in terms of SQL performance tips
etc. I was a complete novice. I did alot of investigation and research into
the fastest way to query and I found that subselects in general were a big
performance hit. I found by creating intermediate tables that I could join
into other queries that performance was greatly enhanced. Of course this is
subjective to the scenario and I imagine there are plenty of exceptions, why
dont you try both and find out?
"Simon Woods" wrote:

> Hi
> I reckon this is a "how long's a piece of string"-type of question but I'l
l
> try it anyway. If you could provide any pointers, even if it is not a dire
ct
> answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's stable
.
> However, I now need to enhance it some more and link in another table. I
> have the option of extending the WHERE clause instead of modifying the
> joining mechanism in the FROM clause. Extending the WHERE clause means
> adding a subselect and the way to do this is *far* easier to implement tha
n
> to rework the joining mechanisms to include an extra table - most especial
ly
> for Left Joins.
> So my preference would be just extend the filter but I'm not sure about th
e
> impact on performance. Will left joining from the additional table (and
> extending the filter) be significantly faster than SubSelecting from it an
d
> using an IN?
> Thanks
> Simon
>
>|||Simon,
Why don't you find out instead of guess? Type the two
possible queries you're considering into query analyzer
and either compare their execution plans or test them on
sample data.
If for some reason you can't test the queries you're considering,
and want more advice here, you'll have better luck if you
post specific queries along with the relevant CREATE TABLE
statements and some sample data.
Steve Kass
Drew University
Simon Woods wrote:

>Hi
>I reckon this is a "how long's a piece of string"-type of question but I'll
>try it anyway. If you could provide any pointers, even if it is not a direc
t
>answer then I'd be really grateful.
>I've written an app generates SQL. I'm joining many tables and it's stable.
>However, I now need to enhance it some more and link in another table. I
>have the option of extending the WHERE clause instead of modifying the
>joining mechanism in the FROM clause. Extending the WHERE clause means
>adding a subselect and the way to do this is *far* easier to implement than
>to rework the joining mechanisms to include an extra table - most especiall
y
>for Left Joins.
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
>Thanks
>Simon
>
>|||Well, first question in my mind is "do you need any of this data for
output?" If yes, then join, if no, then where clause. If it is too slow,
then optimize.
If you are just filtering data, then an exists in the where should be
faster, it will certainly express what you are trying to do in a more
correct manner.
And as everyone else has stated, test it out :)
----
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)
"Simon Woods" <simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote in message
news:eQ94X$tEGHA.4036@.TK2MSFTNGP09.phx.gbl...
> Hi
> I reckon this is a "how long's a piece of string"-type of question but
> I'll try it anyway. If you could provide any pointers, even if it is not a
> direct answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's
> stable. However, I now need to enhance it some more and link in another
> table. I have the option of extending the WHERE clause instead of
> modifying the joining mechanism in the FROM clause. Extending the WHERE
> clause means adding a subselect and the way to do this is *far* easier to
> implement than to rework the joining mechanisms to include an extra
> table - most especially for Left Joins.
> So my preference would be just extend the filter but I'm not sure about
> the impact on performance. Will left joining from the additional table
> (and extending the filter) be significantly faster than SubSelecting from
> it and using an IN?
> Thanks
> Simon
>|||On Fri, 6 Jan 2006 16:40:02 -0000, "Simon Woods"
<simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote:
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
If you're very lucky, the optimizer will turn out exactly the same
code for any of the top three or four ways to code it.
Actually, it's pretty common to see that.
J.|||Hi Simon
can't argue with Alexander and Steve's recommendation of "try both and
find out which is better"!
However, my experience (10 years) is that subselects are almost always
less efficient than joins. I never use them now, and I'm pleasantly
surprised again and again at how SQL Server can gobble up the most
evil-looking multiple join operations and flip the results back in
seconds.
The di

vantage of joins as you say is that they can take quite a bit
of work to get right.
try out the subselect with some (sufficiently large set of) sample
data, I reckon.
cheers
Seb

Wednesday, March 7, 2012

Performanc of joins betwen two dbs

What are the performance rammifactions of joining two or more tables in one
db with two or more table in another db vs having all the tables in the same
db'
Barry FitzgeraldShould not be any difference (unless you go between servers/instances, of
course. But there are other reasons as to why you want to avoid splitting
into several database; backup is one.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Barry Fitzgerald" <barryfz@.home.com> wrote in message
news:uYneHQrqDHA.1880@.TK2MSFTNGP09.phx.gbl...
> What are the performance rammifactions of joining two or more tables in
one
> db with two or more table in another db vs having all the tables in the
same
> db'
> Barry Fitzgerald
>|||Hi Barry
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
As I understand£¬for the database level, there is no obvious performance
difference between joining two or more tables in one database with two or
more table in another database and in the same database.
However, if you carry out any distributed query, that is a query on joined
tables in different servers (Linked Server), many factors will affect the
query. For example, the execute plan, the server load, the network, memory,
IO, CPU, etc.
If you could provide why you want to carried out you plan, I can explained
more clearly.
You can also test the query performance base on your own environment to
find which factor will cause the performance difference.
If you need more help, please fill free to contact me.
Sincerely Yours
Wei,Baisong
--
| From: "Barry Fitzgerald" <barryfz@.home.com>
| Subject: Performanc of joins betwen two dbs
| Date: Fri, 14 Nov 2003 07:34:27 -0600
| Lines: 7
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <uYneHQrqDHA.1880@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchangetest.gumdropbooks.com 209.152.94.98
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316543
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| What are the performance rammifactions of joining two or more tables in
one
| db with two or more table in another db vs having all the tables in the
same
| db'
|
| Barry Fitzgerald
|
|
||||Hello Barry,
Taking about the backup/recovery, If the logically related tables are
spread across different databases, then those databases must be
logically consistent at any given time and you may need to implement
special procedures to ensure the recoverability of these databases.
Please look at "Backup and Recovery of Related Databases" topic in the
SQL2K Books On Line for more information
(BOL URL >
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_bkprst_9ttf.htm)
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security."
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Barry Fitzgerald" <barryfz@.home.com>
>Subject: Performanc of joins betwen two dbs
>Date: Fri, 14 Nov 2003 07:34:27 -0600
>Lines: 7
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <uYneHQrqDHA.1880@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: exchangetest.gumdropbooks.com 209.152.94.98
>Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316543
>X-Tomcat-NG: microsoft.public.sqlserver.server
>What are the performance rammifactions of joining two or more tables in one
>db with two or more table in another db vs having all the tables in the
same
>db'
>Barry Fitzgerald
>
>

Wednesday, March 21, 2012

Performance cost of joins

Performance cost of joins

Performance cost of joins

Performance considerations UDF vs Joins

Monday, March 12, 2012

Performance and inter-database joins

Performance and inter-database joins

Performance and inter-database joins

Friday, March 9, 2012

Performance - Left Outer Joins VS Updates

Performance - Joins vs Filters

Wednesday, March 7, 2012

Performanc of joins betwen two dbs

Percentage of total

Blog Archive

About Me