SQL Server search filter and order by performance issues

Question

We have a table value function that returns a list of people you may access, and we have a relation between a search and a person called search result.

What we want to do is that wan't to select all people from the search and present them.

The query looks like this

SELECT qm.PersonID, p.FullName 
FROM QueryMembership qm
INNER JOIN dbo.GetPersonAccess(1) ON GetPersonAccess.PersonID = qm.PersonID
INNER JOIN Person p ON p.PersonID = qm.PersonID
WHERE qm.QueryID = 1234

There are only 25 rows with QueryID=1234 but there are almost 5 million rows total in the QueryMembership table. The person table has about 40K people in it.

QueryID is not a PK, but it is an index. The query plan tells me 97% of the total cost is spent doing "Key Lookup" witht the seek predicate.

QueryMembershipID = Scalar Operator (QueryMembership.QueryMembershipID as QM.QueryMembershipID)

Why is the PK in there when it's not used in the query at all? and why is it taking so long time?

The number of people total 25, with the index, this should be a table scan for all the QueryMembership rows that have QueryID=1234 and then a JOIN on the 25 people that exists in the table value function. Which btw only have to be evaluated once and completes in less than 1 second.

Answer 1

You should define indexes on the tables you query. In particular on columns referenced in the WHERE and ORDER BY clauses.

Use the Database Tuning Advisor to see what SQL Server recommends.

Answer 2

You need indexes on your WHERE and ORDER BY clauses. I am not an expert but I would bet it is doing a table scan for each row. Since your speed issue is resolved by Removing the INNER JOIN or the ORDER BY I bet the issue is specifically with the join. I bet it is doing the table scan on your joined table because of the sort. By putting an index on the columns in your WHERE clause first you will be able to see if that is in fact the case.

Answer 3

For specifics, of course you would need to post your query and table design.

But I have to make a couple of points here:

• You've already jumped to the conclusion that the slowness is a result of the ORDER BY clause. I doubt it. The real test is whether or not removing the ORDER BY speeds up the query, which you haven't done. Dollars to donuts, it won't make a difference.

• You only get the "log n" in your big-O claim when the optimizer actually chooses to use the index you defined. That may not be happening because your index may not be selective enough. The thing that makes your temp table solution faster than the optimizer's solution is that you know something about the subset of data being returned that the optimizer does not (specifically, that it is a really small subset of data). If your indexes are not selective enough for your query, the optimizer can't always reasonably assume this, and it will choose a plan that avoids what it thinks could be a worst-case scenario of tons of index lookups, followed by tons of seeks and then a big sort. Oftentimes, it chooses to scan and hash instead. So what you did with the temp table is often a way to solve this problem. Often you can narrow down your indexes or create an indexed view on the subset of data you want to work against. It all depends on the specifics of your wuery.

Answer 4

Have you tried restructuring the query into a CTE to separate the TVF call? So, something like:

With QueryMembershipPerson
    (
    Select QM.PersonId, P.Fullname
    From QueryMembership As qm
        Join Person As P
            On P.PersonId = QM.PersonId
    Where QM.QueryId = 1234
    )
Select PersonId, Fullname
From QueryMembershipPerson As QMP
    Join dbo.GetPersonAccess(1) As PA
        On PA.PersonId = QMP.PersonId

EDIT: Btw, I'm assuming that there is an index on PersonId in both the QueryMembership and the Person table.

EDIT What about two table expressions like so:

With 
    QueryMembershipPerson As
    (
    Select QM.PersonId, P.Fullname
    From QueryMembership As qm
        Join Person As P
            On P.PersonId = QM.PersonId
    Where QM.QueryId = 1234
    )
    , With PersonAccess As
    (
    Select PersonId
    From dbo.GetPersonAccess(1) 
    )
Select PersonId, Fullname
From QueryMembershipPerson As QMP
    Join PersonAccess As PA
        On PA.PersonId = QMP.PersonId

Yet another solution would be a derived table like so:

Select ...
From  (
        Select QM.PersonId, P.Fullname
        From QueryMembership As qm
            Join Person As P
                On P.PersonId = QM.PersonId
        Where QM.QueryId = 1234
        ) As QueryMembershipPerson
    Join dbo.GetPersonAccess(1)  As PA
        On PA.PersonId = QueryMembershipPerson.PersonId

If pushing some of the query into a temp table and then joining on that works, I'd be surprised that you couldn't combine that concept into a CTE or a query with a derived table.

Answer 5

if you want to avoid "key lookup", use covered index

create index ix_QueryMembership_NameHere on QueryMembership (QueryID)
include (PersonID);

add more column names, that you gonna select in include arguments.

for the point that, why PK's "key lookup" working so slow, try DBCC FREEPROCCACHE, ALTER INDEX ALL ON QueryMembership REBUILD, ALTER INDEX ALL ON QueryMembership REORGANIZE

This may help if your PK's index is disabled, or cache keeps wrong plan.