Using JOINS improve performance?

Using JOINS improve performance?

Post by Se » Sat, 30 Nov 2002 20:31:55



Hi,
          I'm a beginner in SQL Programming and i need to know whether
usage of SQL Joins will improve performance of my queries. I Used the
following queries that i find difference in Execution Time:

Query 1:
--------
SELECT A.Field1, A.Field2, A.Field3 FROM Table1 A
INNER JOIN Table2 B On A.[FieldX] = C.[FieldX] And C.FieldX = '101'

Query 2:
---------
SELECT A.Field1, A.Field2, A.Field3  FROM Table1 A
WHERE A.[FieldX] in ( Select FieldX from Table2 where FieldX = '101')

No.Of Records: 70000
Query1 Execution Time : 10 Seconds
Query2 Execution Time :14 Seconds

Does this mean that i should always go for Joins instead of nested
queries?
Please help me on this.
Thanks in Advance.

Regards
Sen.

 
 
 

Using JOINS improve performance?

Post by Bas » Sat, 30 Nov 2002 21:03:58



Quote:> Hi,
>           I'm a beginner in SQL Programming and i need to know whether
> usage of SQL Joins will improve performance of my queries. I Used the
> following queries that i find difference in Execution Time:
[snip]
> No.Of Records: 70000
> Query1 Execution Time : 10 Seconds
> Query2 Execution Time :14 Seconds

> Does this mean that i should always go for Joins instead of nested
> queries?

yes! they're also much easier to make with, for example in SQL server, the
graphical design view.

good luck,

Bas

 
 
 

Using JOINS improve performance?

Post by Allan Mitchel » Sat, 30 Nov 2002 21:06:17


Have a look at the execution plans.
I would go with the JOINS everytime as it should involve less overhead.

--

Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org


Quote:> Hi,
>           I'm a beginner in SQL Programming and i need to know whether
> usage of SQL Joins will improve performance of my queries. I Used the
> following queries that i find difference in Execution Time:

> Query 1:
> --------
> SELECT A.Field1, A.Field2, A.Field3 FROM Table1 A
> INNER JOIN Table2 B On A.[FieldX] = C.[FieldX] And C.FieldX = '101'

> Query 2:
> ---------
> SELECT A.Field1, A.Field2, A.Field3  FROM Table1 A
> WHERE A.[FieldX] in ( Select FieldX from Table2 where FieldX = '101')

> No.Of Records: 70000
> Query1 Execution Time : 10 Seconds
> Query2 Execution Time :14 Seconds

> Does this mean that i should always go for Joins instead of nested
> queries?
> Please help me on this.
> Thanks in Advance.

> Regards
> Sen.

 
 
 

Using JOINS improve performance?

Post by Bas » Sat, 30 Nov 2002 21:08:21


and, on closer look, i'd use
WHERE C.FieldX = '101'
instead of And C.FieldX = '101'


Quote:> Hi,
>           I'm a beginner in SQL Programming and i need to know whether
> usage of SQL Joins will improve performance of my queries. I Used the
> following queries that i find difference in Execution Time:

> Query 1:
> --------
> SELECT A.Field1, A.Field2, A.Field3 FROM Table1 A
> INNER JOIN Table2 B On A.[FieldX] = C.[FieldX] And C.FieldX = '101'

> Query 2:
> ---------
> SELECT A.Field1, A.Field2, A.Field3  FROM Table1 A
> WHERE A.[FieldX] in ( Select FieldX from Table2 where FieldX = '101')

> No.Of Records: 70000
> Query1 Execution Time : 10 Seconds
> Query2 Execution Time :14 Seconds

> Does this mean that i should always go for Joins instead of nested
> queries?
> Please help me on this.
> Thanks in Advance.

> Regards
> Sen.

 
 
 

Using JOINS improve performance?

Post by Lorenzo Benagli » Sat, 30 Nov 2002 21:11:02



Hi Sen,

Quote:> Does this mean that i should always go for Joins instead of nested
> queries?

Right, generally Joins are fastest compared to nested queries.
You can use the Query Analyzer to view the execution plans of  both queries
and choose the best solution.
Anyway, I suggest you to separate Join conditions from search arguments
(SARG) to improve the query readability :

Quote:> Query 1:
> --------
> SELECT A.Field1, A.Field2, A.Field3 FROM Table1 A
> INNER JOIN Table2 B On A.[FieldX] = C.[FieldX] And C.FieldX = '101'

SELECT A.Field1, A.Field2, A.Field3 FROM Table1 A
INNER JOIN Table2 B On A.[FieldX] = C.[FieldX]
WHERE C.FieldX = '101'

Moreover take a look on indexes on BOL. You can achieve best results
indexing join fields and SARGs.

Bye,

--
Lorenzo Benaglia
UGIdotNET - http://www.ugidotnet.org
UGISS - http://www.ugiss.org

 
 
 

Using JOINS improve performance?

Post by Greg Linwoo » Sat, 30 Nov 2002 22:16:09


Hi Allan..

