question

David 2 1 asked Oct 8, '19

Most Efficient Method To Self-Reference A Large Table

Hi there,

I have a very large AccessInfo table which needs to reference it. However on the query it takes quite a time to return the results due to the size of the table. What is the most efficient way to query a self-referenced table. My current query is:

SELECT DISTINCT ai.UserID

FROM AccessInfo AS ai

INNER JOIN AccessInfo AS ai1 ON ai.HitsID = ai1.HitsID

INNER JOIN User AS u ON ai1.UserID = u.UserID;

TIA

table inner join size results reference

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Oct 09, 2019 at 11:02 AM

Do you have indexes in place on the fields you're joining on?

0 ·

David 2 1 ThomasRushton ♦♦ commented · Oct 09, 2019 at 12:06 PM

Thanks yes it is doing 2 index scans on IX_AccessInfo.

0 ·