Very Computer

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.

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

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.

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.

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

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

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

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

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