SQL - The Order of Tables and WHERE


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SQL - The Order of Tables and WHERE

  1. #1
    YFM Guest

    SQL - The Order of Tables and WHERE


    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

  2. #2
    DaveSatz Guest

    Re: SQL - The Order of Tables and WHERE

    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



  3. #3
    Michael Levy Guest

    Re: SQL - The Order of Tables and WHERE

    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



  4. #4
    Michael Ganesan Guest

    Re: SQL - The Order of Tables and WHERE


    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



  5. #5
    Kevin Downs Guest

    Re: SQL - The Order of Tables and WHERE

    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




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center