SQL2K TSQL Alternative to TOP X

Last Post 03 Oct 2006 07:47 PM by uday. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
uday
New Member
New Member

--
27 Sep 2006 05:41 PM
Hi all,
The query below executes in under 4 seconds without the TOP X clause. I must have the TOP X clause, can you please suggest an alternative to using TOP X . With the TOP X clause it takes over 2 minutes to complete!!! Clustered & Non-Clustered indexes are all good, updated and rebuilt.
Regards,
Uday Shivamurthy
------------------------------------
SELECT top 100
S.SITE_ID,
S.SHORT_NAME,
SL.DEVICE_ID,
D.DEVICE_DOMAIN DEVICE,
LEFT(D.DEVICE_DOMAIN, LEN(D.DEVICE_DOMAIN) - LEN(S.SHORT_NAME) - 5) AS 'DEVICE_SHORT_NAME',
EVENT_TYPE = CASE WHEN SL.CUSTOMER_EVENT_ID IS NULL THEN SL.EVENT_TYPE ELSE CE.EVENT_TYPE END,
SL.EVENT_LEVEL,
ESL.SEVERITY_LEVEL,
SL.EVENT_DATE EVENT_DATE_UTC,
SL.ACKED,
SL.STARSEC_ID,
SL.NOTES,
SL.INFO_URL
FROM SECURITY_LOGS SL(NOLOCK)
INNER JOIN DEVICES D(NOLOCK)
ON SL.DEVICE_ID = D.DEVICE_ID
INNER JOIN LOCATIONS L(NOLOCK)
ON D.LOCATION_ID = L.LOCATION_ID
LEFT OUTER JOIN CUSTOMER_EVENT CE(NOLOCK)
ON SL.CUSTOMER_EVENT_ID = CE.CUSTOMER_EVENT_ID
INNER JOIN SITES S(NOLOCK)
ON L.SITE_ID = S.SITE_ID
INNER JOIN EVENT_SEVERITY_LEVEL ESL (NOLOCK)
ON SL.EVENT_LEVEL = ESL.EVENT_LEVEL
WHERE L.SITE_ID = '90447'
ORDER BY SL.EVENT_DATE DESC
suggest an alternative to using TOP X
SQLUSA
New Member
New Member

--
30 Sep 2006 12:41 PM
Take the ORDER BY. That slows down TOP X.

Or buy faster hardware....


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/businessintelligence
The Best SQL Server 2005 Training in the World
uday
New Member
New Member

--
03 Oct 2006 07:47 PM
Thanks SQLUSA,
That was extremely useful....In fact, I did'nt need new hardware. The query-engine had to sift through 100's and 1000's of rows to figure out which ones were the "top 100". All, I had to do was a sub-query to fix that problem. It flies like a rocket now.
Regards,
Uday
You are not authorized to post a reply.

Acceptable Use Policy