请教各位高手，关于sql语句的性能问题. （分不够可在加）

Question

首先我觉得你可以把
SUM( isnull(OrderDetail.Price,0) )改为sum(OrderDetail.Price),空值相加应为0.
同时可以在多生成张中间表,最后作关联,建议减少表的关联,这样可能会快点,必要的时候用索引!

Answer 1

SELECT   isnull(SUM(OrderDetail.Price),0) AS SwedishAmount,
isnull(SUM( OrderDetail.Vat),0) AS SwedenVat,
payment.customerId,
payment.paymethodId
FROM OrderDetail LEFT JOIN  (Payment LEFT JOIN [Order] ON Payment.OrderId = [Order].OrderId )
ON OrderDetail.OrderId = Payment.OrderId 
LEFT JOIN Address ON OrderDetail.DeliveryAddressId = Address.AddressId 
left join (select * from payment where payment.paymethodi=133 and payment.paydate >'2002-02-02' and payment.paydate< '2002-04-02') temp4 on orderdetail.orderId = temp4.orderId
WHERE Payment.CustomerId = [Order].CustomerId 
AND Address.CountryId = 1
and payment.orderId = temp4.orderId
and payment.customerId = temp4.customerId
and payment.paymethodId = temp4.paymethodId
GROUP BY payment.customerId,payment.paymethodId

Answer 2

两个建议
从 payment 出发进行左连接 from payment left join ...
可以不用临时表，一句写成吧 或者 第二句中只用 #temp4

Answer 3

