SQL Server Performance

FREETEXTTABLE and total results count when paging

Discussion in 'SQL Server 2005 General Developer Questions' started by sseerraajj, Oct 13, 2009.

  1. sseerraajj New Member

    Hello..
    I am using SQL Server 2005 and searching by Freetextable with full text search Like this:
    SELECT topicid,topicname, Rank1=ft1.rank
    FROM topicstbl p
    JOIN freetextTable(topicstbl,topicname,'name',10) as ft1 on
    (p.topicID = ft1.[key])
    I need paging results 10 by 10.. but also I want to know the total results count or about of that.
    by google or bing we get the first ten results and " of about (results count)".
    how to do like that?
    my table has millions rows, so if I use Count(*) OVER() query will be too slow.
    any help please!
  2. satya Moderator

    select
    topicid,
    count(*) as cnt,
    sum(count(*)) over()
    from
    dbo.[topicstbl]
    group by
    topicid
    order by
    topicid
    go
  3. sseerraajj New Member

    thanks..but it is too slow!!
  4. satya Moderator

    By default hte paging process is too slow, unless you have other way round to store such information as a metadata it is hard to obtain the result quickly.

Share This Page