SQL - The Order of Tables and WHERE

[YFM]

I have a SQL Query (I am using SQL Server 6.5 / 7):

Query 1:

SELECT
R.MiID, R.ConsDate
FROM
HHMRD1 R, HHBCPD1 B
WHERE
R.MiID = B.MiID
AND R.ConsDate = B.ConsDate

I want to optimise the Query - Table HHMRD1 has Millions of Rows and Table
HHBCPD1 has a few thousand so would I be better off redoing the Query so
that it is:

Query 2:

SELECT
R.MiID, R.ConsDate
FROM
HHBCPD1 B, HHMRD1 R,
WHERE
B.MiID = R.MiID
AND B.ConsDate = R.ConsDate

Will Query 2 be Better?

ALSO In SQL Server 6.5 + 7 does it handle the WHERE Clause by working Bottom-Top
(like Oracle)?

Any help/comments will be much appreciated - THANK YOU IN ADVANCE - Regards

[DaveSatz]

You should use ANSI SQL92 syntax - SQL Server sometiimes executes
differently based on using "old style" vs. ANSI:

SELECT R.MiID, R.ConsDate
FROM HHMRD1 R
JOIN HHBCPD1 B on R.MiID = B.MiID AND R.ConsDate = B.ConsDate

I am not sure it would make a difference which table is in the FROM and
which is in the JOIN to the optmizier

try:
SET SHOWPLAN ON
GO
SET NOEXEC ON
GO

SELECT R.MiID, R.ConsDate
FROM HHMRD1 R
JOIN HHBCPD1 B on R.MiID = B.MiID AND R.ConsDate = B.ConsDate

SELECT R.MiID, R.ConsDate
FROM HHBCPD1 B
JOIN HHMRD1 Ron R.MiID = B.MiID AND R.ConsDate = B.ConsDate

SET NOEXEC Off
GO
SET SHOWPLAN Off
GO

hth,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------

"YFM" <YMayat@Hotmail.com> wrote in message news:3a66cc79$1@news.devx.com...
>
> I have a SQL Query (I am using SQL Server 6.5 / 7):
>
> Query 1:
>
> SELECT
> R.MiID, R.ConsDate
> FROM
> HHMRD1 R, HHBCPD1 B
> WHERE
> R.MiID = B.MiID
> AND R.ConsDate = B.ConsDate
>
> I want to optimise the Query - Table HHMRD1 has Millions of Rows and Table
> HHBCPD1 has a few thousand so would I be better off redoing the Query so
> that it is:
>
> Query 2:
>
> SELECT
> R.MiID, R.ConsDate
> FROM
> HHBCPD1 B, HHMRD1 R,
> WHERE
> B.MiID = R.MiID
> AND B.ConsDate = R.ConsDate
>
> Will Query 2 be Better?
>
> ALSO In SQL Server 6.5 + 7 does it handle the WHERE Clause by working
Bottom-Top
> (like Oracle)?
>
> Any help/comments will be much appreciated - THANK YOU IN ADVANCE -
Regards

[Michael Levy]

The query optimizer will decide the best way to solve the query including
the type of join (nested, hash or merge). Let the optimizer do it's work
unless you know that it's doing it wrong.

Hopefully you've got indexes on the four columns. If you want to play, you
might try a composite index.

-Mike
--
Michael Levy MCDBA, MCSD, MCT
michaell@gasullivan.com

[Michael Ganesan]

YFM,
Index the fields you would like to search on. The SQL engine will employ
the services of the index to optimize your search.






"YFM" <YMayat@Hotmail.com> wrote:
>
>I have a SQL Query (I am using SQL Server 6.5 / 7):
>
>Query 1:
>
>SELECT
> R.MiID, R.ConsDate
>FROM
> HHMRD1 R, HHBCPD1 B
>WHERE
> R.MiID = B.MiID
>AND R.ConsDate = B.ConsDate
>
>I want to optimise the Query - Table HHMRD1 has Millions of Rows and Table
>HHBCPD1 has a few thousand so would I be better off redoing the Query so
>that it is:
>
>Query 2:
>
>SELECT
> R.MiID, R.ConsDate
>FROM
> HHBCPD1 B, HHMRD1 R,
>WHERE
> B.MiID = R.MiID
>AND B.ConsDate = R.ConsDate
>
>Will Query 2 be Better?
>
>ALSO In SQL Server 6.5 + 7 does it handle the WHERE Clause by working Bottom-Top
>(like Oracle)?
>
>Any help/comments will be much appreciated - THANK YOU IN ADVANCE - Regards

[Kevin Downs]

further to the other answers...

It is best to use the SQL Server Query Analyser to examine the query plan when
you are attempting to optimise. I would also advise checking the query on both
6.5 and 7 - the query engine and cost analyser has significant changes between
these versions.
The query engine in SQL Server 7 is remarkably good, but, as Dave mentioned,
you must use the SQL92 join syntax to take full advantage of it - not too much
of a hardship, and I find it much clearer and more flexible.

Regards,
Kevin
"YFM" <YMayat@Hotmail.com> wrote in message news:3a66cc79$1@news.devx.com...
>
> I have a SQL Query (I am using SQL Server 6.5 / 7):
>
> Query 1:
>
> SELECT
> R.MiID, R.ConsDate
> FROM
> HHMRD1 R, HHBCPD1 B
> WHERE
> R.MiID = B.MiID
> AND R.ConsDate = B.ConsDate
>
> I want to optimise the Query - Table HHMRD1 has Millions of Rows and Table
> HHBCPD1 has a few thousand so would I be better off redoing the Query so
> that it is:
>
> Query 2:
>
> SELECT
> R.MiID, R.ConsDate
> FROM
> HHBCPD1 B, HHMRD1 R,
> WHERE
> B.MiID = R.MiID
> AND B.ConsDate = R.ConsDate
>
> Will Query 2 be Better?
>
> ALSO In SQL Server 6.5 + 7 does it handle the WHERE Clause by working
Bottom-Top
> (like Oracle)?
>
> Any help/comments will be much appreciated - THANK YOU IN ADVANCE - Regards