I am trying to run a SQL Server procedure from a program in ASP.Net 2005. This procedure is to insert around 500 records(can exceed every month) in a table with 4 columns and is also containing another small procedure also. When this procedure is executed from online server, it shows timeout message as:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
But when the same procedure is run from SQL Query Anayser it excute within seconds. How can i solve this problem , i need this solution urgently too.
Hope to get ur response soon.
Hello romita
Do you mind sharing some code. It would be helpful to try and figure out the problem
Also are you able to run SQL Profiler to check that statments that are hitting the databse from the aps.net application.
regards
G
|||Hi Gonzo11,
Regarding the same problem posted yesterday, am enclosing the code of procedure below for ur reference. Please check n let me know if theres any solution.
CREATE PROCEDURE proc_userroyalty
@.month int, @.year int, @.joins int output, @.royalties int output, @.royaltyamt decimal output, @.netamt decimal output
as
declare @.joinamt bigint, @.user varchar(50), @.left int, @.middle int, @.right int, @.royalty decimal, @.regdate datetime , @.totalpairs int, @.lsdate varchar(50), @.bdate datetime, @.maxamt decimal, @.nxtmon int, @.totalroyal decimal, @.nxtdt datetime, @.nxtyr int
if exists(select * from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year and @.month<month(getdate()) and @.year<=year(getdate()) )
begin
select @.joins = count(*) from db_users where month(us_regdate)=@.month and yeaR(us_regdate)=@.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year
if @.month<8 and @.year <=2007
begin
select @.joinamt=@.joins * 100
end
else
begin
select @.joinamt=@.joins * 120
end
select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year group by bd_amount
end
else
begin
delete from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year
select @.joins=count(*) from db_users where datepart(month,us_regdate) = @.month and datepart(year,us_regdate) = @.year and us_status='Y'
exec proc_royal @.month, @.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year
select @.joinamt=@.joins * 120
if @.royalties > 0
begin
select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.lsdate=str(@.month) + '-' + '28' + '-' + str(@.year)
select @.bdate=convert(datetime,@.lsdate)
declare cur_bonus cursor
for select temp_user,temp_regdt from db_tempbonus where temp_month<=@.month andtemp_year=@.year
open cur_bonus
fetch next from cur_bonus into @.user,@.regdate
while @.@.fetch_status = 0
begin
select @.nxtdt=dateadd(mm,3,@.regdate)
if datediff(dd,@.nxtdt,getdate()) > 0
begin
select @.nxtmon=month(@.nxtdt)
select @.nxtyr=year(@.nxtdt)
SELECT @.totalpairs= SUM(mp_lcount) + SUM(mp_mcount) + SUM(mp_rcount)
FROM db_monthlypairs
WHERE (mp_month<=@.nxtmon ) and (mp_year <=@.nxtyr) AND (mp_user = @.user)
if @.totalpairs< 30
begin
select @.maxamt=25000
end
else
begin
select @.maxamt=50000
end
end
else
begin
select @.maxamt=50000
end
select @.totalroyal=sum(bd_amount) from db_bonusdetails wherebd_user=@.user and bd_purpose='R'
if @.totalroyal <@.maxamt or @.totalroyal is null
begin
insert into db_bonusdetails values(@.bdate,@.user,'R',@.royaltyamt)
end
fetch next from cur_bonus into @.user,@.regdate
end
close cur_bonus
deallocate cur_bonus
select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year
end
else
begin
select @.royalties=0
select @.royaltyamt=0
select @.netamt=0
end
end
GO
PROCEUDRE 2 - proc_royal to be executed within above one.
CREATE PROCEDURE proc_royal
@.month int , @.year int
AS
declare @.user varchar(50), @.regdt datetime, @.lcount int , @.mcount int, @.rcount int, @.uspos char(1), @.rows int, @.mainuser varchar(50), @.regdate datetime
declare cur1 cursor
for select mp_user, us_regdate from db_users,db_monthlypairs where mp_user=us_login and mp_month<=@.month and mp_year<=@.year and mp_user not in(select temp_user from db_tempbonus where temp_month<=@.month and temp_year<=@.year)
group by mp_user, us_regdate having sum(mp_lcount)>=1 and sum(mp_mcount) >=1 and sum(mp_rcount)>=1
open cur1
fetch cur1 into @.mainuser, @.regdate
while @.@.fetch_status=0
begin
declare cur2 cursor
for select us_login, us_regdate, us_position from db_users whereus_reference=@.mainuser and month(us_regdate) <= @.month and year(us_regdate)<=@.year
open cur2
fetch cur2 into @.user, @.regdt, @.uspos
while @.@.fetch_status=0
begin
if @.uspos ='L'
begin
select @.lcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.lcount = @.lcount + 1
end
else
begin
if @.uspos ='M'
begin
select @.mcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.mcount = @.mcount + 1
end
else
begin
select @.rcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.rcount = @.rcount + 1
end
end
fetch cur2 into @.user, @.regdt, @.uspos
end
close cur2
deallocate cur2
if @.lcount>=4 and @.mcount>=4 and @.rcount >=4
begin
insert into db_tempbonus values(@.mainuser, @.regdate, @.month, @.year)
end
fetch cur1 into @.mainuser, @.regdate
end
close cur1
deallocate cur1
GO
proc_royal is to add new members satisfying the condtitions to a table eah month. In the main procedure based on all records in this table that many records will be inserted in another one. This part came as updation in the project, so its very risky to change other codes as it might affect existing program.
Hope u got the idea of the procedure.
If theres any simple way with less execution time please let me know. By each month records can increase too, so solution has to be one for furture user too.
Awaiting ur response,
Regards,
Romita
|||
Basedon your post, the error occurs when the execution time beyond TimeoutProperty. When you are trying to connect or access to a Database tablewhich is having large volume of data, query execution time will bemore. There are two main Timeout property in ADO.NET.
1.Connection Timeout for Connection. It could be solved by settingConnectionTimeout property of Connection object in Connection String.
2. Timeout for Data access ( Command Object ). You can setCommandTimeoutproperty to Command object.I recommend you set CommandTimeOut propertyto bigger one value. Try this.
Please let me know whether this canhandle this problem or not.If you have any further questions, please feel free to let me know.
|||Hello Romita,
Thank you for you reply. I will need some time to go over you SP.
In the meantime are you able to run the SQL Profiler and check what is actually hitting the DB and what is taking the longest time.
regards,
G
|||Hi!
I have tried setting command timeout and connection timeout properties, but still this Time Out Expired exists. Just setting the Timeout property of each object is enough or some other changes are also reqd.
Actually i had tried executing the procedure without calling another procedure within, it didn;t solve my problem. Time is taking to insert 400-500 records at a time, but it cannot be programmed in other way too.
Pls let me know, if theres more to do in Timeout settings or if theres another solution.
Regards,
Romita
|||
Hello Romita,
I have looked at the code that you have sent but i fyou say that it is inserting 400-500 rows that generaly should not take a very long time.
You have to see which parts of the SP are taking up most of the time and address them first.
So, you would have to run SQL Server Profiler, that will help you identify what is hitting the database and what is taking the most time.
Once you identify that you can try to address that issue. For example, very often creating an index for a table ususaly helps.
Hope this helps
regards,
G
No comments:
Post a Comment