比如将如下的连接查询 Select C.CustomerID C.CompanyName,C.ContactName,C.Phone,O.OrderID,O.EmployeeID,O.OrderDate,D.ProductID,D.Quantity,D.UnitPrice From [Order Details] D,Orders O,Customers C Where D.OrderID= O.OrderID and O.CustomerID =C.CustomerID AND C.Country=N'USA' 改为像下面的这些查询: Select CustomerID,CompanyName,ContactName,Phone From Customers Where Country = N'USA'Select O.OrderID,O.CustomerID,O.EmployeeID,O.OrderDate From Orders O,Customers C Where O.CustomerID = C.CustomerID AND C.Country = N'USA' Select D.OrderID, D.ProductID ,D.QUantity,D.UnitPrice From [Order Details] D ,Orders O,Customers C Where D.ORderID = O.OrderID And O.CUstomerID =C.CustomerID And C.Country =N'USA'
把Inner Join改成多个单表查询的语句 可能快些吧!
Select C.CustomerID C.CompanyName,C.ContactName,C.Phone,O.OrderID,O.EmployeeID,O.OrderDate,D.ProductID,D.Quantity,D.UnitPrice
From [Order Details] D,Orders O,Customers C
Where D.OrderID= O.OrderID and O.CustomerID =C.CustomerID AND C.Country=N'USA'
改为像下面的这些查询:
Select CustomerID,CompanyName,ContactName,Phone
From Customers Where Country = N'USA'Select O.OrderID,O.CustomerID,O.EmployeeID,O.OrderDate
From Orders O,Customers C
Where O.CustomerID = C.CustomerID AND C.Country = N'USA'
Select D.OrderID, D.ProductID ,D.QUantity,D.UnitPrice
From [Order Details] D ,Orders O,Customers C
Where D.ORderID = O.OrderID And O.CUstomerID =C.CustomerID And C.Country =N'USA'
写太多的join,这种太大的query在被执行前可能还会被重新解析,
有时候查询优化器都会晕掉的,这些join要在内存里面要做大量的排序和合并,
如果内存不够,就要换出其他内存里面的东西,腾出空间,导致更多换页和物理IO.....
所以肯定慢的要死。如果有相关ID关联到每个字表的话,多写几个小的查询比较好,而且容易维护。另外,使用视图的一个最大问题是,视图有时候不能很好地利用子表的索引,同样就快不起来。
但是如果要建索引视图的话,限制太多了,基本是不可行的。
那我取数据的时候用多个存储过程取吗?这样一个一个select返回给应用程序,比如c#,的时候好象dataset里面好象是只能返回你最后的select取出的结果.我要掉N次存储过程,然后将结果合并?