Comparison of two SQL design scenarios

I have a buyer table buyer, about 10,000 records; a seller table sale, about 50,000 records. There is some correlation between the buyer and the seller, which is recorded in the partner table, and the key fields in the partner tables include Buyerid,saleid and Linkmanid, where Linkmanid is one of the sellers ' salesmen. There is also the concept of a contact in the system, for each buyer, if it and the seller in the partner table has records, take the partner form Linkmanid as the contact ID, if it and the seller in the partner table is not recorded, Then you need to find the seller's main salesman ID (each seller has 1 to n salespeople, and there is only one main salesman) to act as the contact ID.
In the program, often involves a query and a buyer has a business relationship with the list of sellers, which need to display contact information. There are now two scenarios:
(1) Generate a view with three columns in the view: Buyerid,saleid and Linkmanid. The view is easy to write, and the resulting view is easy to use in the program. However, if you do not add the query criteria, directly query this view, you will find 55 * 10,000 = 500 million records (seller and buyer table as Cartesian product). Although we actually use the time, certainly will add the query condition (does not add the query condition to be meaningless), but also seems to have the forgetting query condition to kill the server to be possible; In addition, a view, only to it with query conditions to use, is it a bit strange? SQL Server does not support the so-called parameter view after all.
(2) Write a function that passes in Buyerid and Saleid to Linkmanid as the return value. Although the function is good to write, but put in the query statement is very awkward, very uncomfortable to use.
The following is a pseudo-code for two scenarios:

Scenario 1 Using Views
2SELECT Pa. Buyerid, Pa. Saleid, US. UserName as Linkman, us. Mobile, US. Phone
3FROM Buyer
4 INNER JOIN Partner as PA on buyer.userid = Pa. Buyerid and Pa. Isbuyerfavorite=1
5 INNER JOIN Sale on Pa. Saleid = Sale.userid
6 INNER join Vlinkman as LM on buyer.userid = lm. Buyerid and Sale.userid = lm. Saleid
7 inner JOIN [user] as US on LM. Linkmanid = us. Userid
8
9--Scenario 2 using functions
10SELECT a.*, US. UserName as Linkman, us. Mobile, US. Phone from
11 (
SELECT Pa. Buyerid, Pa. Saleid, Getlinkmanid (Buyer.userid, Sale.userid) as Linkmanid
From Buyer
INNER JOIN Partner as PA on buyer.userid = Pa. Buyerid and Pa. Isbuyerfavorite=1
INNER JOIN Sale on Pa. Saleid = Sale.userid
(a)
17inner join [user] as us on a.linkmanid = us. Userid

In the beginning, I personally preferred scenario 1 because, as shown in pseudocode, using views makes it easier to write query statements and relate to other tables, and, from a running efficiency, I'm deeply skeptical about the efficiency of the function, and I think it's faster to use the view. As a result, when I tried to test both options in the project, I was surprised that the function method was much faster than the view method. At this time I am not convinced, busy to index The view, this is only to be realized, because I this view is used outside the connection (the buyer table and the seller table Cartesian product), so there is no way to index (if this is wrong, please correct me, when I indexed the error "Unable to create an index on the view, Because the view is not bound to the schema ", Baidu a bit, found that there are external connections in the view can not be indexed). Because the query on the view is slow, we can even see in the query plan of SQL Server that it lasy spool the [user] table to which the views are to be connected, and this step takes up the majority of the query time:

And the other one that I didn't think of before is that the efficiency of using the function is very high, the following is the percentage of CPU in the execution plan of scenario 2:

So, in the end, I still adopted the scheme 2, although the use of more than a layer of nesting, but both security or efficiency, are better than 1.

Comparison of two SQL design scenarios