How to compare records in one table quickly?

Question

I have a project for literature researches with a table(in SQL Server 2014). I extract words from some books to comparison table. this table may have thousands of words. I write a query to compare words in different documents and want to show them side by side to show that each word frequency in different book. Write this:

SELECT c1.Word,c1.DocumentID,c1.WordCount,c2.DocumentID,c2.WordCount 
FROM dbo.Comparation c1 
JOIN dbo.Comparation c2 ON c2.Word = c1.Word AND c1.DocumentID <> c2.DocumentID

this query takes 15s on my machine. then i decided to show them page by page and write this:

SELECT c1.Word,c1.DocumentID,c1.WordCount,c2.DocumentID,c2.WordCount 
FROM dbo.Comparation c1 
JOIN dbo.Comparation c2 ON c2.Word = c1.Word AND c1.DocumentID <> c2.DocumentID 
ORDER BY c1.DocumentID OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY;

now for 15 or little more records speed of retrieve was good, but my problem is: when offset is small, every things are OK but when i increase offset query runs slow. Is there any way to load and compare these words fast?

Thank you.

With best regards.

Comparison table

Have you any indexes on that table? If not, create it, `create index ix_ Comparation__Word__DocumentID on Comparation (Word, DocumentID);` — , Mar 05 '16 at 07:50
Dear Evgeny, Thank you for your suggestion. I create that index, speed increased a little. but still takes 4-5 seconds. — AliReza, Mar 05 '16 at 08:04
Dear Ivan, this query used for an WinForms application. A researcher may compare lots of books any time! Thank you. — AliReza, Mar 05 '16 at 08:10
`c1.DocumentID <> c2.DocumentID` -> `c1.DocumentID > c2.DocumentID` — Ivan Starostin, Mar 05 '16 at 10:30

score 0 · Accepted Answer · edited May 23 '17 at 11:45

0

It looks like you are joining on the nvarchar column "Word". As explained here this has performance issues when used over a large number of rows.

Try maybe generating an extra "WordID" column which would be unique for each word and then joining on that.

edited May 23 '17 at 11:45

Community

1
1

answered Mar 05 '16 at 11:43

ChrisS

376
1
11

Thank you dear ChrisS, i'll try it and tell you it's results. Thank you again. – AliReza Mar 05 '16 at 17:55

How to compare records in one table quickly?

1 Answers1