链接服务器高难问题（两端ADSL）
（两端ADSL）已经建立链接服务器srv_lnk，本地建立了一个存储过程，执行的时候
非常慢，我已经等了几十分钟，还没有结果，数据量并不大，大概每个表300条数据，存储过程为PROC_MOVEATOB，执行为：
DECLARE @A VARCHAR(50)
EXEC PROC_MOVEATOB 'A ', 'A ', '2007-03-01 ',@A OUTPUT
部分：
CREATE PROCEDURE PROC_MOVEATOB(@FlagStr varchar(5),@BillBookNo varchar(20),@Time varchar(50),@MatchBillNo varchar(200) output)
AS
Declare @BillSale_Out varchar(50),
@OldTime int ,
@BillNo_Out varchar(20) ,
@BillType varchar(20)
Delete from ENTA.DBO.billsale where creater= '10000 ' and creattime=(convert(varchar(10),getdate(),126))
Delete from ENTA.DBO.billsaleproduct where creater= '10000 ' and creattime=(convert(varchar(10),getdate(),126))
Delete from ENTA.DBO.BillSaleIticket where creater= '10000 ' and creattime=(convert(varchar(10),getdate(),126))
Delete from ENTA.DBO.BillSaleDticket where creater= '10000 ' and creattime=(convert(varchar(10),getdate(),126))
set xact_abort on
Begin tran
Select @BillType= 'BillSale '
Exec PROC_BuildBillNo @BillType,@FlagStr,@OldTime Output ,@BillSale_Out Output
Set @MatchBillNo=@BillSale_Out /*单据编号*/
insert into ENTA.DBO.BillSale( BillNo ,UID, CustomerID , CustomerName, CustomerPersonId,CustomerPersonName,TotalAccount ,Creater, Grup )
values(@BillSale_Out, ' ', 'szid ' , '苏州东航 ', ' ', ' ', 0, '10000 ', 52)
Declare @a varchar(50),
@b varchar(50),
@NewBillSaleProductPIDNo varchar(50),
@c money,
@d money,
@all money
set @all=0
Declare BillBookproduct_Cursor CURSOR for
Select BillNo,BookPid,inprice,tax from srv_lnk.SZABC.DBO.BillSaleProduct where Creattime=@Time AND (PRODUCTID NOT LIKE '479 '+ '% ') AND (PRODUCTID NOT LIKE '731 '+ '% ')
Open BillBookproduct_Cursor
Fetch BillBookproduct_Cursor Into @a,@b,@c,@d
While (@@fetch_status <> -1)
begin
PRINT @A
PRINT @B
set @all=@all+@c+@d
Select @BillType= 'BillSaleProduct '
/*单据编号*/
--Exec PROC_BuildBillNo @BillType,@FlagStr,@OldTime Output ,@NewBillSaleProductPIDNo Output
insert into ENTA.DBO.BillSaleProduct(BillNo , BookPID , ProductName, Name ,
PreCode , Account , ProductID , Productrelation1 ,
Productrelation2 , ConjunctionTickets , Saleprice ,
WritePrice , Inprice , Payprice , Tax ,
Paycut, GetCut, CutInprice, DeadLine ,
Creater , Mender , DataUsable ,
Remark , Grup , profit ) (select @BillSale_Out,BookpId,ProductName, Name ,
PreCode , Inprice+Tax , ProductID , Productrelation1 ,
Productrelation2 , ConjunctionTickets , Saleprice ,
WritePrice , Inprice , Inprice+Tax , Tax ,
Paycut, GetCut, CutInprice, DeadLine ,
'10000 ' , Mender , DataUsable ,
Remark , 52 , profit
from srv_lnk.SZABC.DBO.billSaleProduct where BookPid=@b)
insert into ENTA.DBO.BillSaleDticket(BookPid,TicketId, RestrictionsEndorsement,
NameOfPassenger, TourCode, OrignDestination,
ConjunctionTickets, BookingReference,
IssuedInExchangeFor, EmployeeId, Place1,
DateAndPlaceOfIssue, Place3, PlaceCode2,
PlaceCode1, Place2, PlaceCode3,
Carrier1, Flight1, Carrier2, Flight2,
Class1, Date1, Class2, Date2,
Time1, Time2, Status1, Status2,
FareBasis1, FareBasis2, NotValidBefore1,
NotValidBefore2, NotValidAfter1, Allow1,
NotValidAfter2, Allow2, Fare,
EquivFarePD, Tax, Total, payprice,FareCalculation,
FormOfPayment, remark,Creater,
Grup) (select BookPid,TicketId, RestrictionsEndorsement,
NameOfPassenger, TourCode, OrignDestination,
ConjunctionTickets, BookingReference,
IssuedInExchangeFor, EmployeeId, Place1,
DateAndPlaceOfIssue, Place3, PlaceCode2,
PlaceCode1, Place2, PlaceCode3,
Carrier1, Flight1, Carrier2, Flight2,
Class1, Date1, Class2, Date2,
Time1, Time2, Status1, Status2,
FareBasis1, FareBasis2, NotValidBefore1,
NotValidBefore2, NotValidAfter1, Allow1,
NotValidAfter2, Allow2, Fare,
EquivFarePD, Tax, Total, payprice,FareCalculation,
FormOfPayment, remark, '10000 ',
52 from srv_lnk.SZABC.DBO.BillSaleDticket where BookPID=@b )
insert into ENTA.DBO.BillSaleIticket(BookPid, TicketId, RestrictionsEndorsement,
NameOfPassenger, TourCode, ConjunctionTickets,
OrignDestination, AirlineDate, IssuedinExchangeFor,
DateandPlaceOfIssue, Place1, EmployeeId,
Place2, Place4, Place3, PlaceCode1,
Place5, PlaceCode2 , PlaceCode3,
PlaceCode4, PlaceCode5, Carrier1,
Carrier2 , Carrier3 , Carrier4 , Flight1,
Flight2 , Flight3, Flight4 , Class1 ,
Class2, Class3 , Class4 , Date1 ,
Date2 , Date3 , Date4 , Time1 ,
Time2 , Time3, Time4 , Status1 ,
Status2 , Status3 , Status4 , FareBasis1 ,
FareBasis2, FareBasis3 , FareBasis4 ,
NotValidBefore1 , NotValidBefore2 , NotValidBefore3 ,
NotValidBefore4 , NotValidAfter1 , NotValidAfter2 ,
NotValidAfter3 , NotValidAfter4 , Allow1,
Allow2, Allow3 , Allow4, Fare ,
EquivFarePD , Tax1 , Tax2 , Tax3, payprice,
Total , FareCalculation ,
FormOfPayment,remark, Creater , Grup) (select BookPid, TicketId, RestrictionsEndorsement,
NameOfPassenger, TourCode, ConjunctionTickets,
OrignDestination, AirlineDate, IssuedinExchangeFor,
DateandPlaceOfIssue, Place1, EmployeeId,
Place2, Place4, Place3, PlaceCode1,
Place5, PlaceCode2 , PlaceCode3,
PlaceCode4, PlaceCode5, Carrier1,
Carrier2 , Carrier3 , Carrier4 , Flight1,
Flight2 , Flight3, Flight4 , Class1 ,
Class2, Class3 , Class4 , Date1 ,
Date2 , Date3 , Date4 , Time1 ,
Time2 , Time3, Time4 , Status1 ,
Status2 , Status3 , Status4 , FareBasis1 ,
FareBasis2, FareBasis3 , FareBasis4 ,
NotValidBefore1 , NotValidBefore2 , NotValidBefore3 ,
NotValidBefore4 , NotValidAfter1 , NotValidAfter2 ,
NotValidAfter3 , NotValidAfter4 , Allow1,
Allow2, Allow3 , Allow4, Fare ,
EquivFarePD , Tax1 , Tax2 , Tax3, payprice,
Total , FareCalculation ,
FormOfPayment,remark, '10000 ' , 52 from srv_lnk.SZABC.DBO.BillSaleIticket where BookPID=@b )
Fetch BillBookproduct_Cursor into @a,@b,@c,@d
end
close BillBookproduct_Cursor
Deallocate BillBookproduct_Cursor
/*if @@Error> 0
Begin
RollBack Tran
End
Else
Commit Tran*/
update ENTA.DBO.BillSale set TotalAccount=@all where billno=@BillSale_Out
commit tran
set xact_abort off
GO
速度根本无法忍受，看是否有解决途径！
[解决办法]
顶一下
[解决办法]
是不是网络的问题?直接查询srv_lnk的表快吗?
[解决办法]
这样的语句有点头晕。
楼主可以参考‘如何设置远程服务器以便允许使用远程存储过程’的方法来看看是否可以达到目的。
------解决方案--------------------
跟adsl关系不大，可在本机执行不调远程验证
[解决办法]
网络速度如何?
[解决办法]
帮顶
[解决办法]
你试试直接把远程的数据给插入到本地速度，如果可以那就是存储过程有问题，如果不可以，
那就网速问题
[解决办法]
这样远程的不定因素就太多勒，如果是内网的话应该没有问题
