Better way to filter data from a table

Question

User1501362304 posted

Hi,

I have a simple User table with below columns.

Id, Name, Age, Height, Income, MinAge, MaxAge, MinHeight, MaxHeight, MinIncome, MaxIncome

Here first 5 columns can be considered as user's own detail and rest of the columns as user's preferences.

I want to fetch records from this table for a given user whose preferences match (columns 6 to 11) with simple values (columns 3 to 5)
So, for given user 1, his MinAge and MaxAge expectation should match with other users' Age (Age between MinAge and MaxAge) likewise for height and income. 

To get this, should I first fetch preference data for given user (user 1 in above example) and store in local variables and use them in another Select statement where id is not given user id or there is some other clean and better way of doing this?

Thanks

Answer 1

User452040443 posted

Hi,

I think an alternative for you would be to use the Apply operator. Ex:

select ca.*
from MyTable as t
cross apply
(
    select m.* 
    from MyTable as m
    where 
        m.Id <> t.Id and 
        m.Age between t.MinAge and t.MaxAge and
        m.Height between t.MinHeight and t.MaxHeight and
        m.Income between t.MinIncome and t.MaxIncome
) as ca

Hope this help

Answer 2

User1501362304 posted

Hi,

Thanks for the reply and solution. I read about it and it seems similar to inner join. Can you please clarify what would be impact on performance if we use Cross Apply approach vs fetch particular user's data first and store in local variable approach in terms of cost estimation.

Thanks

Answer 3

User452040443 posted

Hi,

I believe that you better do tests to see the difference.

In general, the execution of a single instruction tends to cost less than two instructions, although in your case the difference may be very small.