Joins Vs Where clause - Performance Query

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ??

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )

USING A WHERE CLAUSE and Alias
--------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

------------
It pays to be honest to your DBAmy experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.

If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.

Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.|||Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).

and avoid using AliasesNope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not. :)

I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
http://www.sql-server-performance.com/articles_performance.asp

HTH|||avoiding aliases is not "reasonable"

:)|||Some people think aliases make code easier to read, blindman does not. :)
My reputation preceeds me.
But even I don't claim the aliases hurt performance.

Joins vs SubQueries

Hi,

Can any one please let me know which one is better in performance, Joins or sub-queries?

Any other differences between joins and sub-queries plz let me know.

Thanks

Pradeep

pradeepyr:

I would suggest that the best way to answer your question is to take quick benchmarks whenever you have doubt. In general (1) look at the execution plan, (2) query execution time and (3) query IO statistics and judge based on this information. This is all information that is readily obtainable from the Query Analyzer in SQL Server 2000 and SQL Server Management Studio in SQL Server 2005.

Dave

|||

joins and subqueries solve different purposes, and cant/may not be replaced just for the sake of it...

**use a subquery when u may not need the column from the table used the subquery in the result set of outer query.....

join is usually easy to optimize than a subquery... as join joins the whole tables based on a condition, use it when u retrive more data....

all said if u have a confusion, and choice of using both,(both givin same result), check the query plans, and decide, or simply go for joins , excluding the one condition i mentioned**..

Joins Position

Hi
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
LalitCan you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>

Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation â'
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
-----
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> -----
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in message
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>> Hello,
>> It is taking too long to run the following query:
>> Note: I have indexes on all of the columns in conditions. My temp db
>> size
>> is 18 GB. Pds_txn table size is 165 GB.
>> The execution plan showing:
>> Table pool/easer spool operation -
>> Row Count: 9 M
>> Disk i/o: 11k
>> Row size: 1089
>> Estimated cost: 11 k (57%)
>> CPU cost: 3.3
>> Sub tree cost: 20 K
>> Any help/hint will be appreciated.
>> Thanks,
>> Alim
>> -----
>>
>> FROM
>> dbo.pds_txn T1
>> INNER JOIN
>> dbo.GROUPS T2 ON
>> T1.GROUP_ID = T2.ID_200
>> INNER JOIN
>> dbo.DIVISIONS T3 ON
>> T1.DIVISION = T3.ID_102
>> INNER JOIN
>> dbo.BILLING_AREAS T4 ON
>> T1.BILLING_AREA = T4.ID_202
>> INNER JOIN
>> dbo.PROVIDERS T6 ON
>> T1.PROVIDER = T6.ID_3
>> INNER JOIN
>> dbo.LOCATIONS T7 ON
>> T1.LOCATION = T7.ID_100
>> INNER JOIN
>> dbo.PROCEDURES T8 ON
>> T1.[PROCEDURE] = T8.ID_1
>> INNER JOIN
>> dbo.FSC T9 ON
>> T1.ORIG_FSC = T9.ID_19
>> INNER JOIN
>> dbo.DIAGNOSIS T10 ON
>> T1.TXN_DX_1 = T10.ID_36
>> INNER JOIN
>> dbo.pds_invoice T11 ON
>> T1.INVOICE_NUM = T11.INVOICE_NUM AND
>> T1.GROUP_ID = T11.GROUP_ID
>> LEFT OUTER JOIN
>> dbo.PROVIDERS T6A ON
>> T11.PERFORMING_PHYS = T6A.ID_3
>> WHERE
>> T1.POSTING_PD_DTE >= '05/01/2003' AND
>> T1.PAY_CODE = 21 AND
>> T2.EXCLUSION_FLAG = 0 AND
>> T3.DIV_NUM <> '2901'
>>
>

Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation –
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
----
--
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> ----
--
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in messa
ge
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>

Joins and performance

