关于一个多表交叉读出数据的效率问题,是先全写成视图，然后用存储过程分页读取，还是直接用存储过程拼接这么复杂的sql 语句分页显示好呢？

 

string sql= "SELECT A.Company_ID,A.Company_Name,A.Company_Attribute,A.Company_Scale,"+
        "B.Pub_Position_ID,B.Company_ID,B.Pub_Position_Name,"+
                    "B.Pub_Position_Number,B.Pub_Position_Age,B.Pub_Position_Sex,B.Pub_Position_Academic,"+
     
         "Pub_Position_Date = replace(convert(varchar(10),B.Pub_Position_Date,102),'.','-'), "+
        

        "B.Pub_Position_Work_Area_Province,B.Pub_Position_Work_Area,B.Pub_Position_Salary,"+
        "AgeRange=cast(B.Pub_Position_MinAge as varchar)+'-'+cast(B.Pub_Position_MaxAge as varchar),"+
      
         "valid_date =convert( varchar(10), dateadd( month,B.Pub_Position_Age,B.Pub_Position_Date),120), "+
        "C.Academic_Credentials_Name,C.Academic_Credentials_Code,"+
        "D.Company_Attribute_Name,D.Company_Attribute_Code,"+
        "E.Company_Scale_Name,E.Company_Scale_Code, "+
        "F.Area_Name,F.Area_Code, "+
        "G.Salary_Name,G.Salary_Code, "+
        "H.Sex_Name,H.Sex_Code "+
        "FROM Company_Info AS A ,"+
        "Company_Pub_Position AS B ,"+
        "Sub_Academic_Credentials1 AS C, "+
        "Sub_Company_Attribute As D, "+
        "Sub_Company_Scale AS E,"+
        "Sub_Province_City_Short AS F,"+
        "Sub_Salary_Range1 AS G ,"+
        "Sub_Sex_Range1 As H "+
        "WHERE A.Company_ID=B.Company_ID "+
        "AND B.Pub_Position_Academic=C.Academic_Credentials_Code "+
        "AND A.Company_Attribute=D.Company_Attribute_Code "+
        "AND A.Company_Scale=E.Company_Scale_Code "+
                                "AND B.Pub_Position_Sex=H.Sex_Code "+
        "AND B.Pub_Position_Work_Area_Province=F.Area_Code "+
        "And B.Pub_Position_Salary=G.Salary_Code "+
        "ORDER BY Pub_Position_Date DESC";

-------------------------------------

方案一：

先把这个sql 语句在数据库中做成视图 VIEW1 ,然后，再用存储过程分页读取 视图 VIEW1 中的数据(sql库中是不是对视图中的数据做过处理，比如缓存，优化之类的？)

方案二：

直接用存储过程拼接这一大段 sql 语句分页读数据

以上这两 种方案哪一种效果好呢，从效率上和性能上各方面综合考虑

 

 

看情况选择，视图在查询方面比较快些

常用频繁的查询，用视图.