I have a buyer table buyer with about 10 thousand records, and a seller table sale with about 50 thousand records. There are some associations between buyers and sellers. Such associations are recorded in the partner table. The key fields in the partner table include buyerid, saleid, and linkmanid, among them, linkmanid is one of the seller's sales staff. There is also a concept of contact in the system. For each buyer, if it has a record with the seller in the partner table, the linkmanid in the partner table will be taken as the contact ID; if there is no record between the seller and the seller in the partner table, you need to find the ID of the seller's main salesman (each seller has one to N salesmen, and one of them has only one main salesman) as the Contact ID.
In Program It is often involved in querying the list of sellers that have business relationships with a buyer. The contact information must be displayed in the list. There are two solutions:
(1) generate a view with three columns: buyerid, saleid, and linkmanid. Views are easy to write, and the formed views are easy to use in the program. However, if you directly query this view without adding query conditions, you will find 10 thousand records (the seller and the buyer table are cartesian products ). In actual use, we will certainly add query conditions (it is meaningless to not add query conditions), but it also seems that the server may be killed by forgetting to add query conditions. In addition, A view can be used only when a query condition is added to it. Is it a bit strange? After all, sqlserver does not support the parameter view.
(2) Write a function, pass in the buyerid and saleid, and use linkmanid as the return value. Although the function is easy to write, it is very awkward to put it in the query statement, and it is very uncomfortable to use.
The following are two false solutions: Code : 1 -- Solution 1 usage View
2 Select Pa. buyerid, Pa. saleid, US. Username As Linkman, US. Mobile, US. Phone
3 From 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 -- Solution 2 use functions
10 Select A. * , US. Username As Linkman, US. Mobile, US. Phone From
11 (
12 Select Pa. buyerid, Pa. saleid, getlinkmanid (buyer. userid, sale. userid) As Linkmanid
13 From Buyer
14 Inner Join Partner As Pa On Buyer. userid = Pa. buyerid And Pa. isbuyerfavorite = 1
15 Inner Join Sale On Pa. saleid = Sale. userid
16 )
17 Inner Join [ User ] As Us On A. linkmanid = Us. userid
In the beginning, I personally prefer solution 1, because, as shown in the pseudo code, it is easier to use views to write query statements and associate them with other tables. In addition, in terms of operation efficiency, I am deeply skeptical about the Function operation efficiency and think that using a view is faster. As a result, when I tried to experiment with both solutions in the project, I was surprised that the speed of the function method was much higher than that of the view method. At this time, I was not convinced that I was busy adding indexes to the view. I was surprised that this view was obtained by using external connections (the buyer table and the seller table Cartesian Product ), therefore, you cannot create an index at all. (if this is incorrect, please correct me. When I create an index, the following error occurs: "You cannot create an index on the view because the view is not bound to the architecture ", baidu once found that there are external connections in the view, the index cannot be created ). View query is slow. In the SQL Server query plan, we can even see that it performs lasy spool on the [user] Table to be connected to the view, this step takes up most of the query time:
What I did not think of before is that the efficiency of using functions is very high. below is the percentage of CPU occupied by scalar computing in the execution plan of solution 2:
Therefore, I finally adopted solution 2. Although there is an additional layer of nesting, both security and efficiency are better than 1.
