Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    24

    Unanswered: Optimizing a query

    Hello,
    I am hoping someone here can help me optimize the following query:
    SELECT
    INCOMING.DATE_TIME, INCOMING.URL, INCOMING.HITS,
    USER_NAMES.USER_LOGIN_NAME,
    CATEGORY.NAME
    FROM
    (wsHQMay2004.dbo.INCOMING INCOMING INNER JOIN wsHQMay2004.dbo.CATEGORY CATEGORY ON INCOMING.CATEGORY = CATEGORY.CATEGORY)
    INNER JOIN wsHQMay2004.dbo.USER_NAMES USER_NAMES ON INCOMING.USER_ID = USER_NAMES.USER_ID
    WHERE
    INCOMING.DATE_TIME >= '2004-05-01 00:00:00.00' AND
    INCOMING.DATE_TIME < '2004-06-01 00:00:00.00'
    ORDER BY
    INCOMING.URL ASC


    I am just hoping to get some tips on perhaps a better way to write this query as right now, due to the size of the incoming table, this query just takes forever.

    Any advise will be apreciated.

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    I would recommend you to create clustered index on INCOMING.DATE_TIME if you do not have it. Think about indexes for CATEGORY.CATEGORY and USER_NAMES.USER_ID. Check execution plan for this query - may be it will be good idea to use INNER LOOP JOIN (it depends how many records are in tables CATEGORY and USER_NAMES).

    Try this version:

    SELECT INCOMING.DATE_TIME, INCOMING.URL, INCOMING.HITS,
    ,(select USER_LOGIN_NAME from wsHQMay2004.dbo.USER_NAMES where USER_NAMES.USER_ID=INCOMING.USER_ID) as 'USER_LOGIN_NAME'
    ,(select NAME from wsHQMay2004.dbo.CATEGORY where CATEGORY.CATEGORY=INCOMING.CATEGORY) as 'NAME'
    FROM wsHQMay2004.dbo.INCOMING INCOMING
    WHERE
    INCOMING.DATE_TIME >= '2004-05-01 00:00:00.00' AND
    INCOMING.DATE_TIME < '2004-06-01 00:00:00.00'
    ORDER BY INCOMING.URL ASC

  3. #3
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    24
    I currently have a non-clustered index on the date_time field. Perhaps I will try a clustered one. The incoming table is about 65 Million records.

    I am not very familiar with SQL Server (Oracle is mainly what I use), how do I get the execution plan for a query?

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Query analyzer - put query in, then menu: Query- Display Estimated Execution Plan or Show Execution Plan. You can make a screenshot of execution plan and somebody could help you to improve query performance.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I hope you have indexes on the USER_ID and CATEGORY fields. I'd prefer a clustered index on those.

    You MAY get better performance by creating a covered composite index on DATE_TIME and USER_ID. Worth a shot...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by lestop
    I am not very familiar with SQL Server (Oracle is mainly what I use), how do I get the execution plan for a query?
    I'd recommend using SET SHOWPLAN_TEXT for an Oracle user. The text output isn't as pretty as the GUI is, but it makes more sense if you are familiar with query plans and is generally more familiar to an Oracle user.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •