Last post Aug 10, 2020 04:32 PM by imapsp
Aug 07, 2020 05:07 PM|vkagrawal|LINK
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?
Aug 07, 2020 05:57 PM|imapsp|LINK
I think an alternative for you would be to use the Apply operator. Ex:
from MyTable as t
from MyTable as m
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
Aug 09, 2020 07:37 AM|vkagrawal|LINK
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.
Aug 10, 2020 04:32 PM|imapsp|LINK
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.