SQL Server Performance

Getting TotalRows in SQL is too slow for huge records.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by bab_ganesh, May 24, 2008.

  1. bab_ganesh New Member

    I am using SQL Server 2005 Std. Ed. I have a table with 30+ Lakhs records. I have the index on this table.
    When I write the SELECT COUNT(*) FROM tblTableName, it tooks around 30 to 40 sec to retrieve the Count. Its too slow.
    I am using ASP.Net + ADO.Net as front end to show the reports. If this query takes this much of time then my application need to wait around 1 min. to show the output.
    Now i want to know.. How to reduce the time to take the total records from the huge table.
    Please answer me as soon as possible.
    Thanks in advance.

  2. satya Moderator

    Do you really need all the columns to show on the results?
    It is not a good practice to achieve performance.
  3. bab_ganesh New Member

    HI Satya..
    Thanks for your response. Actually I do a search pages. It will bring only top 100 records from the DB and show. But i need to show how many rows found for your search.
    for example: a search will retrive 20+ Lakhs records. But I will show only 100 records and control it using custom paging.
    My problem is, taking count. COUNT(*) is slow even I used COUNT(0). No use.
    pl tell me the solution,.
  4. satya Moderator

    What type of indexes and how many exists on this table?
    Also if it is for reporting purpose, have you considered using Reporting SErvices?
  5. bab_ganesh New Member

    I have the table with more than 25 Columns and have unique column with Primary Key clustered Index.
    Regarding reporting service, Can I implement it in ASP.Net C#.NEt 2.0 ? If possible, tell me any primary idea.
  6. satya Moderator

  7. mst New Member

    If you want row count for ALL rows in the table-- and can tolerate a little inaccuracy (but I think this was resolved in sql2005) look at:
    select row_count,object_name(object_id) TableName from sys.dm_db_partition_stats
    Note there may be multiple rows per table; you may need to join to sys.indexes to eliminate. You''d want to only get the rows for HEAP or CLUSTERED-- which would eliminate multiple rows per table. Also, if the table is partitioned it looks like there's more work to do.
  8. adef New Member

    Table with 30k+ need not take up to 30secs. Are you using a where clause in select count(*) at all, - to narrow down search. If yes, is this column indexed? if not index it
  9. gbd77rc New Member

    Depending on how you are going to be using this info have you tried the system stored procedure sp_spaceused '<table name>'. It uses information in the sys.dm_db_partition_stats management view.
  10. FrankKalis Moderator

    In any case shouldn't a COUNT(*) query on only 30k rows take more than a second.
    Can you please post the code you're using in your client to show the count?
  11. bab_ganesh New Member

    I am getting the count as "SELECT COUNT(0) FROM tblMasterTable". Actually I am doing a search page. For every search I am getting how much of record matches with the search string. But I am showing only top 100 records at a time.. if they click next then only I will retrieve next 100 record ( this is done by using ROW_NUMBER()). But count is taking only one time.
    Note: My tblMasterTable contains 205715 records.
    The user searching in 20 fields (varchar(255) datatype). All the fields are not non-clustered fields.
  12. adef New Member

    Are you able to utilised the row column in the sp_spaceUsed SP just mentioned above. Run the code below and let us know if its of use or not
    usage: sp_spaceused dbo.tblMasterTable
  13. gbd77rc New Member

    Hi Ganesh,
    Are you retrieving the count everytime the page is return or once at the start? I normally have two stored procedures for this, the first one to work out the count based on the search, and only call this one if the search changes between page requests. The second stored procedure to do the page of rows. Normally our page size is 20 so it will fit nicely in the web page. This is using the ROW_NUMBER feature.
    Both stored procedures use dynamic SQL to workout which fields to search on.
    These 20 fields are they indexed or do the form a single field in the application? If they form a single field in the application, say for comments I would normally have child table which would have a single varchar(255) field in it. Well I used to, now I use VARCHAR(MAX) for comments, but they are not really searchable anyway.
    Any chance you can show us some code examples where you are getting the issue, the orignal question looked like you wanted the total number of rows in the table and not total number of rows found.
  14. bab_ganesh New Member

    Hi All,
    Thanks for your responses.
    I go with Indexed View. That is I created a View with neccessary fields for my search and set the non-clustered index to all the 20+ searchable fields. Tested in Development Server. Now its pretty fast.
    Even though I will come back to you after I test this in the Production Server.

Share This Page