用了游标 查询变得很慢.求优化

Question

--------------------------------------------------这是查询语句--------------------------------------
SELECT  a.ReceiptNO,
                                    a.ReceiptDate,
                                    a.Sender,
                                    a.Receiver,
                                    a.Maker,
                                    a.Re,
                                    a.LatestReceiveDate,
                                    a.TransportationNo,
                                    a.DeliveryMan,
                                    a.PdtName,
                                    a.NumUnit,
                                    a.DeclarationNO,
                                    b.Name as SenderName,
                                    b.ShortName as ShortSenderName,
                                    c.Name as ReceiverName,
                                    c.ShortName as ShortReceiverName,
                                    dbo.getReceiptInvoice(a.ReceiptNO) As InvoiceNos
                              FROM tblBizReceiptBill a left join 
                                    tblCRMManagementUnit b on a.Sender = b.Id left join 
                                    tblCRMCustomer c on a.Receiver = c.Id  WHERE 1=1 
---------------------------------------------自定义函数------------------------------------------USE [tcliedms]
GO
/****** 对象:  UserDefinedFunction [dbo].[getReceiptInvoice]    脚本日期: 03/27/2012 09:40:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   function    [dbo].[getReceiptInvoice](@no nvarchar(50))   
  RETURNS   varchar(250)   
  as  
begin  
declare @strinvoice nvarchar(250)
declare @invoiceno nvarchar(50) --发票编号
    set @strinvoice = '' 
  
    
declare cur cursor for 
    select invoiceno from tblBizReceiptBillDetail where ReceiptNO = @no order by InvoiceNO open cur
fetch next from cur INTO @invoiceno 
    while @@fetch_status = 0
begin
set @strinvoice = @strinvoice + ',' + @invoiceno
FETCH NEXT FROM cur INTO @invoiceno
end
close cur if(len(@strinvoice) > 0)
begin
set @strinvoice = substring(@strinvoice, 2, 250)
end
return   @strinvoice   
END

Answer 1

尽量不要使用游标，特别是数据量大的时候，前提：如果有其它方法。如果游标里的处理可以抽象出一个sql语句或多个sql组合时，可以放弃游标，而不是在游标里挨个处理