Very slow filtering and ordering big table by set in SQL Server

Question

I have a SQL Server database (versions 2012, 2014) and have a table with many attributes (ca 50). The table has 50,000 rows.

I want to filter and order this table, for example:

select id, ... 
from mytable 
where attr = 3 
order by id

This is not slow, it is working well.

But I have a need to filter out the rows which the user cannot see. There exists a view, which generates a table with ids which the user can see.

Then comes the simple join:

select id, ... 
from mytable
inner join can_see_view on mytable.id = can_see_view.id
where attr = 3 
order by id

This is somewhat very slow. The execution plan is:

I tried to save the can_see ids into a temporary table (@ids) - this is visible on the execution plan.

Whatever I do (inner join, left outer join, intersect, in, exists) it is slow as hell.

How to optimize this? Maybe add an index? Or some other trick??

Edit: sort tooltip:

Edit - sample data

create table dbo.bigtable (
    id int not null identity (1,1),
    attr1 varchar(4000) null,
    attr2 varchar(4000) null,
    attr3 varchar(4000) null,
    attr4 varchar(4000) null,
    attr5 varchar(4000) null,
    attr6 varchar(4000) null,
    attr7 varchar(4000) null,
    attr8 varchar(4000) null,
    attr9 varchar(4000) null,
    attr10 varchar(4000) null,
    attr11 varchar(4000) null,
    attr12 varchar(4000) null,
    attr13 varchar(4000) null,
    attr14 varchar(4000) null,
    attr15 varchar(4000) null,
    attr16 varchar(4000) null,
    attr17 varchar(4000) null,
    attr18 varchar(4000) null,
    attr19 varchar(4000) null,
    attr20 varchar(4000) null,
    attr21 varchar(4000) null,
    attr22 varchar(4000) null,
    attr23 varchar(4000) null,
    attr24 varchar(4000) null,
    attr25 varchar(4000) null,
    attr26 varchar(4000) null,
    attr27 varchar(4000) null,
    attr28 varchar(4000) null,
    attr29 varchar(4000) null,
    attr30 varchar(4000) null,
    attr31 varchar(4000) null,
    attr32 varchar(4000) null,
    attr33 varchar(4000) null,
    attr34 varchar(4000) null,
    attr35 varchar(4000) null,
    attr36 varchar(4000) null,
    attr37 varchar(4000) null,
    attr38 varchar(4000) null,
    attr39 varchar(4000) null,
    attr40 varchar(4000) null,
    attr41 varchar(4000) null,
    attr42 varchar(4000) null,
    attr43 varchar(4000) null,
    attr44 varchar(4000) null,
    attr45 varchar(4000) null,
    attr46 varchar(4000) null,
    attr47 varchar(4000) null,
    attr48 varchar(4000) null,
    attr49 varchar(4000) null,
    attr50 varchar(4000) null
)

GO

create function canseefunc ()
returns @result table (id int null)
as
begin
insert into @result(id)
    select id from bigtable -- no logic, can see everything
    return
end

create view dbo.cansee
as
    select id from canseefunc()
    --select id from bigtable

GO

declare @i int
set @i = 0

while (@i < 50000)
begin
    insert into dbo.bigtable (attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10,
    attr11, attr12, attr13, attr14, attr15, attr16, attr17, attr18, attr19, attr20,
    attr21, attr22, attr23, attr24, attr25, attr26, attr27, attr28, attr29, attr30,
    attr31, attr32, attr33, attr34, attr35, attr36, attr37, attr38, attr39, attr40,
    attr41, attr42, attr43, attr44, attr45, attr46, attr47, attr48, attr49, attr50)
    values (
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()),
    CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()), CONVERT(varchar(255), NEWID()))

    set @i = @i + 1
end

GO

select b.id 
from bigtable as b
inner join cansee as c on b.id = c.id
where attr41 <> 'aa'
order by b.id, b.attr10

As you can see, I am using a function, which computes the ids which the user can see. If I remove the function and simple use the view directly, without calling the function, it is not slow! Why is this?

Answer 1

Your order by is the perf killer here. Are you using an indexed column from mytable If id is your PK for mytable then it shouldn't be an issue.

Otherwise you can add an index. CREATE NONCLUSTERED INDEX ix_can_see_view_id ON can_see_view (id);

In your case, you can also avoid the join by using a in clause like this:

SELECT
    t.id
FROM
    mytable t
WHERE
    t.attr = 3
    AND t.id IN (SELECT * FROM can_see_view)
ORDER BY
    t.id;

Plus the join condition column should have the exact same datatype including size/precision to get the best performance.