Full-text search speed seemed dramatically reduced when you throw in another table in to SQL statement.
Here you go:
SELECT DISTINCT t.TOPIC_ID, t.T_SUBJECT + '(' + CAST(m.M_NAME AS nvarchar(50)) + ')', c.CAT_NAME + ' \ ' + f.F_SUBJECT + ': ' + CAST(t.T_MESSAGE AS nvarchar(100)), T.T_DATE, '/forum/' + t.T_URLBASLIK + '_t' + CAST(t.TOPIC_ID AS nvarchar(10)) + '.html' AS topic_url, 'Forum' "
vidSQL = "FROM FORUM_TOPICS t, FORUM_REPLY r, FORUM_MEMBERS m, FORUM_FORUM f, FORUM_CATEGORY c, FORUM_MEMBERS mr "
vidSQL = vidSQL & " WHERE r.TOPIC_ID = t.TOPIC_ID AND m.MEMBER_ID = t.T_AUTHOR AND mr.MEMBER_ID = r.R_AUTHOR AND f.FORUM_ID = t.FORUM_ID AND c.CAT_ID=t.CAT_ID AND f.F_PRIVATEFORUMS = 0 AND ("
vidSQL = vidSQL & " CONTAINS(t.T_SUBJECT, '" & strFT & "') "
vidSQL = vidSQL & " OR CONTAINS(t.T_MESSAGE, '" & strFT & "') "
vidSQL = vidSQL & ")"
This is like a breeze 0,11 seconds...
But when you add:
vidSQL = vidSQL & " OR CONTAINS(r.R_MESSAGE, '" & strFT & "')
It turns out 9,8 seconds...
Well, it is not just reply but also any other table, say like Members table including hinders it as much as it was..
So I used UNION, it fastened it a lot, like 0,5 seconds for entire replies and topics search. However, this time, I can't take total results by changing select lines "SELECT COUNT( DISTINCT (t.TOPIC_ID))"
I will have to also combine those results with some other tables. I wonder how I can do it without interfering...
So only way I can take to make it search every page, by getting entire row numbers and calculate number of pages by ASP and make it research again?
Topic Locked
Posted - 21 December 2007 : 10:32:48
