SQL Server Performance

Why table scan? why?

Discussion in 'Performance Tuning for DBAs' started by korzon, Mar 24, 2005.

  1. korzon New Member

    I have a simple table:

    ID int 4, ContentID int 4, Signature Decimal 20, Deleted bit.

    A simlpe query:

    select Count(Signature) as num from Table WITH (NOLOCK) where ContentID = ### and deleted = 0

    What kind of index(s) do I need to avoid a costly table scan?
  2. Luis Martin Moderator

    I suppose: contendID, deleted.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

    Use SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.

    Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. antares686 New Member

    ContentID as clustered, don't bother with deleted is the normal suggestion.

    But you may still get an index scan which effectively on a clustered index is the same as a table scan but more effective than a non-clustered index scan.

    The big questions is how many rows do you have? If you ave very few then a table scan is not all that costly if a huge number then clustered on contentid should show a difference.
  5. mmarovic Active Member

    Be aware that:
    select Count(Signature) as num
    from Table WITH (NOLOCK)
    where ContentID = ### and deleted = 0
    is equivalent to:
    Select Count(*) as num
    from Table WITH (NOLOCK)
    where ContentID = ### and deleted = 0
    and Signature is not null
  6. korzon New Member

    quote:Originally posted by mmarovic

    Be aware that:
    select Count(Signature) as num
    from Table WITH (NOLOCK)
    where ContentID = ### and deleted = 0
    is equivalent to:
    Select Count(*) as num
    from Table WITH (NOLOCK)
    where ContentID = ### and deleted = 0
    and Signature is not null

    Hmm, that means that I should instead use

    Select count(contentID) as num
    From table with (nolock)
    where contentID = ### and deleted = 0

    ?
  7. mmarovic Active Member

    If you need simply number of rows you can use either count(contentID) or count(*) or count(0) or count(3.14142) or...
  8. Slart New Member

    I agree with Luis. Assuming you dont count(signature), an index on (Content, Deleted) would be the most efficient (for read operations) because it would not require any bookmark lookups. It would be a contiguous disk read.

    If you did count(signature), then the most efficient index for accessing this table as a read would be (Content,Deleted,Signature).

  9. ranjitjain New Member

    Hmm, that means that I should instead use

    Select count(contentID) as num
    From table with (nolock)
    where contentID = ### and deleted = 0
    ?


    index on contentID,deleted
    and then fire
    set nocount on
    Select count(contentID)
    From table with (nolock)
    where contentID = ### and deleted = 0
    set nocount off

  10. knoen New Member

    Try to create an index on one field and then use count(*) then the optimizer can decide what col to use to get highest performance.

    Im not right?

  11. ghemant Moderator

    Dear Sir ,
    not any field u have to create index of ContentID and deleted ... out of which one clustered index and then if u fire a query it will dafinately boost performance of yr query output

    hsGoswami
    ghemant@gmail.com

Share This Page