I currently build a text processing database for my application.
The database is to store all the plain text files submitted by customers. The plain text files will be processed before it store in the sql server. Thus I have 3 major tables Document, Term and TermDocument.
I encounter a problem when I did a query. For example when I performed the following query
SELECT T.TermName, S.* FROM Weight AS S INNER JOIN Term AS T ON S.TermID = T.TermID WHERE T.TermName IN ('electronic','commerce','consists', ....)
The system told me that "No enough storage is available to complete the operation". The terms in "IN(....)" could have up 3k entries. I know this is huge, but I have no choice but to retrieve all of them in a single query.
Is there anyway to solve the problem or perform similar operation in alternative way.
Maybe it would be easier to use reverse logic?
If possible you could reverse the IN to a NOT IN any maybe the list is shorter then (assumption).
You could also insert the 3000 terms in a temporary table and join with that table instead of using the IN clause.
Anyway, I'm not too fond of queries with 3000 entries in the WHERE clause to be honest.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||I agree that its best to go down the table route, either with a temp table or perhaps a table valued UDF.
There are some good examples of the split function referenced in this thread which may suit your needs as it will enable you to convert a string of terms in to a table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1847071&SiteID=1
Hope that makes sense.
No comments:
Post a Comment