C＃中存储过程的超时错误

i have this stored procedure to retreave data from database (dynamic query). and i am calling this stored procedure from c# codebehind, i am passsing two parameters from c# to this stored procedure.

我有这个存储过程来从数据库中恢复数据(动态查询)。我从c#codebehind调用这个存储过程,我将两个参数从c#传递给这个存储过程。

alter procedure [dbo].[GetCompleteCPTDetails]
    @Practice_Short_Name varchar(50),
    @Uploaded_Date nvarchar(max)
as
begin
DECLARE @CPTtablename nvarchar(300)
DECLARE @vQuery NVARCHAR(max)
DECLARE @upldate nvarchar(100)
set @upldate = @Uploaded_Date
set @CPTtablename ='ACER_CLAIMS_MASTER_DETAIL_Hist_'+@Practice_Short_Name
SET @vQuery = 'select Practice_Short_Name,Service_Date_From,Carrier_Name,
   Location_Description,Patient_Number,Patient_First_Name,
   Patient_Last_Name,Voucher_Number,Procedure_Code,Service_Fees,
   Service_Payments,Service_Adjustments,Acer_Status,Acer_Allowed_Amount
   from ' +@CPTtablename+' 
   where Uploaded_Date =''' + @upldate + ''' 
   order by acer_status asc, Service_Date_From desc, Patient_First_Name asc'
EXEC (@vQuery)

end
GO

but when i am running this query it is giving me TimeOut error. but if i assign value to my parameters in SP and Run SP from query windows then it is showing correct data.

但是,当我运行此查询时,它给了我TimeOut错误。但是,如果我在SP中为我的参数赋值,并从查询窗口运行SP,那么它显示正确的数据。

can any please explain me why it is giving timeout error if i am calling it from C# codebehind.

如果我从C#codebehind调用它,请解释我为什么它会给出超时错误。

2 个解决方案

#1

That is a pretty simple where and order by.
Unless that is just a massive table with no indexes that should be fast.
Is there an index on Uploaded_Date and is it not fragmented.
Also an index on the sort would help.

这是一个非常简单的地方和顺序。除非那只是一个庞大的表,没有索引应该很快。 Uploaded_Date上是否有索引,是否没有碎片。此外,排序索引也会有所帮助。

Are you loading everything into a DataTable?
If so try loading into DataReader.

您是否将所有内容加载到DataTable中?如果是这样,请尝试加载到DataReader中。

Try a top 1 and remove the order by.
If that does not return then you have connection issue as no way that query should time out.

尝试前1并删除订单。如果没有返回,那么您有连接问题,因为查询不应该超时。

The other thing to try is with (no lock) to see if it is a lock problem.

另一件要尝试的是(没有锁定)以查看它是否是锁定问题。

Why is @Uploaded_Date nvarchar(max)?
Is that a date or not?

为什么@Uploaded_Date nvarchar(max)?这是约会吗?

#2

There can be many solutions to this problem, as problem areas can be different in each case.

对于该问题可以有许多解决方案,因为在每种情况下问题区域可以是不同的。

But most common:

但最常见的是:

Check & increase sqlcommand timeout in your application

检查并增加应用程序中的sqlcommand超时

Try calling this SP asynchronously

尝试异步调用此SP

Also i would like to know, your application on the same machine where DB resides?

另外我想知道,你的应用程序在DB所在的同一台机器上?

#1