Can anyone tell me what type of hardware upgrade will make the biggest
different in join performance?
thanks,
Aaron
"Aaron Prohaska" <REMOVE_THISmohaaron@.gmail.com> wrote in message
news:%23rhBA4usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> Can anyone tell me what type of hardware upgrade will make the biggest
> different in join performance?
>
Before going further, I'll warn you that query analysis, tuning and
application design have a much greater potential to increase performance
than hardware.
Keeping that in mind, there are a couple of things to look at.
First, there are 2 kinds of databases. Those whith enough ram to cache the
entire active portion of the database, call these memory-sized databases.
And servers whose active database is much larger than the server's ram, call
these larger-than-memory-sized databases.
So a couple of factors:
-A memory-sized database is much faster than a larger-than-memory-sized
server.
-Configuring and tuning a memory-sized database is vastly easier.
-A memory-sized database scales very will with increases in CPU speed and
number.
These factors are what's driving database servers to 64bit platforms, where
you can throw gobs of memory at them. Anyway this means: If you have have a
memory-sized database server by adding ram, do it.
Anyway, for a larger-than-memory-sized database server, you have to consider
the details of the server workload, and consider how much physical IO will
be occuring during your server's workload. If your queries require physical
IO-intensive scans, you might benefit from faster disks, cleverly deployed
in fast RAID configurations.
David

Joins and performance

Can anyone tell me what type of hardware upgrade will make the biggest
different in join performance?
thanks,
Aaron"Aaron Prohaska" <REMOVE_THISmohaaron@.gmail.com> wrote in message
news:%23rhBA4usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> Can anyone tell me what type of hardware upgrade will make the biggest
> different in join performance?
>
Before going further, I'll warn you that query analysis, tuning and
application design have a much greater potential to increase performance
than hardware.
Keeping that in mind, there are a couple of things to look at.
First, there are 2 kinds of databases. Those whith enough ram to cache the
entire active portion of the database, call these memory-sized databases.
And servers whose active database is much larger than the server's ram, call
these larger-than-memory-sized databases.
So a couple of factors:
-A memory-sized database is much faster than a larger-than-memory-sized
server.
-Configuring and tuning a memory-sized database is vastly easier.
-A memory-sized database scales very will with increases in CPU speed and
number.
These factors are what's driving database servers to 64bit platforms, where
you can throw gobs of memory at them. Anyway this means: If you have have a
memory-sized database server by adding ram, do it.
Anyway, for a larger-than-memory-sized database server, you have to consider
the details of the server workload, and consider how much physical IO will
be occuring during your server's workload. If your queries require physical
IO-intensive scans, you might benefit from faster disks, cleverly deployed
in fast RAID configurations.
Davidsql

Joins and performance

Can anyone tell me what type of hardware upgrade will make the biggest
different in join performance?
thanks,
Aaron"Aaron Prohaska" <REMOVE_THISmohaaron@.gmail.com> wrote in message
news:%23rhBA4usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> Can anyone tell me what type of hardware upgrade will make the biggest
> different in join performance?
>
Before going further, I'll warn you that query analysis, tuning and
application design have a much greater potential to increase performance
than hardware.
Keeping that in mind, there are a couple of things to look at.
First, there are 2 kinds of databases. Those whith enough ram to cache the
entire active portion of the database, call these memory-sized databases.
And servers whose active database is much larger than the server's ram, call
these larger-than-memory-sized databases.
So a couple of factors:
-A memory-sized database is much faster than a larger-than-memory-sized
server.
-Configuring and tuning a memory-sized database is vastly easier.
-A memory-sized database scales very will with increases in CPU speed and
number.
These factors are what's driving database servers to 64bit platforms, where
you can throw gobs of memory at them. Anyway this means: If you have have a
memory-sized database server by adding ram, do it.
Anyway, for a larger-than-memory-sized database server, you have to consider
the details of the server workload, and consider how much physical IO will
be occuring during your server's workload. If your queries require physical
IO-intensive scans, you might benefit from faster disks, cleverly deployed
in fast RAID configurations.
David

Joining Views & Query Performance

Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.

A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.

Thanks, Davequerying a query that queries a query that queries yet another query. yeah that might make query processor a little pissy. Views use indexes and statistics. Have you looked at the query execution plan? Could just be a couple of bad scans.

I discourage nested views among my developers. They say "code reuse". I call them lazy.|||The execution plan looks like a map of Illinois listing the location of every McDonald's. In other words it's full of icons. As for the use of indexes, I don't believe these views meet the criteria, especially the one indicating "The view must not reference any other views, only base tables."

