Could not insert a row larger than the page size into a hash table.

[Silx]

Greets!

Actually, this involves ASP as well, but I didn't want to cross-post to multiple categories. Let me know if I should.

I am getting the following error in my logs when folks search on my site:

ASP Error occurred 6/12/2009 12:25:23 PM in Microsoft OLE DB Provider for ODBC Drivers
Error number: -2147217900 (0x80040E14)
File: /inc_get_search_results.asp, line 75
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.
/forum_search.asp?

Here is my code (please be nice!):

	PUBLIC Function sqlString(ByVal keywords)
		Dim i
		sqlString = "select distinct t.Forum_Topic_ID, t.Forum_Topic_Name, t.Forum_Topic_Replies, t.Forum_Topic_Lastpost, Account.Account_Username, t.forum_topic_locked, t.forum_topic_sticky, Forum_Category.forum_category_name, t.forum_category_ID, Account.account_id, t.forum_topic_date from forum_topic as t left join forum_reply as r on r.forum_topic_id= t.forum_topic_id, forum_category, forum, account "
		if IsArray(keywords) then
			if NOT bSearchTitles AND NOT bSearchDescriptions then 
				sqlString = TRIM(sqlString) & ";"
				bNoFieldsToSearch = True
				Exit Function
			end if
			sqlString = sqlString & "WHERE forum.Forum_ID='" & ForumID & "' AND (("
			for i = 0 to UBOUND(keywords)
				if bSearchTitles then sqlString = sqlString & _
					"t.Forum_Topic_Name LIKE '%" & EscapeApostrophe(keywords(i)) & _
					"%' " & SearchType & " "
			next
			if bSearchTitles AND bSearchDescriptions then 
				sqlString = Left(sqlString, Len(sqlString)-4)
				sqlString = TRIM(sqlString)
				sqlString = sqlString & " OR ("
			end if
			for i = 0 to UBOUND(keywords)
				if bSearchDescriptions then sqlString = sqlString & _
					"r.Forum_Reply_Post LIKE '%" & EscapeApostrophe(keywords(i)) & _
					"%' " & SearchType & " " & _
					"t.Forum_Topic_Post LIKE '%" & EscapeApostrophe(keywords(i)) & _
					"%' " & SearchType & " "
			next
			sqlString = Left(sqlString, Len(sqlString)-4)
			if SearchType = "OR" then sqlString = sqlString & " "
			if bSearchTitles AND bSearchDescriptions then sqlString = sqlString & ") "
			sqlString=sqlString & ")) "
		end if
		sqlString = sqlString & " and t.forum_category_id = forum_category.Forum_Category_ID and forum_category.forum_id = forum.Forum_ID and t.account_id = account.account_id ORDER BY t.Forum_Topic_Date DESC;"
	End Function

	Public Function MakeKeywordArray(ByVal sWordVarTmp)
		Dim sDelim, x
		sDelim = ","
		if TRIM(sWordVarTmp) = "" then Exit Function
		if not instr(sWordVarTmp, sDelim) then sDelim = " "
		x = Split(sWordVarTmp, sDelim)
		MakeKeywordArray = x
	End Function

	if len(Request("SearchPhrase")) > 0 and (bSearchTitles=true or bSearchDescriptions=true) then 
		if Request("type") <> "AND" AND Request("type") <> "OR" then
			Request("type") = "AND"
		end if
		Dim strSQL
		strSQL = sqlString( MakeKeywordArray( trimquotes(HTMLDecode(trim(Request("SearchPhrase")))) ))
		Set dataConn = Server.CreateObject("ADODB.Connection")
		dataConn.Open MM_FreeFastForumDSN_STRING
		Set adoRS = Server.CreateObject("ADODB.Recordset")
		adoRS.Open strSQL, dataConn, adOpenStatic, adLockReadOnly, adCmdText
		TotalRecords = adoRs.RecordCount
		if not adoRS.EOF then adoRS.Move((PageNumber * TopicsPerPage))
		if not adoRS.EOF then 
			arrResults = adoRS.GetRows(TopicsPerPage)			
			TopicsExist=1
			adoRS.Close
			Set adoRS = nothing
			dataConn.Close
			set dataConn = nothing			

		else
			adoRS.Close
			Set adoRS = nothing
			dataConn.Close
			set dataConn = nothing			
			TopicsExist=0
		end if
	end if

Perusing the interwebs, I've run into a number of suggestions to make this error go away. However, I wanted to be sure that my code was sufficient.. and that maybe there's a better way to go about searching.

Right now I'm thinking about performing these routines at the sql server level via stored procedures rather than with asp scripting. Supposedly doing that will possibly kill this problem as well as increase the speed of searching. Thoughts?

thanks for your time!
-S

 

[markros]

I believe if you make this a stored procedure (and also pass the IDs as comma-delimited string), you may be able to optimize this code and avoid using dynamic SQL.

So, go for the robust SP.

 

[Silx]

One of the difficulties of using SP's with a requirement like this is its lack of array support (grrr!)

I imagine your suggestion of passing the search terms via a comma delimited string is because of that problem, right?

 

[markros]

SQL Server 2008 supports passing data table as a parameter.

However, the well-known technique is to use some delimited string and parse it into the table valued function like this one, for example

http://berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split

Or you can pass XML instead - that's another common technique.

 

[Silx]

I think I may need some assistance in getting the TSQL code wrapped up.

I've got a basic select statement working here:

CREATE PROCEDURE v2_Forum_Search
	@searchphrases_input varchar(255),
    @search_descriptions_toggle bit,
    @search_titles_toggle bit,
    @forum_ID int,
	@recordcount_output int OUTPUT
AS

set nocount on
select distinct t.Forum_Topic_ID, t.Forum_Topic_Name, t.Forum_Topic_Replies, t.Forum_Topic_Lastpost, Account.Account_Username, 
				t.forum_topic_locked, t.forum_topic_sticky, Forum_Category.forum_category_name, t.forum_category_ID, Account.account_id, t.forum_topic_date 
				from forum_topic 
        		as t 
		 	       left join forum_reply 
                   as r 
                   on r.forum_topic_id= t.forum_topic_id, forum_category, forum, account
                where @searchphrases_input IN(t.forum_topic_name)
                and forum.forum_id=@forum_id 
                and t.forum_category_id = forum_category.Forum_Category_ID 
                and forum_category.forum_id = forum.Forum_ID 
                and t.account_id = account.account_id 
				ORDER BY t.Forum_Topic_Date DESC;
select @recordcount_output=@@ROWCOUNT

What's missing:
The ability to toggle titles and descriptions (which works in the asp script).
The ability to choose all keywords or any keywords (also works in the asp script).
As mentioned in my posts above, TSQL does not allow for arrays inherently. I need some assistance with that. I know there are ways to get the passed array split into a table, but not sure how to combine that with what I have.

Any assistance is appreciated.

thanks,
-S