Re: containstable, top_n_rank, and additional where clause combination causes unexpected result
From: Dot net work (dotnw_at_hotmail.com)
Date: 10/28/04
- Next message: John Kane: "Re: CONTAINS performance"
- Previous message: John Kane: "Re: Edit fill word list"
- In reply to: Hilary Cotter: "Re: containstable, top_n_rank, and additional where clause combination causes unexpected result"
- Messages sorted by: [ date ] [ thread ]
Date: 28 Oct 2004 11:50:42 -0700
Thanks a lot for your advice.
-dnw.
"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:<#4HNUAOvEHA.2540@TK2MSFTNGP09.phx.gbl>...
> Your problem is that when you limit your result set that is returned from
> MSSearch further rows are removed by the "and ParentAisleId = @PID" clause.
>
> The approaches to this problem are 1) knowing in advance the number of rows
> which are returned by MSSearch for this query and entering this value for
> @nextitemrecpointer, 2) partitioning your table into multiple partitioned
> tables one for each PartentAisleID value so your query would end up looking
> something like this:
>
> if @ParentAisleID=1
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_1,
> containstable(Store_BasicSearchableShelves, ShelfName, @sSearchText,
> @nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
>
> if @ParentAisleID=2
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_2,
> containstable(Store_BasicSearchableShelves, ShelfName, @sSearchText,
> @nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
>
> 3) picking a larger number which will guarantee a larger number of hits that
> when filtered by @ParentAisleID will yield at least @nextitemsrecpointer
> hits. You have to be careful here as you don't want to pick too large a
> number to guarantee hits.
>
> In large search applications partitioning in frequently used and it does
> work very well. They will often have a seperate catalog for each of the
> partitioned tables and you will then get a seperate threads for each catalog
> which will improve your overall querying and indexing.
>
>
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Dot net work" <dotnw@hotmail.com> wrote in message
> news:77b8c5a9.0410280048.698a85c7@posting.google.com...
> > Hello,
> >
> > Something strange happens if I try and do the following:
> >
> > If I use the containstable function, with top_n_rank, along with an
> > extra "and where" clause, then the top_n_rank does not seem to return
> > the correct number of rows.
> >
> > Here is an example to explain my point:
> >
> > select *, tempidentity = IDENTITY (INT) into #tempt
> > from Store_BasicSearchableShelves,
> > containstable(Store_BasicSearchableShelves, ShelfName, @sSearchText,
> > @nextitemsrecpointer) tblSearchResults
> > where [key] = Store_BasicSearchableShelves.ShelfId
> > and ParentAisleId = @PID
> > order by rank desc
> >
> >
> > If @nextitemsrecpointer is equal to 20, then the stored proc only
> > returns 14 rows. If I remove the additional "and ParentAisleId =
> > @PID" where clause, then the stored proc returns 20 rows, which is
> > correct. If I begin to edit the value of @nextitemsrecpointer for
> > experimentational purposes to a higher value such as 30 or 40, then
> > the stored proc returns more rows - 18 and 25 respectively.
> >
> > The additional "and ParentAisleId = @PID" seems to be affecting the
> > way that top_n_rank is behaving.
> >
> > Can anyone please provide me with a work around for this?
> >
> > Thank you,
> > Regards, dnw.
- Next message: John Kane: "Re: CONTAINS performance"
- Previous message: John Kane: "Re: Edit fill word list"
- In reply to: Hilary Cotter: "Re: containstable, top_n_rank, and additional where clause combination causes unexpected result"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