I suggested they rewrite the code to not use so many views.|||yeah the views may be doing a lot of extra junk you just do not need.|||...As for the use of indexes, I don't believe these views meet the criteria,...I don't think Thrasy was referring to "indexed views", but to the fact the views can make use of indexes on their source tables.|||Ok. Either way the execution plan was so large it wasn't worth the effort of debugging. Do you know of any documentation indicating to avoid using nested views? I would like to pass it along to our developers, assuming I can find anything.

Dave|||Hmmm...pity there isn't some sort of public bulletin board, or forum, monitored by experienced and knowledgeable experts to which you could direct them. :(

JOINing ORDER and Performance

Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay t
he same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics sta
y the same. You might
see a small difference, since optimizer has "early out" strategies, but if y
ou do see a big
difference, you have found a weakness in the optimizer and MS would like to
know about it
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more co
lumns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materia
lized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index
is a non-clustered
index containing all the information that a query need from a table. This wa
y, SQL Server don't have
to access each page for each row, all information is already in the non-clus
tered index. This can
make a huge performance difference.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>It shouldn't make a difference, as long as the semantics of the query stay
the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics st
ay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if
you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know ab
out it
Ha.
J.|||> Ha.
Get your point... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.
4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Ha.
> J.
>

JOINing ORDER and Performance

Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
see a small difference, since optimizer has "early out" strategies, but if you do see a big
difference, you have found a weakness in the optimizer and MS would like to know about it
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more columns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materialized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index is a non-clustered
index containing all the information that a query need from a table. This way, SQL Server don't have
to access each page for each row, all information is already in the non-clustered index. This can
make a huge performance difference.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.|||> Ha.
Get your point... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>It shouldn't make a difference, as long as the semantics of the query stay the same (consider
>>outer
>>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>>difference, you have found a weakness in the optimizer and MS would like to know about it
> Ha.
> J.
>

JOINing ORDER and Performance

Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
Dutt
OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...
|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.

joining a varchar with a int column

just noticed this, wonder if there is a performance hit
got results when executing the following
select AgendaID
from ObjectiveAgenda
Inner Join Objective On Objective.ObjectiveID =
ObjectiveAgenda.ObjectiveID
problem is that the ObjectiveID column in one table is of SQL type
INTEGER, while in the other table is of type VARCHAR.
wonder if it is doing a translation behind the scenes, with some
performance hit.Yes, SQL Server will have to convert the value and this can cause table scan
.
Thus this can cause major negative impact.
Here is a script I have used for testing. In the Query Analyser, please
'Display Execution Plan' for the 2 queries at the end of my script. One will
cause Table Scan and another will go for Index S.
set nocount on
create table #test111
(scode int,
sdesc varchar(30))
create index idx1_test111
on #test111(sdesc)
declare @.val1 int
set @.val1 = 1
while @.val1 < 10000
begin
insert into #test111
values(@.val1, convert(varchar(30),(@.val1 * @.val1)))
set @.val1 = @.val1 + 1
end
select * from #test111 where sdesc = 25
go
select * from #test111 where sdesc = '25'
"arzewski@.hotmail.com" wrote:

> just noticed this, wonder if there is a performance hit
> got results when executing the following
> select AgendaID
> from ObjectiveAgenda
> Inner Join Objective On Objective.ObjectiveID =
> ObjectiveAgenda.ObjectiveID
> problem is that the ObjectiveID column in one table is of SQL type
> INTEGER, while in the other table is of type VARCHAR.
> wonder if it is doing a translation behind the scenes, with some
> performance hit.
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||Użytkownik Tibor Karaszi napisaÅ?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> Użytkownik Tibor Karaszi napisaÅ?:
>> 1 is same as 2. The optimizer has all the information and can process the query the same whether
>> the tables are in the same database or different database. For 3 and 4, the query is optimized
>> locally and parts of the query is passed onto the linked server. This limits the flexibility that
>> the optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test
>> with your data, schema, queries etc.
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the qu
ery the same whether the
tables are in the same database or different database. For 3 and 4, the quer
y is optimized locally
and parts of the query is passed onto the linked server. This limits the fle
xibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, yo
u need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on different physical machines and these machi
nes contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||
> It is really opene-end question , because only you do know about your tabl
es
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
That really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
It is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||
> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin
|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin
|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinThat really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin

Join vs Where performance

