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.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|||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?|||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.|||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...|||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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_93sk.asp) 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.

-PatPsql