I'm using Sql Server 2012 and I have a stored procedure that does about 15 left joins and one of those joins is a CROSS APPLY to a table-valued function. The first 14 left joins are not gonna restrict the results (they are left join so no rows will be ruled out) but the function could restrict records as it is a CROSS APPLY. Normally I would think that taking the CROSS APPLY to the top of the JOINs would be make it faster (we could deal with a small number of records sooner) but that's not real, at least in this case. If I take the CROSS APPLY to the top, the SP gets really, really slow. Why could this happen?
In order to give more context, the function is used to limit records based on entity permissions. Also, it is getting data from some tables that are also part of the joins I have in the SP. So, my first guess is that the JOINs in the SP are executed all together and the function is getting the data from the cache instead of disk. That could make sense but I'm just guessing.
I could do the JOINs that I have in the function in the SP but the problem is that security is managed by specifying what you can do so you could have 1 or more rules that tells you that you can see a record. Because of that, doing an INNER JOIN could cause duplicates so I could only convert the function to an EXISTS clause. The downside of this approach is that I cannot reuse the security function anymore (for the moment I'm not using from somewhere else).
I hope it's clear enough so somebody can help me :)