I have a system to teach English. For each word someone learns, I record:
LearnedWords -- Id -- UserId -- WordId
And for each new text that I give to a learner, I want to indicate what words are new for him.
So, imagine a learner has learned 15000 words. So there are 15000 records for him only in the
LearnedWords. Now he chooses to read a new text that has over 2000 words. All I have is textual data here, that is, words.
From these 2000 words, let's say only 300 words are new. How can I find them? The only way that I can think of is to do set operations based on Venn Diagram. That is, using
not in clause. So, I create a procedure that takes the 2000 words as input, creates a temporary table and populates it with the given text words, selects learned words from database, and then executes the
not in query:
declare @textWords table ( Word nvarchar(100) ) -- populate @textWords with 2000 given words here declare @learnedWords table ( Word nvarchar(100) ) declare @newWords table ( Word nvarchar(100) ) insert into @learnedWords (Word) select Words.[Text] from LearnedWords inner join Words on LearnedWords.WordId = Words.Id where LearnedWords.UserId = 1 --> user with 15000 learned words insert into @newWords (Word) -- filters 300 new words for the given user select Word from @textWords -- has 2000 records where Word not in ( select Word from @learnedWords -- has 15000 records for the given user )
However, this procedure takes 14 seconds to complete. It's a nightmare. I ran Tuning Advisor and applied recommendations. But it only improved to 13 seconds. I'm stuck at how to meet this business requirement in data layer with a good performance. Any help?