SQL Server Performance

A simple select count statement takes about 15 min

Discussion in 'Getting Started' started by daibaocun, Jan 21, 2011.

  1. daibaocun New Member

    1. MSSQL 2000 database has around 20 million records.
    2. A simple select count statement takes about 15 minutes, although proper indexing has been created on the table and fields.

    how to improve the preformance the simple select count(*) statement execute?

    the server specs. for MSSQL 2000 database:
    - Intel XEON
    - 4-CPU Quad Cores
    - 8-GB of physical memory.
  2. Luis Martin Moderator

    Welcome to the forums!!.
    Some questions:
    1) What OS, SQL and SP do you have?.
    2) How is SQL memory configured?
    3) Is SQL server dedicated?.
    4) The "Select ..." was measured in production or in tested server?.
  3. RamJaddu Member

    Any index fragmentations ... do you have any maintenance plans to remove index fragmentation?
  4. RamJaddu Member

    In case if you are looking for total row counts. here is the script which work in SQL 2000
    Declare c cursor for select '['+U.name+'.'+O.Name+']' from sysobjects O Inner join sysusers U on O.uid=u.uid where type='U' and O.name <>'dtproperties'
    Declare @name varchar(255),@sql varchar(100)
    open c
    fetch next from c into @name
    print @name
    Create table #TSpace(name varchar(255),rows int,resevered varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))
    while @@fetch_status = 0
    begin
    set @sql = 'sp_spaceused ' +@name
    insert into #Tspace Exec(@sql)
    fetch next from c into @name
    end
    close c
    deallocate c
    select name,rows from #Tspace order by 2 desc
    drop table #tspace
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON

    GO
  5. Adriaan New Member

    "proper indexing has been created on the table and fields"
    You don't mention if there's a Primary Key defined, or at the very least a unique constraint.
    Also, are you doing the SELECT COUNT(*) in SQL Server's own console, or through a different client application?
  6. satya Moderator

    SELECT * on a 20 million rows table would have a slow down execution in terms of other processes that are running at the same time. As explained above if you can come up with response it will help.
    (My guess is OP might have got satisfactory reply from other forums{
  7. PurpleLady New Member

    A quick change would be select count(1) to count each record. I replaced all my select count(*) with it and got much better performance.This is because SQL server first gets all the information on the columns and rows required, and then counts the data. Using a constant eliminates the need for column and row data.
  8. FrankKalis Moderator

    [quote user="PurpleLady"]
    A quick change would be select count(1) to count each record. I replaced all my select count(*) with it and got much better performance.This is because SQL server first gets all the information on the columns and rows required, and then counts the data. Using a constant eliminates the need for column and row data.
    [/quote]
    Every now and then I hear this claim, and I really believe this belongs to the realm of myths around SQL Server. It might be true for other database platforms, but I can't tell for sure, though I would be surprised as this would show some pretty obvious shortcoming of the optimizer there.
    For SQL Server, COUNT() takes an expression as its argument, and if you look at BOL for COUNT, you'll see thisCOUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
    And a little bit further down:
    COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates
    So SQL Server by definition does not have to go to the metadata and get information about columns and stuff, because it knows that this information will never be used inside the COUNT() context and that it would be a waste of resources to get them. And even if SQL Server would query some metadata upfront, this would be done during compile/optimisation time before the actual execution phase. The actual execution phase for SELECT COUNT(1) and SELECT COUNT(*) is identical in terms of execution plan, IO, etc...
    There are tons of questions around this with lots of good answers. Here are some of them for SQL Server:
    http://stackoverflow.com/q/1221559/27535
    http://social.msdn.microsoft.com/fo.../thread/9367c580-087a-4fc1-bf88-91a51a4ee018/
    And here is one for Oracle, that shows that this claim doesn't even hold true in Oracle any longer:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789
    EDIT: Modified formatting
  9. FrankKalis Moderator

    Actually forgot to reply to the original question....
    There is not much you can do about it when the result of COUNT(*) has to be accurate. If you could live with a good approximation, you might try to get the count in READ UNCOMMITTED isolation level, though I guess this doesn't give that much of a benefit, since the actual scan and the work still has to be carrier out. A much faster way would be to get this information from SQL Server's system tables. It is stored there somewhere, but I can't remember which table it was in SQL Server 2000. If memory serves me correctly, then I think it is in sysindexes. Anyway, this number is not guaranteed to be accurate all the time, but depending on your needs it might do the trick.
  10. Madhivanan Moderator

    If you want to know table names with count, run this
    DBCC UPDATEUSAGE (0)
    GO
    select name,rows from sysindexes where indid<2 order by name
  11. daibaocun New Member

    After create clustered index on the table, it takes about 2 seconds to execute select count statement against 17 million records. thanks.
  12. Adriaan New Member

    That would appear to confirm that no primary key or unique index existed. Adding a clustered index on a table without a primary key or unique index means a "uniquifier" is added to the clustered index.
  13. satya Moderator

    But going forward it may be costly to maintain the index depending upon the table growth.
  14. FrankKalis Moderator

    If you choose the clustered index wisely, it almost always is worth the cost of maintaining it. Usually it is much more costly not to have a clustered index.

Share This Page