Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shrek1984
Starting Member
1 Post |
 Posted - 2005-08-20 : 04:06:55
|
| I'm fairly new to asp/sql server web site development and have struck a problem with a script that i am building. the code works fine, as long as there are no more than a few thousand records in the database, otherwise it times out. The problem is, I need it to work on millions of records.I have posted the code below and any idea's on making it work without timing out.private function GetResultsLike(byref recordset, search_phrase, search_array) Dim count, like_phrase, query, query1, query2, query3, value like_phrase = GetLikePhrase(search_phrase) query1 = "select [key], 0 from [Search] where [text] like " & like_phrase & " or [title] like " & like_phrase 'response.Write vbcrlf & "<br>it is " & query1 if UBound(search_array) > 0 then query2 = "select [key], 1 from [Search] where " for count = 0 to UBound(search_array) if count > 0 then query2 = query2 & " and" end if like_phrase = GetLikePhrase(search_array(count)) query2 = query2 & " [text] like " & like_phrase next query2 = query2 & " and [key] not in (select [keySearch] from [SearchResult])" else query2 = "" end if 'response.Write "<br>it is " & query2 if DataBaseType = 0 then 'Access DB m_database.Execute "delete * from [SearchResult]" m_database.Execute "insert into [SearchResult] (keySearch, pageRank) " & query1 'response.write "<br>insert into [SearchResult] (keySearch, pageRank) " & query1 if Len(query2) > 0 then query2 = "insert into [SearchResult] (keySearch, pageRank) " & query2 m_database.Execute query2 end if else 'SQL SERVER 200 DB m_database.Execute "begin tran truncate table [SearchResult] commit tran" m_database.Execute "begin tran insert into [SearchResult] " & query1 & " commit tran" 'response.Write "begin tran insert into [SearchResult] " & query1 & " commit tran" if Len(query2) > 0 then query2 = "begin tran insert into [SearchResult] " & query2 & " commit tran" m_database.Execute query2 end if end if RankResults search_phrase, search_array query = "select [Search].[title], [Search].[text], [Search].[url] from [Search], [YiderResult] where [YiderResult].[keyYider]=[Search].[key] order by [YiderResult].[pageRank] desc" recordset.open query, m_database g_open = g_open + 1 'this recordset is closed in DisplayResults 'response.write recordset(1) GetResultsLike = Array(true, 0) end functionThanks in advanceDavid M Gilbert. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-20 : 04:48:18
|
Hi shrek1984, Welcome to SQL Team!I would remove the transaction stuff from the single statements - they aren't doing anything especially, and may be slowing things down:begin tran truncate table [SearchResult] commit tranPlus I don't think that approach is going to work for multiple users?select [key], 0 from [Search] where [text] like " & like_phrase & " or [title] like " & like_phraseThe pair of LIKE statements here are quite "expensive". However, I would still expect it to be fairly instant on a million rows.Rather than using your intermediate search result table you could do it in one query, which would be faster (and not suffer from multi-user interference)In essence you would need a query like:(I presume that YiderResult is the same table as SearchResult??)select [Search].[title], [Search].[text], [Search].[url] from [Search] JOIN ( select [key], 0 AS [pageRank] from [Search] where [text] like 'foo' or [title] like 'bar' UNION ALL select [key], 1 AS [pageRank] from [Search] where XXX = 'foo' and [text] like 'bar'-- and [key] not in (select [keySearch] from [SearchResult]) ) AS SearchResult ON [SearchResult].[key]=[Search].[key] order by [SearchResult].[pageRank] desc However, this will NOT exclude items which rank as "0" from also being included with a rank of "1" - there's probably some smart way of doing that in one set, but I can't think of it!Kristen |
 |
|
|
|
|
|
|
|
SQL Server Time Out