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!
    sseerraajj, Oct 13, 2009
    #2

  2. satya Moderator

    select
    topicid,
    count(*) as cnt,
    sum(count(*)) over()
    from
    dbo.[topicstbl]
    group by
    topicid
    order by
    topicid
    go
    satya, Oct 16, 2009
    #3

  3. sseerraajj New Member

    thanks..but it is too slow!!
    sseerraajj, Oct 20, 2009
    #4

  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.
    satya, Oct 26, 2009
    #5

Share This Page