SQL Server Performance Forum – Threads Archive

Problem while fetching the data from a large table

Hi all We have a table with more than 4000000 rows.
When i’m running the below query through a stored procedure, from a vb.net based web application (Using SQL data provider)
we are getting "Time out expiry" error. Query
SELECT TOP 100 ListID, ListItemID, ActivityDateTime, Title, FileSize, DateCreated, URL, ConditionsForUse,
IconURL, SiteID FROM TR_ACTIVITY_LOG_TBL
WHERE UserID=’arihara.selvan’ AND DisplayToUser=1 AND ActivityType=2
ORDER BY ActivityDateTime Desc I checked the indexes of the table. There are 3 non-clustered indexes available on this table
on ListID, ListItemId and UserID columns.
When i checked the Show execution plan option, it is using the Index created on UserID column.
Not able to run the index tuning wizard, since i have only read access to this server. Any other way for tuning further?
Will a clustered index can resolve the problem? The table doesn’t have any unique column.
The size of the table is 23GB. creating a clustered index possible on this table? regards
Ari

---

Decision about clustered index should not be done based on just one query. For this specific query composite index on
userID, displayToUser, ActivityType, ActivityDateTime desc
would reduce response time to miliseconds. My guess is there are tens of 1000s of rows for that specific user.

---

That’s begging for poor performance. Add an INT column with IDENTITY(1,1) and make this the clustered primary key. Then rebuild the other indexes.

---

There is also a newer mechanism that you can use in SQL Server 2005 that compliments the existing Database Tuning Advisor Tool. It does not require that you pre-identify a workload, and it is integrated into the engine and runs as part of the regular operation of the server (you do not need to run anything). It does not do as much work as the DTA, but it can find the common problems that cause significant performance problems in deployed systems. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Why is using a clustered index on an Int column much more favorable? For this query the index on UserId is used, according Ariharaselvan.
I thought the row locator for a heap table points more directly to the rows then the clusterkey in a table with a clustered index. I agree with Adriaan, that without a clustered index you often run in many administrative and performance problems.
For the original question it would be usefull to know how many rows are to be expected in the results.

---

]]>