I would also suggest joins, but to make the query more readable. Why
<should> the join method involve less overhead? I'd be expecting the
optimizer to flatten both statements into the same plan. Is there any
functional difference between the two queries? (apart from that Query 1
doesn't make sense anyway, because the C Alias is not declared).

Greg Linwood


> Have a look at the execution plans.
> I would go with the JOINS everytime as it should involve less overhead.

> --

> Allan Mitchell (Microsoft SQL Server MVP)
> www.SQLDTS.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org



> > Hi,
> >           I'm a beginner in SQL Programming and i need to know whether
> > usage of SQL Joins will improve performance of my queries. I Used the
> > following queries that i find difference in Execution Time:

> > Query 1:
> > --------
> > SELECT A.Field1, A.Field2, A.Field3 FROM Table1 A
> > INNER JOIN Table2 B On A.[FieldX] = C.[FieldX] And C.FieldX = '101'

> > Query 2:
> > ---------
> > SELECT A.Field1, A.Field2, A.Field3  FROM Table1 A
> > WHERE A.[FieldX] in ( Select FieldX from Table2 where FieldX = '101')

> > No.Of Records: 70000
> > Query1 Execution Time : 10 Seconds
> > Query2 Execution Time :14 Seconds

> > Does this mean that i should always go for Joins instead of nested
> > queries?
> > Please help me on this.
> > Thanks in Advance.

> > Regards
> > Sen.

 
 
 

Using JOINS improve performance?

Post by Steve Thompso » Sat, 30 Nov 2002 23:19:53



> Does this mean that i should always go for Joins instead of nested
> queries?

You've gotten some good advice, I'd like to add joins will generally be
faster than nest queries, but not always. It depends on the type (and
selectivity quality) of indicies you have defined on the joined fields and
the amount of data in both tables being joined.

Steve

 
 
 

Using JOINS improve performance?

Post by Se » Tue, 03 Dec 2002 14:04:48


Dear All,
Thanks a lot for all your replies. I've also learned some tips from
all of your mails about improving performance and query readability. I
thank all of u for your support.

Regards
sen



> > Does this mean that i should always go for Joins instead of nested
> > queries?

> You've gotten some good advice, I'd like to add joins will generally be
> faster than nest queries, but not always. It depends on the type (and
> selectivity quality) of indicies you have defined on the joined fields and
> the amount of data in both tables being joined.

> Steve

 
 
 

Using JOINS improve performance?

Post by Doug Mill » Thu, 05 Dec 2002 01:46:14


Sometimes you HAVE to use nested queries - the logic of what you are
after simply won't allow any other choice.
But when at all possible, make it a join rather then a nested query.
When the engine goes to evaluate what you are doing, it MAY decide to
create a nested query style lookup based on what it thinks is going to
be fastest.
But, it MAY find another way.
If the nested query is indeed faster, then you can give the compiler a
hint to get the join as fast.

Not what you are after, but I'd look to indexes for even more speed.
Create an index on whatever field the '101' is equal to.
On the OTHER table, create an index on the join field.
Run those results.
Then create an index on the other fieldx field for the join. Rerun the
results.
If slower, give the compiler a hint.

A 10 second select statement on a 2 table join is generally not
acceptable.

-doug miller


> Hi,
>           I'm a beginner in SQL Programming and i need to know whether
> usage of SQL Joins will improve performance of my queries. I Used the
> following queries that i find difference in Execution Time:

> Query 1:
> --------
> SELECT A.Field1, A.Field2, A.Field3 FROM Table1 A
> INNER JOIN Table2 B On A.[FieldX] = C.[FieldX] And C.FieldX = '101'

> Query 2:
> ---------
> SELECT A.Field1, A.Field2, A.Field3  FROM Table1 A
> WHERE A.[FieldX] in ( Select FieldX from Table2 where FieldX = '101')

> No.Of Records: 70000
> Query1 Execution Time : 10 Seconds
> Query2 Execution Time :14 Seconds

> Does this mean that i should always go for Joins instead of nested
> queries?
> Please help me on this.
> Thanks in Advance.

> Regards
> Sen.

 
 
 

1. improving join performance on huge tables

I'm in the process of trying to do a fairly complex join on 2 large tables;
Table A is around 20 million rows and table B is about 4 million. The result
of this join is then used to update table A.

These tables have a total of 8 columns, with the join operation being done
on 4 of these columns, and an index has been created on these 4 columns. So
the update statement looks something like:

UPDATE  DB1.TableA
Set Total = T1.Total + T2.Total
FROM DB2.TableA T1
INNER JOIN DB2.TableA T2 ON
T1.Col1 = T2.Col1 AND
T1.Col2 = T2.Col2 AND
T1.Col3 = T2.Col3 AND
T1.Col4 = T2.Col4

Because these tables are so large, it takes DAYS for this to complete, since
SQL apparently needs to store all the intermediate data that would be
required to rollback the operation were it to fail, so it spends most of
it's time storing this information on disk. I get this impression from the
output of sp_who2, which shows extremely high disk I/O, while the CPU
activity remains pretty low.

One solution to problems like this might be to only process some fixed
number of rows at a time, say 10000, until the entire join is complete,
using a while loop to do this. The problem is how to constrain the join
operation so it only operates on 10K rows at a time. Since this table
doesn't have an indentity column or any other monotonically increasing ID
column, I'm trying to think of a way to do this. In other words, if I had an
identity column I could just add a where clause to operate on a particular
range of rows based on ID, incrementing the range by 10000 (or whatever)


fairly new to T-SQL programming.

Thanks for any advice.

-Gary

2. Transaction Replication processing overheads

3. Non-Equi-Joins: Any tricks to improve performance ?

4. ORA-1578

5. Using Filegroups to Improve Performance

6. Stored procedure generated network traffic (local, and remote)

7. Question about using a pl/sql table to improve performance of a query

8. [WEBMASTER] 'www/html mirrors.html'

9. how to improve query performance wihout using dynamic sql

10. Performance of views using outer joins (5.01)

11. Performance of joins using BETWEEN

12. Performance Inner Joins and SubSelects using IN clause

13. Left join vs right join - Performance