Consider the following hypothetical queries:
QUERY A
Select ...
>From TableA TA Join TableB TB
ON TA.Field1 = TB.Field1 AND
TA.Field2 = TB.Field2 AND
TA.Field3 = TB.Field3
Where
TA.Field4 = 'Some Value'
QUERY B
Select ...
>From TableA TA Join TableB TB
ON TA.Field1 = TB.Field1
Where
TA.Field4 = 'Some Value' AND
TA.Field2 = TB.Field2 AND
TA.Field3 = TB.Field3
Notice that the difference is that the Join clause in Query A has more
than one expression to evaluate but in Query B, the Join only has a
single expression and the other expressions have been moved to the
Where clause.
These queries should return the same results but my question is about
performance. What are the best practices regarding Joins vs the Where
clause? Do these queries have significantly differing performance?
Which is preferred and why?
Can someone point me to some articles or books that would help me to
under stand?
ThanksChris,
I don't think you will see any performance differences (generally) between
either method (INNER JOINS). I generally prefer to use JOINs for the join
criteria and WHERE for filtering. Using JOIN can help reduce the number of
cross joins when the WHERE clause is ommitted. Also, ANSI compliance when
using LEFT or RIGHT as opposed to *= or =*.
HTH
Jerry
"Chris Dunaway" <dunawayc@.gmail.com> wrote in message
news:1129744620.635243.276050@.o13g2000cwo.googlegroups.com...
> Consider the following hypothetical queries:
> QUERY A
> Select ...
> ON TA.Field1 = TB.Field1 AND
> TA.Field2 = TB.Field2 AND
> TA.Field3 = TB.Field3
> Where
> TA.Field4 = 'Some Value'
>
> QUERY B
> Select ...
> ON TA.Field1 = TB.Field1
> Where
> TA.Field4 = 'Some Value' AND
> TA.Field2 = TB.Field2 AND
> TA.Field3 = TB.Field3
>
> Notice that the difference is that the Join clause in Query A has more
> than one expression to evaluate but in Query B, the Join only has a
> single expression and the other expressions have been moved to the
> Where clause.
> These queries should return the same results but my question is about
> performance. What are the best practices regarding Joins vs the Where
> clause? Do these queries have significantly differing performance?
> Which is preferred and why?
> Can someone point me to some articles or books that would help me to
> under stand?
> Thanks
>|||hit CTRL + K
run both queries and look if the execution plans are different
http://sqlservercode.blogspot.com/
"Chris Dunaway" wrote:

> Consider the following hypothetical queries:
> QUERY A
> Select ...
> ON TA.Field1 = TB.Field1 AND
> TA.Field2 = TB.Field2 AND
> TA.Field3 = TB.Field3
> Where
> TA.Field4 = 'Some Value'
>
> QUERY B
> Select ...
> ON TA.Field1 = TB.Field1
> Where
> TA.Field4 = 'Some Value' AND
> TA.Field2 = TB.Field2 AND
> TA.Field3 = TB.Field3
>
> Notice that the difference is that the Join clause in Query A has more
> than one expression to evaluate but in Query B, the Join only has a
> single expression and the other expressions have been moved to the
> Where clause.
> These queries should return the same results but my question is about
> performance. What are the best practices regarding Joins vs the Where
> clause? Do these queries have significantly differing performance?
> Which is preferred and why?
> Can someone point me to some articles or books that would help me to
> under stand?
> Thanks
>|||I have done that and, being inexperienced in T-SQL, I do not fully
understand what the execution plans are telling me. My question was
more general than specific. The hypothetical queries I provided may
perform equivalently. I am more interested in best practices. In
general, when joining two table using JOIN, is it advantageous to have
more than one join expression? Also, would you ever put a constant
value in a join expression like this:
Select ...
>From TableA TA Join TableB TB
ON TA.Field1 = TB.Field1 AND
TA.Field2 = TB.Field2 AND
TA.Field3 = TB.Field3 AND
TA.Field5 = 'const expression'
Where
TA.Field4 = 'Some Value'
I want to make my queries as fast as possible (don't we all?) and I
want to make sure that I use practices that others have found to
reliably produce better results.
Can you recommend any books that will help me to learn more about how
Queries are optimized in SQL Server?
Thanks again.|||Inside Microsoft SQL Server 2000
by Kalen Delaney
http://www.amazon.com/exec/obidos/A...link%5Fcode=xm2
One of the best books
"Chris Dunaway" wrote:

> I have done that and, being inexperienced in T-SQL, I do not fully
> understand what the execution plans are telling me. My question was
> more general than specific. The hypothetical queries I provided may
> perform equivalently. I am more interested in best practices. In
> general, when joining two table using JOIN, is it advantageous to have
> more than one join expression? Also, would you ever put a constant
> value in a join expression like this:
> Select ...
> ON TA.Field1 = TB.Field1 AND
> TA.Field2 = TB.Field2 AND
> TA.Field3 = TB.Field3 AND
> TA.Field5 = 'const expression'
> Where
> TA.Field4 = 'Some Value'
> I want to make my queries as fast as possible (don't we all?) and I
> want to make sure that I use practices that others have found to
> reliably produce better results.
> Can you recommend any books that will help me to learn more about how
> Queries are optimized in SQL Server?
> Thanks again.
>|||IMO, your best performance will be realized if have built the appropriate
indexes on your tables.
I generally stay away from compound keys unless they are used specifically
for sorting.
When MS bought Fox Software they incorporated some of the technology known
then as Rushmore into SQL Server. Rushmore relied almost exclusively on
index schemes for speed.
If you have indexes build on the fields that will be most commonly used in
queries and join conditions, you will see better performance than if the
indexes did not exist. However, if the tables are small, you may not see
any improvement at all.
HTH,
-Steve-|||Hi Chris,
There is no significant performance difference between the two. You can
compare the query plans to check if they are the same (no knowledge of
query plans is required for that). If the are the same, then execution
(and performance) will be the same.
You ask for best practices. To me, the best way to write a query is to
make the query easy to read, easy to maintain, but still resulting in
efficient execution. In that order (performance last).
IMO, the best practice is to only specify the joining columns in the ON
clause, and all filtering expressions in the WHERE clause. Typically,
this means that you only mention the foreign key column(s) in the ON
clause.
Of course, this only applies to inner joins. For outer joins the
functional difference will determine which expressions are placed in the
ON clause and which in the WHERE clause.
Gert-Jan
Chris Dunaway wrote:
> Consider the following hypothetical queries:
> QUERY A
> Select ...
> ON TA.Field1 = TB.Field1 AND
> TA.Field2 = TB.Field2 AND
> TA.Field3 = TB.Field3
> Where
> TA.Field4 = 'Some Value'
> QUERY B
> Select ...
> ON TA.Field1 = TB.Field1
> Where
> TA.Field4 = 'Some Value' AND
> TA.Field2 = TB.Field2 AND
> TA.Field3 = TB.Field3
> Notice that the difference is that the Join clause in Query A has more
> than one expression to evaluate but in Query B, the Join only has a
> single expression and the other expressions have been moved to the
> Where clause.
> These queries should return the same results but my question is about
> performance. What are the best practices regarding Joins vs the Where
> clause? Do these queries have significantly differing performance?
> Which is preferred and why?
> Can someone point me to some articles or books that would help me to
> under stand?
> Thanks

Join Styles

Hi,
Are there any difference(in performance) between these two type of join:
SELECT Customers.CustomerID,orders.orderid FROM Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
&
select c.customerid,o.orderid from customers c,orders o where
c.customerid=o.customerid
I tried them many times while STATISTICS IO/TIME were set on and couldn't
find considerable difference.
Thanks,
Leila
Leila...
These are simply two different ANSI standards. One is older than the other
one. In general SQL Server will build the exact same execution plan for
either of them. The only difference between the two is when there are OUTER
JOINS. The newer method of using the JOIN keyword is preferred and is part
of the SQL 99 standard.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||I find the INNER JOIN syntax more readable, it also allows you to separate
join criteria from filter criteria.
Try as you might, for inner joins with simple join criteria, I don't think
you'll ever see performance differences, because internally the engine sees
them as equivalent. Now if you have complex join and filter criteria, you
might be able to see differences if the filter takes place in the join as
opposed to after.
http://www.aspfaq.com/
(Reverse address to reply.)
"Leila" <leilas@.hotpop.com> wrote in message
news:#48398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||Leila:
The ANSI style (INNER JOIN) is better because for complex queries, the
optimizer can more easily select a better execution plan than for the old
style query; you won't find differences in simple queries though.
You might as well stop cross-posting.
Regards,
Eric Garza
AMIGE
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||http://www.microsoft.com/sql/techinf...ent/july23.asp
AMB
"Leila" wrote:

> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>