谢谢各位的回答。To pengdali(大力) :这样还是很慢。有什么更好的方法。另外ordreDetail是一张很大的表有千万条记录。
To  newdongkui(老乌鸦）：你的方法试过了比原来的还要慢～～，有没有更好的解决方法。

Answer 4

如果不考虑并发性，建议将这四个表所涉及的数据在程序开始时就做成临时表，再利用临时表JOIN，保证更快

Answer 5

To  zqllyh(学习Stupid As Pig中...) :
  如何建这四张临时表？orderDetail可是很大的一张表，如果条件不足会很慢的。

Answer 6

利用 orderdetail.orderId = temp4.orderId　这个建ORDER在DETAIL的临时表
如
select * from orderdetail where orderdetail.orderId in (select orderId from #temp4)

Answer 7

O，奇怪，你已利用payment生成#TEMP4，为什么语句中还要加payment？

Answer 8

还有，应该FROM  payment 再LEFT JOIN  OrderDetail，以更少的去JOIN多的。要不把结构的SQL贴出来，让大家帮你重写这一句。

Answer 9

如果语句中不用payment速度就变得很慢。我不知道这句话“要不把结构的SQL贴出来，让大家帮你重写这一句。” 是什么意思？ 是需要贴什么给你？

Answer 10

这是我最初的一个存储过程：不管数据大小都很慢，最少3分钟以上。
CREATE PROCEDURE usp_PaymentPerCustomer (
@PayMethodId smallint,
@PayDateFrom datetime,
@PayDateTo datetime
)
AS
BEGIN
SELECT  SUM( isnull(OrderDetail.Price,0) ) AS SwedishAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS SwedenVat,
payment.customerId,
payment.paymethodId
INTO #temp1
FROM OrderDetail LEFT JOIN  (Payment LEFT JOIN [Order] ON Payment.OrderId = [Order].OrderId )
ON OrderDetail.OrderId = Payment.OrderId 
LEFT JOIN Article ON OrderDetail.ArticleId = Article.ArticleId
LEFT JOIN Address ON OrderDetail.DeliveryAddressId = Address.AddressId 
WHERE Payment.CustomerId = [Order].CustomerId 
AND Payment.PaymethodId = @PayMethodId 
AND Address.CountryId = 1
AND datediff(day, Payment.PayDate, @PayDateTo) >0
AND datediff(day, Payment.PayDate, @PayDateFrom)< 0
GROUP BY payment.customerId,
payment.paymethodId

--select * from #temp1

SELECT  SUM( isnull(OrderDetail.Price,0) ) AS ForeignAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS ForeignVat,
payment.customerId,
payment.paymethodId
INTO #temp2
FROM  OrderDetail LEFT JOIN  (Payment LEFT JOIN [Order] ON Payment.OrderId = [Order].OrderId )
ON OrderDetail.OrderId = Payment.OrderId 
LEFT JOIN Address ON OrderDetail.DeliveryAddressId = Address.AddressId 
WHERE Payment.CustomerId = [Order].CustomerId 
AND Payment.PaymethodId = @PayMethodId 
AND Address.CountryId <> 1
AND datediff(day, Payment.PayDate, @PayDateTo) >0
AND datediff(day, Payment.PayDate, @PayDateFrom)< 0
GROUP BY payment.customerId,
payment.paymethodId

--select * from #temp2
 
SELECT DISTINCT 
Customer.CustomerId as CustomerId,
ISNULL(Customer.FirstName,'') + ' ' + ISNULL(Customer.LastName,'') as CustomerName,
payMethod.Paymethod,
payMethod.PaymethodID
INTO #temp3
FROM  (Payment LEFT JOIN customer ON Payment.CustomerID = Customer.CustomerId )
LEFT JOIN payMethod ON Payment.paymethodId = Paymethod.paymethodId
WHERE  Payment.PaymethodId = @PayMethodId 
AND datediff(day, Payment.PayDate, @PayDateTo) >0
AND datediff(day, Payment.PayDate, @PayDateFrom)< 0 SElECT  isnull(#temp1.SwedishAmount,0) AS  SwedenAmount,
isnull(#temp1.SwedenVat,0) AS SwedenVat,
isnull(#temp2.ForeignAmount,0) AS ForeignAmount,
isnull(#temp2.ForeignVat,0) AS ForeignVat,
#temp3.*
FROM #temp3 LEFT JOIN #temp2 ON (#temp3.customerId = #temp2.customerId AND #temp3.paymethodId = #temp2.paymethodId)
LEFT JOIN #temp1 ON (#temp3.customerId = #temp1.customerId AND #temp3.paymethodId = #temp1.paymethodId)

DROP TABLE #temp1,#temp2,#temp3
END

GO

Answer 11

这是我改进的存储过程：数据小的时候一分钟内可以取出，当数据大的时候就会很慢很慢，3分钟以上～～
各位能帮我改改这个存储过程吗？ 谢谢。CREATE PROCEDURE usp_PaymentPerCustomer (
@PayMethodId smallint,
@PayDateFrom datetime,
@PayDateTo datetime
)
AS
BEGIN
select * into #temp4 from payment 
where payment.paymethodid = @PayMethodId 
and payment.paydate >@PayDateFrom
and payment.paydate < @PayDateTo SELECT  SUM( isnull(OrderDetail.Price,0) ) AS SwedishAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS SwedenVat,
payment.customerId,
payment.paymethodId
INTO #temp1
FROM OrderDetail LEFT JOIN  (Payment LEFT JOIN [Order] ON Payment.OrderId = [Order].OrderId )
ON OrderDetail.OrderId = Payment.OrderId 
LEFT JOIN Address ON OrderDetail.DeliveryAddressId = Address.AddressId 
left join #temp4 on orderdetail.orderId = #temp4.orderId
WHERE Payment.CustomerId = [Order].CustomerId 
AND Address.CountryId = 1
and payment.orderId = #temp4.orderId
and payment.customerId = #temp4.customerId
and payment.paymethodId = #temp4.paymethodId
GROUP BY payment.customerId,
payment.paymethodId

--select * from #temp1

SELECT  SUM( isnull(OrderDetail.Price,0) ) AS ForeignAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS ForeignVat,
payment.customerId,
payment.paymethodId
INTO #temp2
FROM  OrderDetail LEFT JOIN  (Payment LEFT JOIN [Order] ON Payment.OrderId = [Order].OrderId )
ON OrderDetail.OrderId = Payment.OrderId 
LEFT JOIN Address ON OrderDetail.DeliveryAddressId = Address.AddressId 
left join #temp4 on orderdetail.orderId = #temp4.orderId
WHERE Payment.CustomerId = [Order].CustomerId 
AND Address.CountryId <> 1
and payment.orderId = #temp4.orderId
and payment.customerId = #temp4.customerId
and payment.paymethodid = #temp4.paymethodId
GROUP BY payment.customerId,
payment.paymethodId

--select * from #temp2
 
SELECT DISTINCT 
Customer.CustomerId as CustomerId,
ISNULL(Customer.FirstName,'') + ' ' + ISNULL(Customer.LastName,'') as CustomerName,
payMethod.Paymethod,
payMethod.PaymethodID
INTO #temp3
FROM Payment LEFT JOIN customer ON Payment.CustomerID = Customer.CustomerId 
LEFT JOIN payMethod ON Payment.paymethodId = Paymethod.paymethodId
left join #temp4 on payment.paymethodId = #temp4.paymethodId
WHERE  payment.orderId = #temp4.orderId
and payment.customerId = #temp4.customerId SElECT  isnull(#temp1.SwedishAmount,0) AS  SwedenAmount,
isnull(#temp1.SwedenVat,0) AS SwedenVat,
isnull(#temp2.ForeignAmount,0) AS ForeignAmount,
isnull(#temp2.ForeignVat,0) AS ForeignVat,
#temp3.*
FROM #temp3 LEFT JOIN #temp2 ON (#temp3.customerId = #temp2.customerId AND #temp3.paymethodId = #temp2.paymethodId)
LEFT JOIN #temp1 ON (#temp3.customerId = #temp1.customerId AND #temp3.paymethodId = #temp1.paymethodId)

DROP TABLE #temp1,#temp2,#temp3,#temp4
END
GO

Answer 12

Payment这张表有
paymethodId（外键）， // 传入的参数
paydate，             // 传入的参数 < @todate and > @fromdate
orderId（外键 ），    // Payment.orderId = Order.OrderId
customerId（外键 ）   // Payment.customerId = order.customerIdOrderDetail这张表有
price，vat， 
orderId（外键 ），     // OrderDetail.orderId = payment.orderId
deliveryAddressId( 外键 ），// OrderDetail.DeliveryAddressId = Address.AddressIdAddress这张表有AddressId，countryId。Customer这张表有
CustomerId，
firstName，
lastNamepaymethod这张表有
PaymethodId，
paymethod当Address.CountryId ＝ 1时 我要得到sum（ orderdetail.price ) as SwedishAmount,sum( orderdetail.vat) as SwedenVat
当Address.CountryId <> 1时 我要得到sum（ orderdetail.price ) as ForeignAmount ,sum( orderdetail.vat) as ForeignVat最终我要取出：
sum（ orderdetail.price ) as SwedishAmount,
sum( orderdetail.vat) as SwedenVat,
sum（ orderdetail.price ) as ForeignAmount ,
sum( orderdetail.vat) as ForeignVat,
customerId //customer.customerId = payment.customerId
paymethodId  // paymethod.paymethodId = payment.paymethodId
paymethod

Answer 13

其实只要这一段能搞定，其他的都可以了，当然这里可能有很多重复的，先不考虑，只看着一段：（能不能有好的解决办法）
select * into #temp4 from payment 
where payment.paymethodid = @PayMethodId 
and payment.paydate >@PayDateFrom
and payment.paydate < @PayDateTo SELECT  SUM( isnull(OrderDetail.Price,0) ) AS SwedishAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS SwedenVat,
payment.customerId,
payment.paymethodId
INTO #temp1
FROM OrderDetail LEFT JOIN  (Payment LEFT JOIN [Order] ON Payment.OrderId = [Order].OrderId )
ON OrderDetail.OrderId = Payment.OrderId 
LEFT JOIN Address ON OrderDetail.DeliveryAddressId = Address.AddressId 
left join #temp4 on orderdetail.orderId = #temp4.orderId
WHERE Payment.CustomerId = [Order].CustomerId 
AND Address.CountryId = 1
and payment.orderId = #temp4.orderId
and payment.customerId = #temp4.customerId
and payment.paymethodId = #temp4.paymethodId
GROUP BY payment.customerId,
payment.paymethodId

Answer 14

显然你把问题搞复杂了，如果只要得到sum（ orderdetail.price ) as SwedishAmount,
sum( orderdetail.vat) as SwedenVat,(与下面两个值有啥区别？))
sum（ orderdetail.price ) as ForeignAmount ,
sum( orderdetail.vat) as ForeignVat,
customerId //customer.customerId = payment.customerId
paymethodId  // paymethod.paymethodId = payment.paymethodId
其实只涉及两个表
Payment这张表有
paymethodId（外键）， // 传入的参数
paydate，             // 传入的参数 < @todate and > @fromdate
orderId（外键 ），    // Payment.orderId = Order.OrderId
customerId（外键 ）   // Payment.customerId = order.customerIdOrderDetail这张表有
price，vat， 
orderId（外键 ）,deliveryAddressId( 外键 ）所以：
  select * into #temp4 from payment 
where payment.paymethodid = 133       //参数
and payment.paydate >'2002-02-02'    //参数
and payment.paydate < '2002-04-02'   //参数
select SUM( isnull(OrderDetail.Price,0) ) AS ForeignAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS ForeignVat,
#temp4.customerId,
#temp4.paymethodId,#temp4.orderId from OrderDetail left join #temp4 
               on OrderDetail.OrderId = #temp4.OrderId
     group by   OrderDetail.OrderId
应该可以得到结果，不知道我有没有搞清你的意思customerId ,paymethodId

Answer 15

当Address.CountryId = 1时，OrderDetail.DeliveryAddressId = Address.AddressId
得到前两个
当Address.CountryId<>1时， 得到后两个。
还有我要得到paymethod.Paymethod这个列。
所以这几张表都要用到。

Answer 16

当然就第一断而言，就三张表就够了，OrderDetail，payment 和Address

Answer 17

但你那语句显然很慢的（如果数据很大），orderDetail是一张千万的表。现在我的问题是如何优化我的语句，让它执行的更快～～
谢谢各位了。

Answer 18

To zqllyh(学习Stupid As Pig中...) :
         select * into #temp4 from payment 
where payment.paymethodid = 133       --参数
and payment.paydate >'2002-02-02'    --//参数
and payment.paydate < '2002-04-02'   --//参数
select   SUM( isnull(OrderDetail.Price,0) ) AS ForeignAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS ForeignVat,
#temp4.customerId,
#temp4.paymethodId
from OrderDetail left join #temp4 
         on OrderDetail.OrderId = #temp4.OrderId
     group by   #temp4.customerId,#temp4.paymethodId这一步执行很慢的，发费了3分52秒，有什么办法可改进的，

Answer 19

select * into #temp4 from payment  where payment.paymethodid = 133       --参数
and payment.paydate >'2002-02-02'   and payment.paydate < '2002-04-02'  order by orderid,paymethodid --//参数
select   SUM( isnull(OrderDetail.Price,0) ) AS ForeignAmount,
SUM( isnull(OrderDetail.Vat,0) ) AS ForeignVat,
#temp4.customerId,
#temp4.paymethodId
from #temp4 right join OrderDetail
         on OrderDetail.OrderId = #temp4.OrderId
      group by   #temp4.customerId,#temp4.paymethodId
 OrderDetail.OrderId 建索引。
OrderId，customerId,paymethodId 建索引
将*号指明字段，没必要的字段不要列出。3分多钟的确太慢了，要不看看能不能考虑把ORDERDETAIL的一些历史记录移走。

Answer 20

orderDetail的数据没办法移动～～这是最烦人的。
我试过指明字段，效果不明显。这些索引也都有了，还是慢。
有没有更好的方法建索引，或更好的解决方案。

Answer 21

--试下这个，你再稍改下。应该能行
select customerId,paymethod.paymethod,paymethod.paymethodId,
       SwedishAmount,SwedishAmount,ForeignAmount,ForeignVat
(select payment.customerId,@paymethodId as paymethodId ,
    sum( case Address.countryid 1 then orderdetail.price else 0 end ) as SwedishAmount, 
    sum( case Address.countryid 1 then orderdetail.vat else 0 end ) as SwedishAmount, 
    sum( case Address.countryid 0 then orderdetail.price else 0 end ) as ForeignAmount, 
    sum( case Address.countryid 1 then orderdetail.vat else 0 end ) as ForeignVat
from Payment 
inner join OrderDetail on OrderDetail.orderId = payment.orderId
inner join ( select AddressId, (case countryid when 1 then 1 else 0) as countryid from Address) as Address
         on OrderDetail.DeliveryAddressId = Address.AddressId
where paydate < @todate and paydate > @fromdate and Payment.paymethodId = @paymethodId
group by payment.customerId,Address.countryid ) as temp4
inner join paymethod and paymethod.paymethodId = temp4.paymethodId

Answer 22

谢谢各位的帮助，虽然没得到好的解决方法，
To newdongkui(老乌鸦)：
谢谢，我把我的语句精简了很多。～～～～～～～～～～～～～～～～～
我只好在程序里解决这个问题了。