Databases
StackOverflow2013
Postsdbo
POSQL Server 2005 Full Text forum Search
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2005 Full Text forum Search
    primarykey
    data
    text
    <p>I'm working on a search stored procedure for our existing forums.</p> <p>I've written the following code which uses standard SQL full text indexes, however I'm sure there is a better way of doing it and would like a point in the right direction.</p> <p>To give some info on how it needs to work, The page has 1 search text box which when clicked will search thread titles, thread descriptions and post text and should return the results with the title matches first, then descriptions then post data. </p> <p>Below is what I've written so far which works but is not elegant or as fast as I would like. To give an example of performance with 20K threads and 80K posts it takes about 12 seconds to search for 5 random words.</p> <pre><code>ALTER PROCEDURE [dbo].[SearchForums] ( --Input Params @SearchText VARCHAR(200), @GroupId INT = -1, @ClientId INT, --Paging Params @CurrentPage INT, @PageSize INT, @OutTotalRecCount INT OUTPUT ) AS --Create Temp Table to Store Query Data CREATE TABLE #SearchResults ( Relevance INT IDENTITY, ThreadID INT, PostID INT, [Description] VARCHAR(2000), Author BIGINT ) --Create and populate table of all GroupID's This search will return from CREATE TABLE #GroupsToSearch ( GroupId INT ) IF @GroupId = -1 BEGIN INSERT INTO #GroupsToSearch SELECT GroupID FROM SNetwork_Groups WHERE ClientId = @ClientId END ELSE BEGIN INSERT INTO #GroupsToSearch VALUES(@GroupId) END --Get Thread Titles INSERT INTO #SearchResults SELECT SNetwork_Threads.[ThreadId], (SELECT NULL) AS PostId, SNetwork_Threads.[Description], SNetwork_Threads.[OwnerUserId] FROM SNetwork_Threads INNER JOIN SNetwork_Groups ON SNetwork_Groups.GroupId = SNetwork_Threads.GroupId WHERE FREETEXT(SNetwork_Threads.[Description], @SearchText) AND Snetwork_Threads.GroupID IN (SELECT GroupID FROM #GroupsToSearch) AND SNetwork_Groups.ClientId = @ClientId --Get Thread Descriptions INSERT INTO #SearchResults SELECT SNetwork_Threads.[ThreadId], (SELECT NULL) AS PostId, SNetwork_Threads.[Description], SNetwork_Threads.[OwnerUserId] FROM SNetwork_Threads INNER JOIN SNetwork_Groups ON SNetwork_Groups.GroupId = SNetwork_Threads.GroupId WHERE FREETEXT(SNetwork_Threads.[Name], @SearchText) AND Snetwork_Threads.GroupID IN (SELECT GroupID FROM #GroupsToSearch) AND SNetwork_Groups.ClientId = @ClientId --Get Posts INSERT INTO #SearchResults SELECT SNetwork_Threads.ThreadId, SNetwork_Posts.PostId, SNetwork_Posts.PostText, SNetwork_Posts.[OwnerUserId] FROM SNetwork_Posts INNER JOIN SNetwork_Threads ON SNetwork_Threads.ThreadId = SNetwork_Posts.ThreadId INNER JOIN SNetwork_Groups ON SNetwork_Groups.GroupId = SNetwork_Threads.GroupId WHERE FREETEXT(SNetwork_Posts.PostText, @SearchText) AND Snetwork_Threads.GroupID IN (SELECT GroupID FROM #GroupsToSearch) AND SNetwork_Groups.ClientId = @ClientId --Return Paged Result Sets SELECT @OutTotalRecCount = COUNT(*) FROM #SearchResults SELECT #SearchResults.[ThreadID], #SearchResults.[PostID], #SearchResults.[Description], #SearchResults.[Author] FROM #SearchResults WHERE #SearchResults.[Relevance] &gt;= (@CurrentPage - 1) * @PageSize + 1 AND #SearchResults.[Relevance] &lt;= @CurrentPage*@PageSize ORDER BY Relevance ASC --Clean Up DROP TABLE #SearchResults DROP TABLE #GroupsToSearch </code></pre> <p>I know its a bit long winded but just a nudge in the right direction would be well appreciated.</p> <p>Incase it helps 80% of the query time is taken up when search posts and according to teh query plan is spent on "Clustered Index Scan" on the posts table. I cant see anyway around this.</p> <p>Thanks</p> <p>Gavin</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndy Mikula
    1. USGavin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload