SQL超时错误！ [英] SQL Timed Out Error !

问题描述

大家好，

每当我从MS SQL 2005到Crystal Report检索患者数据记录时，都会收到超时错误".我认为表的行可能过多.

在OrderTest表中，有50,000多行.
在OrderTestDetail表中，超过200,000行.
在Doctor Table中，超过1000行.
在患者表"中，超过40,000行.

我使用这种存储过程:

Hello all,

Whenever i retrieve records of patient data from MS SQL 2005 to Crystal Report, i got Timed Out Error. I think may be too many rows of my tables.

In OrderTest Table, there''s over 50,000 rows.
In OrderTestDetail Table, over 200,000 rows.
In Doctor Table, over 1000 rows.
In Patient Table, over 40,000 rows.

I use this kind of stored procedure:

SELECT 
OT.OrderDate, 
OTD.ItemID,
I.ItemName,
OTD.Price,
P.PatientName, 
D.DoctorName,
D.Remark
FROM OrderTest OT
LEFT JOIN OrderTestDetail OTD ON OT.OrderTestID=OTD.OrderTestID
LEFT JOIN Items I ON  OTD.ItemID=I.ItemID
LEFT JOIN Doctors D ON  I.[DoctorGroup]=D.[DoctorGroup]
LEFT JOIN Patients P ON P.PatientID=OT.PatientID
WHERE OT.OrderDate BETWEEN @FromDate AND @ToDate

我应该如何更改存储过程?
大家请帮帮我.

问候，
nway nge

How should i change my stored procedure?
Please help me, everyone.

regards,
nway nge

推荐答案

在代码中调用此Sp的地方，可以设置set CommandTimeout

Where you calling this Sp in code you can set set CommandTimeout

cmd.CommandTimeout = 0 ;

您可以使用SQL命令的 CommandTimeout 属性和标记 httpRunTime 的 executionTimeout 属性在C#中使用web.config b>.无需更改过程

You can use the CommandTimeout property of the SQL Command and the executionTimeout property of tag httpRunTime of web.config in C#. Without changing the procedure

您需要使用索引视图，以最大程度地减少查询执行所花费的时间，因为联接数量更多3.

希望对您有帮助，

谢谢
-Amit

Hi,

you need to use Indexed View to minimize time taken by query execution as number of joining are more then 3.

hope this will help you,

thanks
-Amit

这篇关于SQL超时错误！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！