SQL Server Performance

Problem while fetching the data from a large table

Discussion in 'SQL Server 2005 General Developer Questions' started by Ariharaselvan, Jan 2, 2007.

  1. Ariharaselvan New Member

    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
  2. mmarovic Active Member

    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.
  3. Adriaan New Member

    quote:The table doesn't have any unique column.
    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.
  4. satya Moderator

    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.
  5. Expansion New Member

    quote:Originally posted by Adriaan

    Add an INT column with IDENTITY(1,1) and make this the clustered primary key.

    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.

Share This Page